This post describes how to import historical stock quotes from yahoo. This custom function is more advanced than the previous one: Excel udf: Import historical stock prices from yahoo
You can choose:
- Start date
- End date
- Interval (daily, weekly and monthly)
- Stock symbol ticker
Function YahooStockQuotes(Fyear As String, Fmonth As String, Fday As String _ , Tyear As String, Tmonth As String, Tday As String, interval As String _ , ticker As String) Dim url As String, http As Object Dim csv As String, temp() As Variant, txt As String Dim r As Integer, a As Single, b As String Dim chk As Boolean ReDim temp(6, 0) url = "http://ichart.finance.yahoo.com/table.csv?s=" & ticker & _ "&d=" & Tmonth - 1 & "&e=" & Tday & "&f=" & Tyear _ & "&d=d&a=" & Fmonth - 1 & "&b=" & Fday & "&c=" & Fyear & "&g=" & interval & "&ignore=.csv" On Error Resume Next Set http = CreateObject("MSXML2.XMLHTTP") http.Open "GET", url, False http.Send csv = http.responseText r = 0 txt = "" chk = False For a = 1 To Len(csv) b = Mid(csv, a, 1) If b = "," Then temp(r, UBound(temp, 2)) = txt If chk = True Then temp(r, UBound(temp, 2)) = CDec(temp(r, UBound(temp, 2))) r = r + 1 txt = "" ElseIf b = Chr(10) Then temp(r, UBound(temp, 2)) = txt If chk = True Then temp(r, UBound(temp, 2)) = CDec(temp(r, UBound(temp, 2))) ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1) txt = "" r = 0 chk = True Else txt = txt & b End If 'If a = 200 Then Exit For Next a ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) - 1) 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 B6
- Copy (Ctrl + c) and paste (Ctrl + v) custom function into formula bar.
- 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
- Excel udf: Import historical stock prices from yahoo