getting started example

[ShieldTrade]

@cunnane, tx for helping me getting up and running with xloil

this is my starting point ( a simplified version of ccxt recommended usage ) and my goal is to use xloil to write binance_pr and ccxt_pr to excel every 10 seconds.

any help would be appreciated

import xloil as xlo
import datetime, asyncio

binance_pr = 1
ccxt_pr = 10

class worker():
    def __init__(self, name, duration,):
        self.__stopped = False
        self.name = name
        self.duration = duration

    async def fetch_binance_pr(self):  # simulating a ws connection
        while not self.__stopped:
            global binance_pr
            binance_pr = binance_pr + 1
            print(self.name, 'received a pr from binance. Pr is', binance_pr)
            await asyncio.sleep(self.duration)

    async def fetch_ccxt_pr(self):
        while not self.__stopped:
            global ccxt_pr
            ccxt_pr = ccxt_pr + 10
            print(self.name, 'received a pr from ccxt. Pr is', ccxt_pr)
            await asyncio.sleep(self.duration)


async def main():
    con_1 = worker('ws_binance', 2)
    con_2 = worker('ws_ccxt', 5)
    loops = [
            con_1.fetch_binance_pr(),
            con_2.fetch_ccxt_pr(),
        ]
    # Here we have two coroutines running parallely which are supposed to run forever
    await asyncio.gather(*loops) 

if __name__ == "__main__":
    loop = asyncio.get_event_loop()
    loop.run_until_complete(main())

asyncio.run(main())

[cunnane]

I've converted this to a disucssion as it's not strictly a problem with xlOil!

Have you looked at pyGetUrlLive int the async examles? When given a URL like https://blockchain.info/tobtc?currency=USD&value=1 it will publish USDBTC every 4 seconds. However it's more heavyweight that your require as it builds a full RtdPublisher. To just get some exchange prices, this will work:

import ccxt.async_support as ccxt
import xloil as xlo

@xlo.func
async def poloniex_btc():
    poloniex = ccxt.poloniex()
    while True:
        ticker = await poloniex.fetch_ticker('ETH/USDT')
        yield ticker['last']

[ShieldTrade]

The above is only a simplified example. The fetch method name might be confusing.

async def fetch_binance_pr(self) and async def fetch_ccxt_pr(self) are in fact ws.
As in ccxt ufficial example orderbook = await exchange.watch_order_book(symbol) is a future returned every time the ws ( called in the background by ccxt) receives a price.

Every time a price is received a complex algo is called. It all happens in the main loop which is supposed to run forever.
Assuming all var produced by the algo are global I am looking for a way to have xloil print them on excel.

[ShieldTrade]

I think this can be a good starting point to integrate xlOil with CCXT ( and my code)without too much changes.

import xloil as xlo
import asyncio

binance_pr, ccxt_pr, all_conn_started = 1, 10, False

""" start all ws connection only once when called """
@xlo.func
async def Py_binance_pr(sec=5):
    if not all_conn_started:
        asyncio.create_task(start_all_ws_conn())
    while True:
        yield binance_pr
        await asyncio.sleep(sec)

@xlo.func
async def Py_ccxt_pr(sec=5):
    if not all_conn_started:
        asyncio.create_task(start_all_ws_conn())
    while True:
        yield ccxt_pr
        await asyncio.sleep(sec)

""" class simulating a websocket connection receiving a pr every x_sec
If all the connections are called in the same loop, every time the orderbook is returned the future is done and the control is given back to the event loop.
We are just simulating ccxt design without importing the library.
orderbook = await exchange.watch_order_book(symbol) in https://github.com/ccxt/ccxt/blob/master/examples/ccxt.pro/py/many-exchanges-many-streams.py
is replaced by await asyncio.sleep"""

class fake_ws():
    def __init__(self, name, x_sec,):
        self.__stopped = False
        self.name = name
        self.x_sec = x_sec

    async def binance_connect_and_get_pr(self):  # simulating a ws connection
        while not self.__stopped:
            global binance_pr
            binance_pr = binance_pr + 1
            print('received a pr from', self.name, 'Pr is', binance_pr)
            await asyncio.sleep(self.x_sec)

    async def ccxt_connect_and_get_pr(self):
        while not self.__stopped:
            global ccxt_pr
            ccxt_pr = ccxt_pr + 10
            print('received a pr from', self.name, 'Pr is', ccxt_pr)
            await asyncio.sleep(self.x_sec)


async def start_all_ws_conn():
    global all_conn_started
    ws_binance = fake_ws('ws_binance', 2)
    ws_ccxt = fake_ws('ws_ccxt', 5)
    all_conn_started = True
    loops = [
            ws_binance.binance_connect_and_get_pr(),
            ws_ccxt.ccxt_connect_and_get_pr(),
        ]
    # Here we have two coroutines running parallely which are supposed to run forever
    await asyncio.gather(*loops)

[cunnane]

I don't think you need to use global prices here (they're a bit un-Excel like as Excel functions generally don't have hidden state). You also don't need to explicitly do the asyncio.gather - xlOil is managing the asyncio event loop, you just need to add tasks to it, so I think the code below is enough. Where you might want globals is to ensure you only have one connection object, i.e. one fake_ws as the below will create on connection per Excel function (not per call, just per function on the sheet).

import xloil as xlo
import asyncio

class fake_ws:
    def __init__(self, name, x_sec):
        self.__stopped = False
        self.name = name
        self.x_sec = x_sec
        self.binance_pr = 1 # Start binance connection here
        self.ccxt_pr = 1 # Start ccxt connection here

    async def binance_get_pr(self):  # simulating a ws connection
        while not self.__stopped:
            self.binance_pr += 1
            yield self.binance_pr
            await asyncio.sleep(self.x_sec)

    async def ccxt_get_pr(self):
        while not self.__stopped:
            self.ccxt_pr += 2
            yield self.ccxt_pr
            await asyncio.sleep(self.x_sec)
            
@xlo.func
async def Py_binance_pr(sec=5):
    conn = fake_ws('ws_binance', 2)
    while True:
        yield conn.binance_get_pr()
        await asyncio.sleep(sec)

@xlo.func
async def Py_ccxt_pr(sec=5):
    conn = fake_ws('ws_ccxt', 2)
    while True:
        yield ccxt_get_pr()
        await asyncio.sleep(sec)

[ShieldTrade]

I changed your code yield ccxt_get_pr() with yield conn.ccxt_get_pr() and added

@xlo.func
async def Py_clock(sec=5):
    while True:
        yield datetime.datetime.now()
        await asyncio.sleep(sec)

= Py_binance_pr() in excel is giving me #VALUE while = Py_clock() works correctly

[cunnane]

My mistake, you need to use an async for here as binance_get_pr is itself an async generator:

@xlo.func
async def Py_binance_pr(sec=5):
    conn = fake_ws('ws_binance', 2)
    async for price in conn.binance_get_pr():
        yield price 
        await asyncio.sleep(sec)

And similarly for Py_ccxt_pr