Some people update their spreadsheet one stock at a time, others update one transaction at a time. Regardless which method you use, it is always a hassle to update the stock prices manually.
There are many ways to search for live quotes and automatically update your spreadsheet with these real-time stock prices. Today, i will show you one method i used, before i started using SGXcafe.
While SGXcafe is easy, portable and amazing, it is good to have your own personal spreadsheet to keep track of your transactions, in case you want to play around with the data you have. In addition, SGXcafe only provides daily reports on your gain/loss and portfolio value.
Update Stockprices Automatically with VBA using Yahoo Finance Data
Ok here's how it works.
Open Excel and enable the developer tab by going to options, add-ins and checking VBA ToolPak.
After that, you should see a new 'Developer tab'. Click on Visual Basic,
At the top left you should see your spreadsheet. Right Click on it and create a 'module'. Paste the following code in the blank space at the right.
---
Function StockQuote(strTicker As String)
Dim strURL As String, strCSV As String
Dim dbClose As Double
' Compile the request URL
strURL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & strTicker & "&f=l1"
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", strURL, False
http.Send
strCSV = http.responseText
dbClose = Val(strCSV)
StockQuote = dbClose
Set http = Nothing
End Function
---
You have just created a formula with the name 'stockquote'.
Simply type =StockQuote('input the relevant stockquote here')
For example, Singtel as a stockquote of Z74.SI in Yahoo Finance. Hence, type this:
=StockQuote(Z74.SI)
You can reference a cell instead, like this:
A few things to note
The stock prices are not automatically updated in real time. You would need to highlight the cells, press 'F2' and hit 'Ctrl+Enter'. This is because the formula does not recalculate every second, or Excel will crash. Somewhat like a Pivot table.
Happy investing!
