Live-Updated Spreadsheet for Time Series Data

The purpose of this model is to provide a live-updated spreadsheet by automatically capturing the current data provided by a website via Web Query and then automatically recording the data into a master data sheet through a Bootstrapping method.

So, it's just two clicks away on our spreadsheet to update a time series graph to the current rate, automatically. It's similar when we are running a live-update for our antivirus software.



The Worksheet

To follow the technique, it's good to download the sample worksheet first (please enable macro) and follow the coding explanation by opening the visual basic editor (>Tools >Macro >Visual Basic Editor) and the corresponding cells in the worksheet.


The Sheets

There are three sheets: INDO17, Data and Graph. "INDO17" is a sheet where the web query should go and where the two updating clicks are available. "Data" is a sheet where the bootstrapping process is running. "Graph" is only for visualisation.


Cell Naming

Cell naming is an important method as explained before in the previous post. There are several cells to be named in this worksheet and used for visual basic coding: INDO, INDOrow, INDOwebquery, nodays and updatelatestINDO.

To find which range is referred by a cell name, just go to the box on the top left, scroll the box and click the name. A range or cell will be shown.

• INDO: a range as the destination for the updated rate from 2-Mar-06 to 31-Dec-10 in "Data" sheet (B5:B1263).
• INDOrow: a cell showing a number of rows from 2-Mar-06 to the latest date has been updated in "Data" sheet (D3).
• INDOwebquery: a range as the source of data captured via web query in "INDO17" sheet (A2:D21).
• nodays: a cell showing a number of days need to be updated since the last updated date in "INDO17" sheet (M14).
• updatelatestINDO: a cell showing the next date needs to be updated in "Data" sheet (G4).

Visual Basic Coding

Web Query Update
This code is for the first button "Update INDO-17 Yield from Web Query". The webpage source used for web query is the INDO-17 yield data provided in the Central Bank of Indonesia website.

The code:

Sub dailyINDO()

Application.OnTime Now + TimeValue("12:00:00"), "updateINDO"

End Sub

Sub updateINDO()

Sheets("INDO17").Activate
Range("a1").Select
Selection.QueryTable.Refresh BackgroundQuery:=True
dailyINDO

End Sub

Recording Data through Bootstrapping
This code is for the second button "Record Yield into Data Sheet". The code is quite complicated. However, if we can follow the logic and as referring to the range names, this is a piece of cake for us.

The code:

Sub recordINDO()

For j = 1 To Range("nodays")
For i = 1 To Range("nodays")

If Sheets("Data").Range("updatelatestINDO") = Range("INDOwebquery").Cells(i, 1) Then
Range("INDOwebquery").Cells(i, 4).Copy

r = Sheets("Data").Range("INDOrow") + 1

Sheets("Data").Range("INDO").Cells(r).PasteSpecial Paste:=xlPasteValues
Calculate
End If

Next i
Next j

Sheets("Data").Select

End Sub

The meaning of the above code is:

For the loops as many as the number of days needs to be updated, Excel will copy each yield in the web query sheet and paste into the data sheet on each corresponding date. Where the corresponding date is found by referring the number of rows from the first date to the latest updated date plus 1 day after.

Piece of cake, aye?


The "INDOrow"

It seems the key problem here is to find the number of "INDOrow", a cell showing a number of rows from 2-Mar-06 to the latest date has been updated in "Data" sheet (D3).

The way is using a MATCH function to calculate the number of rows from 2-Mar-06 to the latest date as this formulae:
=MATCH("latest date",C5:C498,0)

Where the "latest date" is found by tagging a note in the column C as the latest date using IF formulae: =IF(AND(the next yield=0, the current yield >0),"latest date","").


The "updatelatestINDO"

This is a cell showing the next date needs to be updated in "Data" sheet (G4) that can be found easily using an INDEX function:

=INDEX($A$5:$A$498,INDOrow+1)


Recommendation and Notes

The title is for updating government bond yield, but the application can be also for updating other time series data such as foreign exchange rates or share prices.

I also open for any suggestions particularly for any more simple VBA codes.

Please let 2 days lag from Bank Indonesia website for current rate. It doesn't mean they are lazy to update, they just have a lot of other things to do.

More explanation about how to get data from the internet in Excel can be viewed here in Microsoft website.

Excel supports both reading and writing of XML. Complimenting this feature is the ability to create Web queries to XML files. You can create a Web query to any XML, but if you use the XML Spreadsheet schema, then you can preserve rich spreadsheet concepts like formulas.