Tom’s Tutorials For Excel: Taking Stock

Tom’s Tutorials For Excel: Taking Stock

Here’s a fast and easy way to download the daily pricing activity of a company’s stock, from the first day it went public up to its last closing date. The following macro uses Microsoft as an example.

Microsoft’s stock abbreviation is MSFT as defined by the strSymbol variable.
The stock debuted on March 13, 1986. Identify iYearFirst as 1986. Identify the month by subtracting 1 from its month number. March is month #3, so the iMonthFirst variable = 2.
The iDayFirst variable is simply 13.

For the last closing day, the current date defines the year, month, and day components.

Sub TakingStock()

'Declare variables.
Dim strYahoo As String, strSymbol As String
Dim iYearFirst As Integer, iMonthFirst As Integer, iDayFirst As Integer
Dim iYearLast As Integer, iMonthLast As Integer, iDayLast As Integer

'Define variables.
strYahoo = "ichart.finance.yahoo.com/table.csv?s="
strSymbol = "MSFT"
iYearLast = Year(Date): iMonthLast = Month(Date): iDayLast = Day(Date)
iYearFirst = 1986: iMonthFirst = 2: iDayFirst = 13

'Open your stock compilation workbook from Yahoo.
Workbooks.Open Filename:= _
"http://" & strYahoo & strSymbol & _
"&d=" & iMonthLast & "&e=" & iDayLast & "&f=" & iYearLast & _
"&g=d&a=" & iMonthFirst & "&b=" & iDayFirst & "&c=" & iYearFirst & _
"&ignore=.csv"
Columns.AutoFit

End Sub