Excel VBA: Live Updated Spreadsheet

Can we store data in our spreadsheet that can be lively updated from the internet and then directly recorded into a table? Yes we can. Here the example how your net worth in foreign currency can automatically be updated and recorded in terms of domestic currency.

Step 1
Open a website that stores an xml data for currency rate, for example:
http://apps.kompas.com/bursa/

Step 2
Open an Excel file then go to:
> Data
> Import External Data > New Web Query

Step 3
Copy and paste the URL address, then press Go

Check at the table we want to import, then click Import.
In your Sheet1, the data would be stored like this.


Step 4
Name the cell of the currency rate as "IDRrate".
Just highlight the cell and go to upper left next to "fx" to name it.
Go to Sheet2 and create a table like this.
Name the cell of the home currency as "IDR".


Step 5
Record new macro and name it "autorate"
> Tools
> Macro > Record New Macro
Record new macro and name it "liverate"
> Tools
> Macro > Record New Macro
Record new macro and name it "recordrate"
> Tools
> Macro > Record New Macro

Step 6
Go to
> Tools
> Macro > Visual Basic Editor
Write these simple VBA codes for the new macros:

Sub autorate()
Application.OnTime Now + TimeValue("12:00:00"), "liverate"
End Sub

'Data updated every 12 hours.

Sub liverate()
Sheets("Sheet1").Activate
Range("a1").Select
Selection.QueryTable.Refresh BackgroundQuery:=True
autorate
End Sub

'Data updated by clicking the "Update Rates" button.

Sub recordrate()
Range("IDRrate").Copy
Sheets("Sheet2").Range("IDR").PasteSpecial Paste:=xlPasteValues
Calculate
Sheets("Sheet2").Select
End Sub

'Data recorded from the web query sheet to the calculation table.

Step 7
Make buttons for "Update Rates" and "Record IDR"
> View
> Toolbars > Forms
Pick up a button put on the sheet.
> Right click
> Assign macro
> Choose "liverate" for the "Update Rates" button.
> Choose "recordrate" for the "Record IDR" button.

Sample
(please enable macro to open the file)

Simple sample download >>>

Live-updated PT Astra Intl Tbk (ASII) Valuation >>>