Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming help
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=2...&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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming help
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 -- Regards, Tom Ogilvy Me wrote in message ... 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=2...&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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming help
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 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=2...&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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming help
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. -----Original Message----- 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming help
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? -- Regards, Tom Ogilvy "David" wrote in message ... 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. -----Original Message----- 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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming help
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 On Mon, 22 Dec 2003 23:24:14 -0500, "Tom Ogilvy" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming help <--One problem though
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? On Wed, 24 Dec 2003 02:02:16 GMT, Me wrote: 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 On Mon, 22 Dec 2003 23:24:14 -0500, "Tom Ogilvy" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming help
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. -- Regards, Tom Ogilvy Me wrote in message ... 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 On Mon, 22 Dec 2003 23:24:14 -0500, "Tom Ogilvy" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming help
"Tom Ogilvy" 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? -- Regards, Tom Ogilvy 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] |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming help <--One problem though
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. -- Regards, Tom Ogilvy Me wrote in message ... 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? On Wed, 24 Dec 2003 02:02:16 GMT, Me wrote: 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 On Mon, 22 Dec 2003 23:24:14 -0500, "Tom Ogilvy" wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming help
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming help
"Don Guillett" wrote in message ...
Did you look at my post? 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming help <--One problem though
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? On Wed, 24 Dec 2003 15:24:20 -0500, "Tom Ogilvy" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programming | Excel Discussion (Misc queries) | |||
CD Programming | Excel Discussion (Misc queries) | |||
Programming help | Excel Discussion (Misc queries) | |||
vba programming | Excel Programming | |||
How to add via programming ? | Excel Programming |