using a dictionary like cache

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:
Code:
Global tsValueCache As Dictionary

But then you have to run this silly thing:
Code:
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:
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:

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Corticus

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
Not to get bogged down in the details here, but I know that:
Code:
If IsEmpty(ar) Then
should be:
Code:
If IsEmpty(seriesArray) Then
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,116
Office Version
  1. 365
Platform
  1. Windows
Of course the object would only be valid for the current Excel session.
Code:
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

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
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.
 
Last edited:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
Here's what's crazy.
In this stripped down test, I first run cleacDictionary and then testIt2
Code:
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

Well-known Member
Joined
Apr 30, 2002
Messages
1,579

ADVERTISEMENT

duplicate
 
Last edited:

Corticus

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
code should be this... but the error still happens.
Code:
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

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
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
Code:
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
What is getSeriesArray?

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

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,719
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top