To Whomever Knows the Answer:
I wrote a small program that goes out to the internet and looks up the
historical prices over a 10 year period from Yahoo! Finance
(
www.finance.yahoo.com). The data can be found on the following URL:
http://finance.yahoo.com/q/hp?s=LLTC (you can input any ticker symbol
you want). From the URL you can change the date range, click "Get
Prices", and then scroll to the bottom of the page and click "Download
To Spreadsheet." A window then pops up asking if you want to open or
save the file. (The window's characteristics are Name: table.csv;
Type: Microsoft Excel Worksheet; From: ichart.finance.yahoo.com). If
you click open, the table will open inside the internet browser and the
aesthetics of the page look similar to Excel itself. At this point you
can copy the data set and paste it into Excel.
I'm wondering if there is a way to automate the above mentioned
process. Below you will find code that takes you as far as the pop-up
window. I'm not sure what to do after that. I'm making a wild guess
that there is a way to recognize the pop-up window, manipulate the
pop-up window, and then copy the output from the internet explorer to
Excel.
I wrote a function in order to grab two place holding values for date
information. The source code for the
http://finance.yahoo.com/q/hp?s=LLTC website reads January as a 00,
February as 01... and the function is the only way I could quickly
think of to have the program maintain 2 place holders rather than
interpreting a 00 as 0.
Any help is most appreciated.
Thank You,
Matt
Sub yahooPrices()
Dim ie As Object
Dim a 'start month
Dim b 'start day
Dim c 'start year
Dim d 'end month
Dim e 'end day
Dim f 'end year
Dim tic 'ticker
Dim mon 'month
Dim dy 'day
Dim yr 'year
Sheets("Yahoo Price Drop").Select
tic = UCase(InputBox("Enter the desire ticker for 10 years of
historical prices.", "Ticker", "APOL"))
mon = Format(DateSerial(Year(Date), Month(Date), day(Date)), "mmm")
dy = Format(DateSerial(Year(Date), Month(Date), day(Date)), "dd")
yr = Format(DateSerial(Year(Date), Month(Date), day(Date)), "yyyy")
'make sure single digit dates are 0x and not just x (e.g. 03 < 3)
a = monthToNumber(mon)
b = dy
c = yr - 10
d = a
e = b
f = yr
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
ie.navigate "http://ichart.finance.yahoo.com/table.csv?s=" & tic &
"&a=" & a & "&b=" & b & "&c=" & c & "&d=" & d & "&e=" & e & "&f=" & f &
"&g=d&ignore=.csv"
'Application.DisplayAlerts = False
'SendKeys "%o"
End Sub
Private Function monthToNumber(ByVal mon)
Select Case mon
Case "Jan"
monthToNumber = Right(100, 2)
Case "Feb"
monthToNumber = Right(101, 2)
Case "Mar"
monthToNumber = Right(102, 2)
Case "Apr"
monthToNumber = Right(103, 2)
Case "May"
monthToNumber = Right(104, 2)
Case "Jun"
monthToNumber = Right(105, 2)
Case "Jul"
monthToNumber = Right(106, 2)
Case "Aug"
monthToNumber = Right(107, 2)
Case "Sep"
monthToNumber = Right(108, 2)
Case "Oct"
monthToNumber = Right(109, 2)
Case "Nov"
monthToNumber = Right(110, 2)
Case "Dec"
monthToNumber = Right(111, 2)
End Select
End Function