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

VBA code


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?

  1. Press Alt-F11 to open visual basic editor
  2. Click Module on the Insert menu
  3. Copy and paste vba code.
  4. Exit visual basic editor

How to create an array formula

  1. Select cell B6
  2. Copy (Ctrl + c) and paste (Ctrl + v) custom function into formula bar.
  3. Press and hold Ctrl + Shift.
  4. Press Enter once.
  5. Release all keys.

How to copy array formula

  1. Select cell B2
  2. Copy (Ctrl + c)
  3. Select cell range B2:H28
  4. Paste (Ctrl + v)

Download example file.

YahooStockQuotes-added-featuresv2.xlsm

(Excel 97-2003 Workbook *.xls)

Recommended blog posts

Want to know more about excel and stocks? You must read these blog posts: