Error with macro trying to automate stock history download

jpsmith1

Board Regular
Joined
Aug 4, 2003
Messages
61
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?

error.jpg

Spreadsheet

Code:
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 4, 2003
Messages
61

ADVERTISEMENT

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

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
Aug 4, 2003
Messages
61

ADVERTISEMENT

Thank you. I will look at that too.
 

jpsmith1

Board Regular
Joined
Aug 4, 2003
Messages
61
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.
 

jpsmith1

Board Regular
Joined
Aug 4, 2003
Messages
61
OK, using search function to see what 'tagging' gets me. This place rocks. Thanks for the help.
 

Forum statistics

Threads
1,141,483
Messages
5,706,649
Members
421,460
Latest member
Taamrak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top