Here is how to quickly import historical stock quotes from yahoo.
First you need to know the company ticker. Go to Yahoo Finance and type the company name here.
Microsoft has symbol ticker msft. Use the argument msft in the custom function to import historical stock prices.
Function YahooStockQuotes(ticker As String) Dim url As String, today As Date, http As Object Dim csv As String, temp() As String, txt As String Dim r As Integer, a As Single, b As String ReDim temp(6, 0) today = Date url = "http://ichart.finance.yahoo.com/table.csv?s=" & ticker & _ "&d=" & Month(today) - 1 & "&e=" & Day(today) & "&f=" & Year(today) & "&g=d&a=0&b=1&c=2003&ignore=.csv" On Error Resume Next Set http = CreateObject("MSXML2.XMLHTTP") http.Open "GET", url, False http.Send csv = http.responseText r = 0 txt = "" For a = 1 To Len(csv) b = Mid(csv, a, 1) If b = "," Then temp(r, UBound(temp, 2)) = txt r = r + 1 txt = "" ElseIf b = Chr(10) Then temp(r, UBound(temp, 2)) = txt ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1) txt = "" r = 0 Else txt = txt & b End If Next a YahooStockQuotes = Application.Transpose(temp) Set http = Nothing End Function
Where to copy vba code?
- Press Alt-F11 to open visual basic editor
- Click Module on the Insert menu
- Copy and paste vba code.
- Exit visual basic editor
How to create an array formula
- Select cell B2
- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
Array formula: =YahooStockQuotes("msft")
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.
How to copy array formula
- Select cell B2
- Copy (Ctrl + c)
- Select cell range B2:H28
- Paste (Ctrl + v)
Download example file.
(Excel 97-2003 Workbook *.xls)
Recommended blog posts
Want to know more about excel and stocks? You must read these blog posts:
- Tracking a stock portfolio #2 in excel
- Tracking a stock portfolio in excel (auto update)
- Calculate your stock portfolio performance with Net Asset Value based on units in excel
- Automate net asset value (NAV) calculation on your stock portfolio (vba) in excel
- Calculate your stock portfolio performance in excel
- Dynamic stock chart in excel – Add date ranges
- Create a dynamic stock chart using a web query and a drop down list in excel
- Scan stock markets in excel
- Stock alerts in excel
- Learn how to create a stock chart in excel