SOLVED

Automatic recalculation of functions

New Contributor

Let me introduce a use case (I intentionally tried to make it realistic):

 

Suppose we are building a simple dashboard in Excel. The data is coming from a Web API (we are calling =WEBSERVICE function). The response is then parsed using =TEXTSPLIT and other text functions, to turn the response into a table. To make things clean, all the logic of building URL and parsing response is moved to lambda. Nice and clean!

 

The big problem - there is no way to keep this dashboard refreshed automatically. One can press F2 in the formula cell or refresh the sheet/ workbook, but for a dashboard that's not ideal.

 

I have explored some options (constraint - no VBA allowed) - an interesting one is to make dependency on a stock data. Make a cell (say MSFT) as stock and get price (cell A1) for it. Now if I want to show running time in cell A2, I can write something like =IF(A1>0, NOW(), NOW()). Since A1 refreshes periodically, A2 recalculates. 

 

As expected, this is less than ideal solution - linked data refresh frequency is low and this technique looks very hacky. What stops us in providing some mechanism to allow a refresh frequency (may by some specific functions which can acquire data from outside the current workbook)? Is it performance? If RTD can have near streaming data, can we not allow some of these functions to execute every n seconds? 

4 Replies
You could do this with a bit of VBA. Consider the last answer in this thread:
https://stackoverflow.com/questions/22772898/how-to-have-vba-execute-every-10-minutes
As mentioned in the discussion - why could we not make this a feature of function itself? Just to repeat myself, with new Lambda/ new functions, we could do a lot without ever needing to resort to VBA.
There are many situations where macros are not allowed/ there are users who are not developers.
best response confirmed by cmukesh19 (New Contributor)