Corticus
Well-known Member
- Joined
- Apr 30, 2002
- Messages
- 1,579
I have a UDF that retrieves a single data point from a time series.
A time series is an array of data value pairs, often used in finance to express some notion like the closing price of IBM.
IBM:CLOSE is the id for the time series, for example.
Data looks like this:
1/1/14 | 100
1/2/14 | 101
1/3/14 | 102
etc
The data is delivered to Excel by an http web service.
The service accepts a date parameter, which I am currently using to deliver just one data point at a time to the worksheet.
Everything is efficient enough (50ms a request), but when you have 10k data points per series, and 500 series in a set (like the S&P500), it results in a lot of calls and is slow.
My solution is to have the web service ask for the entire series instead of just one point.
Then, I put the series in a global dictionary.
Chances are, somebody is asking for a bunch of dates from the same series, so the first time they ask, the request is made and the entire time series goes to the cache and the value is returned. Every subsequent time, the series is in the cache (identified by the series id key) and is retrieved from there instead of making the web request. This should save 10's of thousands of web requests and dramatically increase performance.
If it worked.
It's a pretty generic caching strategy.
I started my strategy using 2 global arrays, one as an index and one to hold the values. The index array would have a pointer to the start of the series in the value array. However, maintaining and index and data on your own gets complicated, you have to maintain an index, clean up the cache as it gets full etc. Yuck.
So I decided to use a dictionary instead.
MUCH less overhead.
However, it seems you can't really have a global dictionary.
You can dimension the dictionary:
But then you have to run this silly thing:
and I don't know how/when/where to run that to make the instance of the object persist.
The code to use the dictionary is super-simple, thanks to the inherent elegance of dictionaries:
Can I do this?
Am I just dreaming that I could have a simple cache and let Excel do the work of dealing with keys and values and all that.
Or... do I have to go back to global arrays... that code got nasty but worked.
Thanks.
A time series is an array of data value pairs, often used in finance to express some notion like the closing price of IBM.
IBM:CLOSE is the id for the time series, for example.
Data looks like this:
1/1/14 | 100
1/2/14 | 101
1/3/14 | 102
etc
The data is delivered to Excel by an http web service.
The service accepts a date parameter, which I am currently using to deliver just one data point at a time to the worksheet.
Everything is efficient enough (50ms a request), but when you have 10k data points per series, and 500 series in a set (like the S&P500), it results in a lot of calls and is slow.
My solution is to have the web service ask for the entire series instead of just one point.
Then, I put the series in a global dictionary.
Chances are, somebody is asking for a bunch of dates from the same series, so the first time they ask, the request is made and the entire time series goes to the cache and the value is returned. Every subsequent time, the series is in the cache (identified by the series id key) and is retrieved from there instead of making the web request. This should save 10's of thousands of web requests and dramatically increase performance.
If it worked.
It's a pretty generic caching strategy.
I started my strategy using 2 global arrays, one as an index and one to hold the values. The index array would have a pointer to the start of the series in the value array. However, maintaining and index and data on your own gets complicated, you have to maintain an index, clean up the cache as it gets full etc. Yuck.
So I decided to use a dictionary instead.
MUCH less overhead.
However, it seems you can't really have a global dictionary.
You can dimension the dictionary:
Code:
Global tsValueCache As Dictionary
But then you have to run this silly thing:
Code:
Set tsValueCache = New Dictionary
The code to use the dictionary is super-simple, thanks to the inherent elegance of dictionaries:
Code:
Function getTimeSeriesValue(seriesID As String, valueDate As Date)
Dim seriesArray As Variant
seriesArray = tsValueCache.Item(seriesID)
If IsEmpty(ar) Then
seriesArray = getSeriesArray(getJSON(seriesID))
tsValueCache.Add seriesID, seriesArray
End If
getTimeSeriesValue = getSeriesArrayValue(seriesArray, valueDate)
End Function
Function getSeriesArrayValue(valueArray As Variant, lookupDate As Date)
Dim i As Integer
For i = 1 To UBound(valueArray)
If valueArray(i, 0) = lookupDate Then
getSeriesArrayValue = valueArray(i, 1)
Exit For
End If
Next i
End Function
Can I do this?
Am I just dreaming that I could have a simple cache and let Excel do the work of dealing with keys and values and all that.
Or... do I have to go back to global arrays... that code got nasty but worked.
Thanks.
Last edited: