Error with macro trying to automate stock history download

[jpsmith1]

I am trying to write a macro that will automate the downloading of stock histories. I have a spreadsheet that you enter in the start and end date for the data and then uses the tickers in row 7 across the columns. I am just about there, but I get an error trying to paste the second ticker no matter if I start with column 2 or any other. Anyone know the root cause of this error?

Spreadsheet

Sub CollectData()
' This macro downloads stock data from Yahoo
Dim Page, Smonth, Sday, Syear, Fmonth, Fday, Fyear, Ticker As String
Dim finalrow, finalcolumn, c, finalrow2, r As Integer
Sheets("Data").Select
finalrow = Range("A65536").End(xlUp).Row
finalcolumn = Range("IV4").End(xlToLeft).Column
Smonth = Range("B4").Value - 1
Fmonth = Range("B5").Value - 1
Sday = Range("D4").Value
Fday = Range("D5").Value
Syear = Range("F4").Value
Fyear = Range("F5").Value
r = 7
For c = 3 To 4 ' change to finalcolumn when it works
  Windows("Portfolio.xls").Activate
  Sheets("Data").Select
  Ticker = Cells(r, c).Value
  On Error Resume Next
  Program = "C:\Program Files\Internet Explorer\IEXPLORE.EXE"
  TaskID = Shell(Program, 1)
  If Err <> 0 Then
    MsgBox "Cannot start " & Program, vbCritical, "Error"
  End If
  Application.Wait Now + TimeSerial(0, 0, 3)
  SendKeys "%+FO"
  SendKeys "http://ichart.finance.yahoo.com/table.csv?s=" & Ticker & "&a=" & Fmonth & "&b=" & Fday & "&c=" & Fyear & "&d=" & Smonth & "&e=" & Sday & "&f=" & Syear & "&g=w&ignore=.csv"
  SendKeys "{enter}"
  Application.Wait Now + TimeSerial(0, 0, 1)
  SendKeys "S"
  Application.Wait Now + TimeSerial(0, 0, 3)
  SendKeys "c:\StockTable.csv{enter}"
  Application.Wait Now + TimeSerial(0, 0, 3)
  SendKeys "Y" 'to overwrite existing file
  SendKeys "%+FC"
  Application.Wait Now + TimeSerial(0, 0, 1)
  Workbooks.Open Filename:="c:\StockTable.csv"
  finalrow2 = Range("A65536").End(xlUp).Row
  Range("G2").Select
  Range(Selection, Selection.End(xlDown)).Select
  Selection.Copy
  Windows("Portfolio.xls").Activate
  Cells(8, c).Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  Windows("StockTable.csv").Activate
  If c = 2 Then
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Portfolio.xls").Activate
    Range("A8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("StockTable.csv").Activate
    End If
 '   Windows("StockTable.csv").Close False
  SendKeys "%+FC"
  SendKeys "N"
  Application.Wait Now + TimeSerial(0, 0, 3)
Next c

  
End Sub

 

[jpsmith1]

Bump for help. Thanks!

 

[jpsmith1]

Bump

 

[Norie]

I've no idea why you are getting that error, I don't actually think I've seen that one before.

There are other ways to automate web pages and if you do a board search you'll find plenty of examples.

Try searching on the username Right_Click, or even my own.

PS It's generally regarded that using SendKeys is a bad idea.

 

[jpsmith1]

Thanks. I tried doing some searching, but didn't find anything. I will look again and look for messages from you and right click. I taught myself how to write macros by recording some and then coming here when I got stuck. I will see if I can find the posts with good examples of how to do it without sendkeys.

 

[Smitty]

Excel has this functionality built in.

Goto Data-->Import External Data-->Import Data-->MSN MoneyCentral Investor Stock Quotes.iqy

Follow the wizard from there.

It will get you away from Send Keys and it's very reliable.

HTH,

SMitty

 

[jpsmith1]

Thank you. I will look at that too.

 

[jpsmith1]

Excel has this functionality built in.

Goto Data-->Import External Data-->Import Data-->MSN MoneyCentral Investor Stock Quotes.iqy

Follow the wizard from there.

It will get you away from Send Keys and it's very reliable.

HTH,

SMitty

I didn't know that function even existed. It is a neat tool, but what I really need are the weekly stock prices over a variable amount of time so that I can crunch data on many stocks and once in order to pick the best combination for a portfolio. I can do it by hand using Yahoo data, but it is a pain in the *** to try to collect the data on 50+ stocks at once.

 

[Legacy 98055]

Tagging

 

[jpsmith1]

OK, using search function to see what 'tagging' gets me. This place rocks. Thanks for the help.