I was using Excel to model my retirement portfolio and wanted to get the stock prices from the web. Found a bunch of helpful links, which I will share below, but for a simple close price, I used a VBA function to pull the close price from Yahoo Finance API. Add it to the module, not class module, to use it from any excel sheet.
Option Explicit Function StockQuote(ByVal ticker As String) As Double Dim url As String Dim closePrice As Double Dim http As Object ' Compile the request URL with start date and end date url = "http://download.finance.yahoo.com/d/quotes.csv?s=" & ticker & "&f=l1" Set http = CreateObject("MSXML2.XMLHTTP") http.Open "GET", url, False http.Send StockQuote = http.responseText Set http = Nothing End Function
You can read up on this API here: http://code.google.com/p/yahoo-finance-managed/wiki/csvQuotesDownload.
How to use
Use a function like any standard Excel function from any cell. Refer to another cell for the ticker value or pass a hardcoded string.
Press enter and…
Links to sources:
- API documentation on google code
- Video Tutorial by DontFretBred
- Using the Yahoo Finance API for CSV
- Detailed API documentation by Clif Ngan