error correction on vba macro

S

steve

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
 
D

Donald Guillett

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
Click to expand...

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."
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Historical Stock Quotes - Yahoo Finance 4
VBA for Dependents shortcut menu 0
Why do I have to restart Excel when ActiveSheet.QueryTables.Addfails? Options 5
VBA Pivot Table Error 1
click an internet pop-up "OK" button 2
Copy filtered data to sheet 2 1
Find the correct sheet then find a value on that sheet 8
MACRO Effeciency 3

Top