Code to get data from Yahoo Finance

[TEB2]

If I paste the following code in the address bar in IE, I get a window asking
me if I want to open or save a csv file which has the monthly highs for the
stock for the time period specified:

http://ichart.finance.yahoo.com/table.csv?s=(insert stock
symbol)&a=12&b=01&c=2007&d=01&e=01&f=2009&g=m&ignore=.csv

Here's the issue. I have a list of 332 stock symbols in a spreadsheet. I
need the code that will insert the first stock symbol after "s=" in the above
address, save the file to a specific folder and loop thru the list till the
end.

I have to evaluate these stocks on a monthly basis to determine the
market-to-cost adjustment. This would be my final piece to fully automate
the process.

Thanks for your help!

Tom

 

[Don Guillett]

If you send ME your email address I will send you a file of mine that does
this for you. All you do is input the symbol . Or, send me your file and
I'll take a look.
Symbol Name Basis
Shares Last Trade Value Profit
IBM INTL BUSINESS MAC $ 100.00 100.00 $
90.86 $ 9,086.00 $ 8,986.00
T AT&T INC. $ 100.00 100.00 $ 26.21 $
2,621.00 $ 2,521.00
MSFT MICROSOFT CP - $ 24.12
-
FE FIRSTENERGY CP - $ 52.74
-
cm-pd.to CANADIAN IMP BANK - $ 20.19
$ -
^GSPC S&P 500 INDEX,RTH - $ 963.20
-

 

[Charles L. Snyder]

This retired EE Prof has hundreds of excel spreadsheets, all freely
downloadable, that will do what you want and more:

http://www.gummy-stuff.org/

cls

 

[Joel]

Can anybody tell me why I can't get the sendkey to work in this code. I put
the Stock symbol in the Active worksheet in cell A1. I just can't get the
file saved properly.


Sub GetStocks()
Const READYSTATE_LOADING = 1
Folder = "c:\Temp\"
URL1 = "http://ichart.finance.yahoo.com/table.csv?s="
URL2 = "&a=12&b=01&c=2007&d=01&e=01&f=2009&g=m&ignore=.csv"

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

RowCount = 1
Do While Range("A" & RowCount) <> ""
Stock = Range("A" & RowCount)
On Error Resume Next
Kill (Folder & Stock & ".csv")
On Error GoTo 0
'get web page
IE.Navigate2 URL1 & Stock & URL2
Do While IE.readystate <> READYSTATE_LOADING
DoEvents
Loop

'Loop
Application.Wait (Now + TimeValue("0:00:05"))
AppActivate "File Download", True

'Alt S - Save File
Application.SendKeys "%S", True
'Alt n - Enter filename
Application.SendKeys "%n", True
Application.SendKeys _
(Folder & Stock & ".csv{ENTER}"), True



RowCount = RowCount + 1
Loop


End Sub