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.

 

[Corticus]

Not to get bogged down in the details here, but I know that:

If IsEmpty(ar) Then

should be:

If IsEmpty(seriesArray) Then

 

[Kenneth Hobson]

Of course the object would only be valid for the current Excel session.

Dim d As dictionary

Sub dic1()
 Dim i As Integer
 If d Is Nothing Then Set d = New dictionary
 For i = 1 To 10
  If Not d.Exists(i) Then d.Add i, Nothing
 Next i
 MsgBox Join(d.Keys, vbLf)
End Sub

Sub dic2()
  If Not d Is Nothing Then MsgBox Join(d.Keys, vbLf)
End Sub

Sub dic3()
 Dim i As Integer
 If d Is Nothing Then Set d = New dictionary
 For i = 11 To 12
  If Not d.Exists(i) Then d.Add i, Nothing
 Next i
 MsgBox Join(d.Keys, vbLf)
End Sub

Sub dicDelete()
  Set d = Nothing
End Sub

 

[Corticus]

Kenneth - thank you for the reply.
In your examples, won't the dictionary 'go away' after the procedure is complete?

I need the dictionary to stick around so other worksheet formulas can make use of it.
That's why I am dimensioning it globally, but unlike simple objects (arrays, strings, dates etc), I have to 'create new' and I don't know how to get that to be global.

 

[shg]

won't the dictionary 'go away' after the procedure is complete?

No, because it's declared at the module level

That's why I am dimensioning it globally

Declaring it as Global (or Public) just changes its scope, not its lifetime. See Help for Scope and lifetime of variables

I have to 'create new' and I don't know how to get that to be global.

Where the initialization is done (and Kenneth's code does that) doesn't matter.

 

[Corticus]

Here's what's crazy.
In this stripped down test, I first run cleacDictionary and then testIt2

Sub clearDictionary()

    Set tsValueCache = Nothing
    Set tsValueCache = New Dictionary

End Sub

Sub testIT2()

    Dim x As Variant
    Dim seriesID As String
    
    seriesID = "IBM:CLOSE"
    
    x = tsValueCache("IBM:CLOSE")
    If IsEmpty(x) Then tsValueCache.Add seriesID, getSeriesArray(getJSON(seriesID))
    

End Sub

What's crazy is that x comes back empty, indicating the key is not found.
But... when I go to add it to the dictionary, I get an error that it is already there "key is already associated with an element of this collection"
How could it already be there if I just cleared the dictionary?
And it it is there, why is x empty?

 

[Corticus]

duplicate

 

[Corticus]

code should be this... but the error still happens.

Sub testIT2()

    Dim x As Variant
    Dim seriesID As String
    
    seriesID = "IBM:CLOSE"
    
    x = tsValueCache.Item("IBM:CLOSE")
    If IsEmpty(x) Then tsValueCache.Add seriesID, getSeriesArray(getJSON(seriesID))
    

End Sub

 

[Corticus]

Thanks for the clarification.
I do need it to be global so other modules can see it.
I did not know that declaring at the module level would allow it to persist to other worksheet functions.

It seems my issue is not getting the object to persist however, as you'll see now in my subsequent posts, but actually the issue is just adding a key/element pair to the dictionary and being able to retrieve it.
Here's where my code is now... the issue being that I am always getting the 'this item already exists' error

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

    Dim seriesArray As Variant
    
resumeAfterNew:
    seriesArray = tsValueCache.Item(seriesID)

    If IsEmpty(seriesArray) Then
        seriesArray = getSeriesArray(getJSON(seriesID))
        tsValueCache.Add seriesID, seriesArray
    End If
    
    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

It may be that I just don't know how to use a dictionary and the issue is much simpler than I think.

Thanks.

 

[shg]

What is getSeriesArray?

Also, where is the dictionary declared, and do you have Option Explicit at the top of very module?