Programming help

  • Thread starter Thread starter Me
  • Start date Start date
M

Me

I'm fairly new to Excel and VBA.

Here is the situation:

1. I have a spreadsheet containing the symbols for the S&P500 index in
A2:A501.

2. I can download data for a single symbol using the yahoo website:

http://chart.yahoo.com/d?a=9&b=1&c=2002&d=5&e=30&f=2004&g=d&s=

which will give me a link at the bottom of the page to download 200
days of data in .CSV format.

3. I need to collect the data for all of the 500 symbols and save them
to a /DATA driectory as *.csv

4. I need to be able to do daily updates

5. I need to be able to automate this whole process

Any help appreciated
 
Sub Tester3()
Dim cell As Range
Dim wkbk As Workbook
For Each cell In Range("A1:A500")
sName = "C:\Data\" & cell.Value & ".csv"
Set wkbk = _
Workbooks.Open( _
"http://chart.yahoo.com/table.csv?a=9&b=1&" & _
"c=2002&d=5&e=30&f=2004&s=" & _
cell.Value & _
"&y=0&g=d&ignore=.csv")
On Error Resume Next
' delete existing file with this name
Kill sName
On Error GoTo 0
wkbk.SaveAs FileName:=sName, _
FileFormat:=xlCSV
wkbk.Close SaveChanges:=False
Next
End Sub
 
I recommend that you got a good book on Excel/ VBA as a starting point. I
am using "Excel 2000 VBA" by John Green. Not bad, if you have some
programming background.
After puzzling around with it a bit, you will be able to formulate questions
that allow more concise answers.

Ed
 
No need to create a csv workbook for each symbol. You could use
querytables.add instead to import to a data worksheet and then copy/paste to
a sheet that shows all symbols, side by side. Here is part of my macro that
does this.
You can also use a double click event to graph the results.
Date ^TYX ^TnX ^FvX ^IRX ^OTX ^dji
6/20/2003 4.46 3.40 2.30 0.81
151.08 9,200.75
6/19/2003 4.42 3.34 2.24 0.79
151.72 9,179.53
6/18/2003 4.39 3.36 2.30 0.86
153.98 9,293.80
6/17/2003 4.30 3.27 2.22 0.87
151.33 9,323.02
6/16/2003 4.22 3.17 2.11 0.84
151.26 9,318.96


For Each c In [SL]
On Error Resume Next
strurl = "http://table.finance.yahoo.com/table.csv?a=" & StartMo & "&b=" &
StartDay & "&c=" & StartYr & "&d=" & StopMo & "&e=" & StopDay & "&f=" &
StopYr & "&y=0&g=" & [e2] & "&s=" & c & ""

With ActiveWorkbook.Worksheets("Data").QueryTables.Add( _
Connection:="URL;" & strurl, Destination:=Worksheets("Data").Cells(1,
1))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
copy/paste code here
Next
 
I went to the site that has the data. What the problem
appears to be to me is suppling symbols, individually to
the site and then then getting rid of the extraneous
data, since you only want the close. Then it becomes more
complicated, because you will then need to find the
symbol on your existing worksheet and give it your "new"
close. Seems at a minimum, what you need to do is
oriented to the website, as much as it is to Excel and
that a list of the symbols to look up would be needed.
Even with this start, part of what you are thrying to do
is web based.
 
I didn't see anything in the original post that said he only wanted the
close. Was there a subsequent post that said this? Perhaps it isn't
showing up for me. Or was that implicit in the URL?
 
TOM: You the MAN!

This is exactly what I wanted. Works great.
Let me ask you a couple of questions about this code if I could.

I assume I can change the 4th line to A1:A5000 if I wanted to
download a larger exchange like the NASDAQ?

Also, is there anyway to modify this code so it will download more
than the 200 files? As you can see, if you click additional links,
you can download an additional 200 files at a time.

Thanks again for your help
 
One problem I'm finding is that yahoo limits a batch download to 237
symbols. I don't suppose there is a way around that, huh?
 
Yes, you can change A1:A500 to A1:A5000 as long as you put valid symbols in
each cell.

The link at the bottom is to **look at** the next 200 records. However, the
data is determined by the dates at the top. With the dates in your sample
query, the CSV file contains 312 records. Look at Don's code to see how to
send the start and stop dates in the URL. This will determine how many
records are contained in the files.
 
Tom Ogilvy said:
I didn't see anything in the original post that said he only wanted the
close. Was there a subsequent post that said this? Perhaps it isn't
showing up for me. Or was that implicit in the URL?
Click to expand...

I guess the original poster did not say so, but I was looking
for something exactly like this.

I have a set of symbols in the columns (I guess I can change A1:A5
to A1:E1) and just want to get the adjusted closing price for the
past year in the columns below the symbols. This is the last column
in the spreadsheet on yahoo.

Eternally grateful to the kind soul who can provide this. This is
just too difficult for my present non-programming-oriented mind.

Kaspa [Only did some FORTRAN programming 15 years ago]
 
The code I provided wasn't doing a batch download - it was doing one symbol
at a time based on the URL you posted. Perhaps you are looking at another
URL.
 
Did you look at my post?

--
Don Guillett
SalesAid Software
(e-mail address removed)
The Chess Addict said:
"Tom Ogilvy" <[email protected]> wrote in message
I didn't see anything in the original post that said he only wanted the
close. Was there a subsequent post that said this? Perhaps it isn't
showing up for me. Or was that implicit in the URL?
Click to expand...

I guess the original poster did not say so, but I was looking
for something exactly like this.

I have a set of symbols in the columns (I guess I can change A1:A5
to A1:E1) and just want to get the adjusted closing price for the
past year in the columns below the symbols. This is the last column
in the spreadsheet on yahoo.

Eternally grateful to the kind soul who can provide this. This is
just too difficult for my present non-programming-oriented mind.

Kaspa [Only did some FORTRAN programming 15 years ago]
Click to expand...
 
Don Guillett said:
Did you look at my post?
Click to expand...

Yes, I did, but I am not quite familiar with this
programming. It looks like I have to insert some more
code in your macro (you have mentioned add code here). I tried
to merge your code with Tom's solution, without much luck.
I guess I don't understand some parts of the code.

Tom's code runs well, but it creates too many CSV files and it
is harder to import only one column from them to the main
spreadsheet (at least for me).

Thanks for all your help.

Kaspa
 
Hi Tom,

OK now you have me confused. Your code DOES do a batch download
but stops after it gets to the 237th symbol. If you remember, column
A1:A500 contains all of the S&P500 symbols. What would be the
sense in doing one symbol at a time?
 
Back
Top