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
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.
iDayFirst variable is simply 13.
For the last closing day, the current date defines the year, month, and day components.
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
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 & _