using a dictionary like cache

[Corticus]

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:

Global tsValueCache As Dictionary

But then you have to run this silly thing:

Set tsValueCache = New Dictionary

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:

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.

 

[Kenneth Hobson]

Before adding to the dictionary, check if it exists.

If Not d.Exists(i) Then d.Add i, Nothing

 

[RoryA]

This line:

x = tsValueCache.Item("IBM:CLOSE")

actually adds an empty item with that key if it's not already in use. As Kenneth says, you need to use Exists to test.

 

[Corticus]

It's at the top of the post.

getSeriesArray works, but it never gets to that line. It thinks seriesArray is empty, tries to add the key to the dictionary, and then fails because it is already there.
tsValueCache.exists("IBM:CLOSE") returns 'True'.
But, seriesArray = tsValueCache.Item(seriesID) comes back empty.

 

[Corticus]

ah... I see now.
"actually adds an empty item with that key if it's not already in use"
I didn't realize that.
Will update my code and post back. That sounds like the issue.

 

[Corticus]

That worked.
Just FYI, I do have OPTION EXPLICIT and tsValueCache is dimensioned in a config module that I have a connection constants and stuff like that in.

The issue was simply to test for exists first and that the .item was adding the key with no element if it wasn't there.
How ridiculous!

Thanks for the help.

Here is the code that worked:

Function getTimeSeriesValue(seriesID As String, valueDate As Date) As Double
On Error GoTo functionErr

    Dim seriesArray As Variant
    
resumeAfterNew:
    If tsValueCache.Exists(seriesID) Then
        seriesArray = tsValueCache.Item(seriesID)
    Else
        tsValueCache.Add seriesID, getSeriesArray(getJSON(seriesID))
    End If

    seriesArray = tsValueCache.Item(seriesID)
    getTimeSeriesValue = getSeriesArrayValue(seriesArray, valueDate)

exitFunction:
    Exit Function

functionErr:
    Select Case err.Number
        Case 91     'object not instantiated
            Set tsValueCache = New Dictionary
            err.Clear
            Resume resumeAfterNew
        Case Else
            MsgBox (err.Number & " " & err.Description)
            Resume exitFunction
    End Select

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           'date
            getSeriesArrayValue = valueArray(i, 1)      'value
            Exit For
        End If
    Next i

End Function

It's definitely faster, but not as fast as I hoped.
I'm thinking of ditching the array of values that getSeriesArrayValue has to loop through and replacing that with another dictionary.
That way I'll eliminate any looping.

Good stuff everybody, thanks again and I'll post back with whatever I end up with.

SOLVED

 

[Corticus]

hooboy, that last adjustment did the trick.
8,000 lookups in 4 seconds or .5 ms per lookup.

Here's what I ended up with:

Dim valueDictionary As Dictionary
Dim tsValueCache As Dictionary

Function getTimeSeriesValue(seriesID As String, valueDate As Date) As Double
On Error GoTo functionErr

    Dim seriesArray As Variant
    Dim seriesDictionary As Dictionary
    Dim i As Integer
    
resumeAfterNew:
    If Not tsValueCache.Exists(seriesID) Then
        Set valueDictionary = New Dictionary
        seriesArray = getSeriesArray(getJSON(seriesID))
        For i = 1 To UBound(seriesArray)
            valueDictionary.Add seriesArray(i, 0), seriesArray(i, 1)
        Next i
        tsValueCache.Add seriesID, valueDictionary
    End If

    Set seriesDictionary = tsValueCache.Item(seriesID)
    getTimeSeriesValue = seriesDictionary(valueDate)

exitFunction:
    Exit Function

functionErr:
    Select Case err.Number
        Case 91     'object not instantiated
            Set tsValueCache = New Dictionary
            err.Clear
            Resume resumeAfterNew
        Case Else
            MsgBox (err.Number & " " & err.Description)
            Resume exitFunction
    End Select

It blazes!

Thanks all.