On Apr 20, 11:52*am, steve wrote:
I have a macro which I wrote in Excel 2007 which is supposed to go to
the yahoo financial site and download stock ticker information for a
specified date range. It has a main sheet called NYSEMasterlist which
has 16 columns of stock ticker codes. Also on the sheet in cells S2-S4
it has the beginning date by month, day, and year, and in cells S6-S8
the ending date by month, day, and year. It takes column A and copies
it to a sheet called Insurt, then using the date range on the
NYSEMasterlist sheet, it goes to the site, downloads the stockticker
quotes for that stock using the date range mentioned above in .csv
format. It then saves the the information onto my computer with a .xls
format with the ticker code as the name of the file. It then deletes
the stock ticker from row 1 in the Insurt sheet. It then grabs the
next ticker and does the same thing. When it runs out of ticker codes,
it goes back to the NYSEMasterlist sheet and copies column B to the
Insurt worksheet and continues.

This works fine until there is no ticker information on the website.
Then it gives me an error and stops. So I put in error correction and
tested it by putting 3 bogus tickers in the list and everything seemed
to work fine when I ran a list of 50 tickers with the 3 bogus tickers
in them. But when I tried running through the entire list. After it
gets about half way through column B, the program goes to a blank
excel screen and when I close it, it says there is also a table open
called table63854.csv, which is how the information originally is
stored on the yahoo site open, even though I see no table open. There
is obviously an error in my error correction sections. Below is listed
my code. Any help would be appreciated.

In the code below, there are listed comments. There are no comments in
this code. When I copied the macro in, it took the first " and changed
it to a '.

Sub NYSEDownloader3()
Dim x As Integer
Dim cellvalue1
Dim myFilename As String
Dim StartMonth As String
Dim StartDay As String
Dim StartYear As String
Dim EndMonth As String
Dim EndDay As String
Dim EndYear As String

StartMonth = Worksheets("NYSEMasterList").Range("S2").Value
StartDay = Worksheets("NYSEMasterList").Range("S3").Value
StartYear = Worksheets("NYSEMasterList").Range("S4").Value

EndMonth = Worksheets("NYSEMasterList").Range("S6").Value
EndDay = Worksheets("NYSEMasterList").Range("S7").Value
EndYear = Worksheets("NYSEMasterList").Range("S8").Value

For x = 1 To 16
* * Sheets("Insurt").Select
* * Range("A1").Select

* * Do While IsEmpty(Range("A1")) = False
* * * * On Error GoTo frys
* * * * cellvalue1 = Range("A1")
* * * * Range("C1").Select
* * * * ActiveSheet.Hyperlinks.Add anchor:=Selection, Address:= _
* * * * * * * "http://ichart.finance.yahoo.com/table.csv?s=" & _
* * * * cellvalue1 & "&a=" & StartMonth & "&b=" & StartDay & "&c=" &
StartYear & "&d=" & EndMonth & "&e=" _
* * * * & EndDay & "&f=" & EndYear & "&g=d&ignore=.csv", _
* * * * TextToDisplay:="http://ichart.finance.yahoo.com/table.csv?s="
& _
* * * * cellvalue1 & "&a=" & StartMonth & "&b=" & StartDay & "&c=" &
StartYear & "&d=" & EndMonth & "&e=" _
* * * * & EndDay & "&f=" & EndYear & "&g=d&ignore=.csv"
* * * * Range("A1").Select
* * * * Selection.Copy
* * * * Range("C1").Select

* * * * On Error Resume Next
* * * * Selection.Hyperlinks(1).Follow NewWindow:=False,
addhistory:=True

* * * * If Err.Number < 0 Then GoTo frys
* * * * ActiveWindow.Visible = False
* * * * Windows("Stocktracker.xlsm").Activate
* * * * Sheets("Insurt").Select
* * Range("A1").Select
* * Selection.Copy
* * Windows("table.csv").Visible = True
* * Range("M1").Select
* * ActiveSheet.Paste
* * myFilename = Range("M1")
* * ActiveWorkbook.SaveAs myFilename, FileFormat:= _
* * xlNormal, Password:="", writerespassword:="",
ReadOnlyRecommended:=False _
* * , CreateBackup:=False
* * ActiveWindow.Close

frys:
* * On Error GoTo 0
* * Rows("1:1").Select
* * Selection.Delete shift:=xlUp
* * Range("A1").Select

* * Loop

* * Sheets("NYSEMasterList").Select
* * Range(Cells(1, x), Cells(400, x)).Select
* * Selection.Copy
* * Sheets("Insurt").Select
* * Range("A1").Select
* * Selection.Insert shift:=xlDown
* * Next x

End Sub

I write a lot of code to get financial data from Yahoo. It appears
that there could be a better way. I would need to see the file.

"If desired, send your file to dguillett1 @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."