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.

VBA Code

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?

  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 B2
  2. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
    Array formula:  =YahooStockQuotes("msft")
  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.xls

(Excel 97-2003 Workbook *.xls)

Recommended blog posts

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

Related posts:

Excel udf: Import historical stock prices from yahoo – added features

Adjust stock chart axis automatically

Learn how to create a stock chart in excel

Compare your stock portfolio with S&P500 in excel

Calculate your stock portfolio performance with Net Asset Value based on units in excel