Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, Yahoo ichart csv file
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I get a Format error when converting my yahoo contacts to CSV file | Excel Discussion (Misc queries) | |||
How do I email an excel file via yahoo? | Excel Discussion (Misc queries) | |||
How do I set up a shared excel file on my Yahoo group site? | Excel Discussion (Misc queries) | |||
Sort CSV file from Yahoo Finance Query | Excel Programming | |||
vba yahoo! finance | Excel Programming |