scheduling timer tasks

[ekwf]

First, I wanted to say that this project is excellent!

I have a sheet that gets some data from Bloomberg, calculates model values and uses xloil-python to send these values to a database over tcpip. Sometimes the model values don't change enough to bother updating so there are periods of inactivity. I dealt with this by creating a heartbeat mechanism that sends a heartbeat message ever 30seconds.

The user opens this sheet and presses a button runs an xloil macro function to initialize the TCP connection object. Upon initialization this function is called:

def start_hb(self):
        logging.debug("%s starting hb loop" % self.name)
        loop = xloil.get_event_loop()
        self.hb_task = asyncio.run_coroutine_threadsafe(self.send_hb(),loop)
        logging.debug("%s kicked off send_hb loop" % (self.name))

which schedules this function on the xloil event loop:

async def send_hb(self):
        while True:        
            self._send_hb()
            await asyncio.sleep(self.hb_int)

the actual _send_hb() function does a time check and if enough time has passed sends the heartbeat. It also has a debug that prints regardless of whether a send happens or not. During all of this Bloomberg data ticks via RTD at it's own pace.

What I noticed is that when the sheet is initialized without Bloomberg, the heartbeat fires exactly as expected. However, with Bloomberg (a lot of RTD calls) the start_hb function never finishes. That is, starting hb loop prints, but kicked off send_hb loop never prints and eventually during a period of low activity the tcp connection disconnects due to heartbeat timeout.

Am I missing something about how run_coroutine_threadsafe works here? For what it's worth, I tried to follow the discussion in #59 to do this.

[cunnane]

This does seem strange: I know the BBG does fiddle with the RTD timing, but from what you're describing, it seems you're not using any RTD functions in xloil?

run_coroutine_threadsafe should return more or less immediately unless it throws, so I'd suggest putting a try...except around start_hb and logging any error (I would expect xloil to catch and print the error, but there may be some reason why it doesn't)

Also, it's possible to structure this so the user doesn't have to press a button: create an RTD clock and a worksheet function "SEND_HB" which takes a single dummy argument. Point the dummy argument to the output of the clock on the worksheet and it will be called whenever the clock ticks (assuming you have automatic calc enabled).

[keithalewis]

Another possibility might be https://xlladdins.github.io/Excel4Macros/on.time.html.
This corresponds to the xlcOnTime function number.

The Bloomberg add-ins do not play nicely with other add-ins.

[ekwf]

As an experiment I tried switching to loop = xloil.get_async_loop() and it appears to do exactly what I want although I can't 100% understand why this works and the other doesn't.

[cunnane]

Interesting observation. get_event_loop gives the asyncio loop which is used to background load code and handle ribbon callbacks, and get_async_loop returns the loop which handles RTD functions. Both these loops should be being "pumped" unless the associated thread crashes, so I'm not sure why you see this behaviour but will ponder on it.

[cunnane]

@keithalewis Agree that BBG is not friendly, especially the old-skool spill functionality. But I've no idea how it could sabotage my asyncio loop.

Ontime is a good idea, the most Excel-onic approach!

[govert]

In the past I found that xlcOnTime is more reliable that Application.OnTime, but xlcOnTime can't be called from a UDF context, or from some other thread, so setting it up from the context required is not so useful. xlcOnTime also used to reset the undo/redo stack every time, which is annoying (though I have not checked recently). I can't remember whether multiple 'OnTime' macros can be scheduled (e.g. by different add-ins). I ended up avoiding both xlcOnTime and Application.OnTime completely.

In Excel-DNA I have evolved to an elaborate mechanism that runs a windows message loop on the main thread to receive any notifications (including timer callbacks), then check whether Excel is in a 'receptive' state for invoking a macro (that means Excel is not busy calculating, running a macro, editing a formula etc.) and when safe then invoke a macro from which to execute the work. This has become reliable over time, though included a watchdog check and other enhancements.

RTD-based approaches which stay completely inside the normal Excel calculation world are of course best. Although RTD has its own quirks it tends to work as advertised, integrate well with calculations, including dynamic arrays. The fact that the RTD ThrottleInterval is a shared setting, global and persistent between Excel sessions, seems to be the biggest gotcha.

I'm always happy to discuss and test any of these low-level Excel ideas - at this level Excel is mostly undocumented, the knowledge base stuff from Microsoft has bit-rotted away and we can only figure out what would through empirically studying Excel like some artifact left by aliens.

[keithalewis]

❤️"at this level Excel is mostly undocumented, the knowledge base stuff from Microsoft has bit-rotted away and we can only figure out what would through empirically studying Excel like some artifact left by aliens."