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 String, txt As String
Dim r As Integer, a As Single, b As String
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 = ""
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
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 features.xls

(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