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[Sorry, it's a premium spreadsheet]
The SheetsThere 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 NamingCell 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 CodingWeb Query UpdateThis 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 SubRecording Data through BootstrappingThis 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 SubThe 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?Option for updating screenYou may insert this code to disable the visualisation of screen updating. Meaning, you can't see the price line moving forward as time moves.
Application.ScreenUpdating = False
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 NotesThe 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.
