In the previous tutorial, we looked at replicating SaxoTraderGO charts using Excel's built in stock chart tool. One important difference still exists however: the chart is static and needs to be updated manually. Using a similar implementation as in Tutorial 5, we will now attempt to continuously refresh the chart as new data comes in. This sound easier than it is, as Excel will automatically recalculate all of the values that are computed on the Data sheet when we refresh the source data.
This chart features Bollinger Bands, which are fairly simple to recalculate. However, consider a situation where historic chart data is fed into many individual indicators, each of which require numerous components to be calculated separately. Assuming the chart pulls out a maximum of 1200 datapoints, a total of roughly 6000 cells are updated each time only to refresh the underlying data. Depending on the amount of calculations linked to this data, a simple refresh can quickly cascade into a series of updates across many more cells (easily over 20000). This puts serious pressure on Excel, especially considering we would ideally like to update our chart every 3 seconds. In addition, the vast majority of the recalculations is not required, as the historic data does not change (and therefore, any indicators linked to this do not change either). In essence, we are only interested in updating the most recently available value and compute the changes in the indicator values accordingly.
To overcome this issue, we implement a precheck, which pulls out only the latest data from the /charts/ endpoint and compares it against the data that is in the graph. If the timestamp of the horizon on the latest response from the OpenAPI matches the timestamp of the latest data in the chart, we can use some simple VBA logic to only update the latest datapoints. This reduces the amount of recalculations substantially, as Excel will only recompute dependent cells that are linked to the these datapoints.
If the timestamp is different, a full refresh is triggered, which pulls down new data from the OpenAPI for the entire time window. This will only happen once per minute at most (when the horizon is set to 1 minute).
To achieve the above result, the following bits of VBA are linked up to the buttons labeled Start Chart and Stop Chart:
When the AutoUpdateChart subroutine is called, the following steps are executed:
- Check if routine is still allowed to run.
- Call the precheck subroutine to only pull out the latest datapoints from the OpenAPI.
- If the precheck finds a difference in the timestamps, trigger a full refresh of the chart data.
- Loop this function every 3 seconds.
The precheck subroutine performs the usual API calls and error checking that we have implemented in earlier charting tutorials. It also compares the value of the returned timestamp (in cell Data!L2) against the current one (in cell Data!A61). If the value is the same, the last values are updated using the response from the API that is already available on the Data sheet (see image). This vastly reduces the pressure on both the OpenAPI, as we only request a single datapoint unless the current horizon expires, and Excel, because it will recompute the values in row 61 only.
And finally, a full refresh is triggered only when required by the precheck, which sets the fullupdate flag to True. This will recompute all values, which is necessary because all time blocks are 'shifted' as the latest one is added on the top.
This tutorial introduces Bollinger Bands for demonstration purposes only, as the main subject concerns improving update performance of the Excel chart. The Data sheet is not designed to automatically respond to changes in the amount of datapoints and the parameters of the Bollinger Bands.