J
JB
Hello All,
I have been running the following VBA script to pull stock and stock
option prices from Yahoo. It works fine for stocks e.g. IBM, QQQ,
MRK, PFE,S,A etc. It does not work for the options e.g. QAVJD.X,
QAVJC.X. Until approximately 10/05/2003 it did work for both stocks
and options. I modified the macro slightly to get it to work for
stocks.
For some reason when I run the "Master" script it will not post the
option prices to the "home" page (Tab) even though the information is
captured on the ‘Website" page (tab).
I get a run-time error 1004 Application-defined or object defined
error. When I click on the End box the stock prices are posted on the
"Home" page but the option prices are not.
Can anyone tell me how to fix this?
Or is there a better way to download stock and option prices. I would
like to put the symbol in col A and have the most current price
available ( delay is ok) be in col B. If the change in price (from
prior day close) could be in col C that would be nice. And/or if the
name of the stock or description of the option could be in col B, the
price in Col C and the Change in price in Col D, that would awesome!
Thanks.
JBESr
Sub setupworksheet()
'this only needs to be run once to set up
'sample data and format worksheet
'this part was set up using the recorder,
'so code may not be efficient
Sheets("sheet1").Select
ActiveWindow.Zoom = 75
Range("a2") = "ticker"
Range("B2") = "datebot"
Range("C2") = "shares"
Range("d2") = "price"
Range("E2") = "cost"
Range("F2") = "last"
Range("G2") = "change"
Range("H2") = "value"
Range("I2") = "todaychg"
Cells.Select
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Rows("2:2").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
With Selection.Interior
.ColorIndex = 49
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
ActiveWorkbook.Names.Add Name:="ticker", _
RefersToR1C1:="=Sheet1!R2C1"
ActiveWorkbook.Names.Add Name:="datebot", _
RefersToR1C1:="=Sheet1!R2C2"
ActiveWorkbook.Names.Add Name:="shares", _
RefersToR1C1:="=Sheet1!R2C3"
ActiveWorkbook.Names.Add Name:="price", _
RefersToR1C1:="=Sheet1!R2C4"
ActiveWorkbook.Names.Add Name:="cost", _
RefersToR1C1:="=Sheet1!R2C5"
ActiveWorkbook.Names.Add Name:="last", _
RefersToR1C1:="=Sheet1!R2C6"
ActiveWorkbook.Names.Add Name:="change", _
RefersToR1C1:="=Sheet1!R2C7"
ActiveWorkbook.Names.Add Name:="value", _
RefersToR1C1:="=Sheet1!R2C8"
ActiveWorkbook.Names.Add Name:="tdaychg", _
RefersToR1C1:="=Sheet1!R2C9"
Range("J2").Select
ActiveWorkbook.Names.Add Name:="profit", _
RefersToR1C1:="=sheet1!R2C10"
ActiveCell.FormulaR1C1 = "profit/loss"
Sheets("Sheet1").Name = "home"
Sheets("Sheet2").Name = "website"
Sheets("Sheet3").Name = "dummy"
Columns("C:C").Select
Selection.NumberFormat = "0"
Columns("D").Select
Selection.NumberFormat = "0.00"
Columns("E:E").Select
Selection.NumberFormat = "$#,##0"
Columns("F:F").Select
Selection.NumberFormat = "0.00"
Columns("G:G").Select
Selection.NumberFormat = "0.00;[Red]0.00"
Columns("H:H").Select
Selection.NumberFormat = "$#,##0.00"
Columns("I:I").Select
Selection.NumberFormat = "$#,##0;[Red]$#,##0"
Range("A1").Select
Selection.NumberFormat = "0"
ActiveWorkbook.Names.Add Name:="counter", _
RefersToR1C1:="=home!R1C1"
Range("A3").Select
ActiveCell.FormulaR1C1 = "FNM"
Range("A4").Select
ActiveCell.FormulaR1C1 = "FRE"
Range("A5").Select
ActiveCell.FormulaR1C1 = "MRK"
Range("A6").Select
ActiveCell.FormulaR1C1 = "PFE"
Range("B3").Select
ActiveCell.FormulaR1C1 = "5/26/2003"
Range("B3:B6").Select
Selection.FillDown
Range("C3").Select
ActiveCell.FormulaR1C1 = "100"
Range("C3:C6").Select
Selection.FillDown
Range("D3").Select
ActiveCell.FormulaR1C1 = "68"
Range("D4").Select
ActiveCell.FormulaR1C1 = "55"
Range("D5").Select
ActiveCell.FormulaR1C1 = "58"
Range("D6").Select
ActiveCell.FormulaR1C1 = "32"
Range("E3").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-2]"
Range("E3:E6").Select
Selection.FillDown
Range("F5").Select
Columns("J:J").Select
Selection.NumberFormat = "$#,##0;[Red]#,##0"
Range("a1").Select
Range("a9") = "you may clear the stock data"
Range("a10") = "and enter up to 99 tickers"
ActiveWorkbook.Names.Add Name:="website", _
RefersToR1C1:="=dummy!R1C1"
End Sub
Sub master()
buildwebsite
scoupdata
findstartpos
readdata
End Sub
Sub buildwebsite()
Dim i As Integer
Dim ticker As String
i = 1
While Range("ticker").Offset(i, 0) <> ""
ticker = ticker & " " & UCase(Range("ticker").Offset(i))
i = i + 1
Wend
Range("website") = _
"URL;http://finance.yahoo.com/q?s=" & ticker & "+&d=v1"
End Sub
Sub scoupdata()
Dim website As String
Application.ScreenUpdating = False
Sheets("website").Select
Cells.Clear
website = Range("website")
With ActiveSheet.QueryTables.Add(Connection:= _
website, _
Destination:=Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Cells.Select
With Selection
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Range("a1").Select
End Sub
Sub findstartpos()
Dim what As String
Cells.Find(what:="Symbol", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:= _
False).Activate
End Sub
Sub readdata()
Dim i As Integer
Dim ticker As String
i = 1
While Range("ticker").Offset(i) <> ""
ticker = UCase(Range("ticker").Offset(i))
While Selection.Value <> ticker
Selection.Offset(1, 0).Select
Wend
Range("last").Offset(i, 0) = Selection.Offset(0, 2)
'Range("change").Offset(i, 0) = Selection.Offset(0, 3)
'Range("value").Offset(i, 0) = _
' Range("shares").Offset(i, 0) * _
' Range("last").Offset(i, 0)
' Range("tdaychg").Offset(i, 1) = _
'Range("change").Offset(i, 1) * _
'Range("shares").Offset(i, 1)
'Range("profit").Offset(i) = _
'Range("value").Offset(i) - Range("cost").Offset(i)
i = i + 1
Wend
Sheets("home").Select
Application.ScreenUpdating = True
End Sub
I have been running the following VBA script to pull stock and stock
option prices from Yahoo. It works fine for stocks e.g. IBM, QQQ,
MRK, PFE,S,A etc. It does not work for the options e.g. QAVJD.X,
QAVJC.X. Until approximately 10/05/2003 it did work for both stocks
and options. I modified the macro slightly to get it to work for
stocks.
For some reason when I run the "Master" script it will not post the
option prices to the "home" page (Tab) even though the information is
captured on the ‘Website" page (tab).
I get a run-time error 1004 Application-defined or object defined
error. When I click on the End box the stock prices are posted on the
"Home" page but the option prices are not.
Can anyone tell me how to fix this?
Or is there a better way to download stock and option prices. I would
like to put the symbol in col A and have the most current price
available ( delay is ok) be in col B. If the change in price (from
prior day close) could be in col C that would be nice. And/or if the
name of the stock or description of the option could be in col B, the
price in Col C and the Change in price in Col D, that would awesome!
Thanks.
JBESr
Sub setupworksheet()
'this only needs to be run once to set up
'sample data and format worksheet
'this part was set up using the recorder,
'so code may not be efficient
Sheets("sheet1").Select
ActiveWindow.Zoom = 75
Range("a2") = "ticker"
Range("B2") = "datebot"
Range("C2") = "shares"
Range("d2") = "price"
Range("E2") = "cost"
Range("F2") = "last"
Range("G2") = "change"
Range("H2") = "value"
Range("I2") = "todaychg"
Cells.Select
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Rows("2:2").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
With Selection.Interior
.ColorIndex = 49
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
ActiveWorkbook.Names.Add Name:="ticker", _
RefersToR1C1:="=Sheet1!R2C1"
ActiveWorkbook.Names.Add Name:="datebot", _
RefersToR1C1:="=Sheet1!R2C2"
ActiveWorkbook.Names.Add Name:="shares", _
RefersToR1C1:="=Sheet1!R2C3"
ActiveWorkbook.Names.Add Name:="price", _
RefersToR1C1:="=Sheet1!R2C4"
ActiveWorkbook.Names.Add Name:="cost", _
RefersToR1C1:="=Sheet1!R2C5"
ActiveWorkbook.Names.Add Name:="last", _
RefersToR1C1:="=Sheet1!R2C6"
ActiveWorkbook.Names.Add Name:="change", _
RefersToR1C1:="=Sheet1!R2C7"
ActiveWorkbook.Names.Add Name:="value", _
RefersToR1C1:="=Sheet1!R2C8"
ActiveWorkbook.Names.Add Name:="tdaychg", _
RefersToR1C1:="=Sheet1!R2C9"
Range("J2").Select
ActiveWorkbook.Names.Add Name:="profit", _
RefersToR1C1:="=sheet1!R2C10"
ActiveCell.FormulaR1C1 = "profit/loss"
Sheets("Sheet1").Name = "home"
Sheets("Sheet2").Name = "website"
Sheets("Sheet3").Name = "dummy"
Columns("C:C").Select
Selection.NumberFormat = "0"
Columns("D").Select
Selection.NumberFormat = "0.00"
Columns("E:E").Select
Selection.NumberFormat = "$#,##0"
Columns("F:F").Select
Selection.NumberFormat = "0.00"
Columns("G:G").Select
Selection.NumberFormat = "0.00;[Red]0.00"
Columns("H:H").Select
Selection.NumberFormat = "$#,##0.00"
Columns("I:I").Select
Selection.NumberFormat = "$#,##0;[Red]$#,##0"
Range("A1").Select
Selection.NumberFormat = "0"
ActiveWorkbook.Names.Add Name:="counter", _
RefersToR1C1:="=home!R1C1"
Range("A3").Select
ActiveCell.FormulaR1C1 = "FNM"
Range("A4").Select
ActiveCell.FormulaR1C1 = "FRE"
Range("A5").Select
ActiveCell.FormulaR1C1 = "MRK"
Range("A6").Select
ActiveCell.FormulaR1C1 = "PFE"
Range("B3").Select
ActiveCell.FormulaR1C1 = "5/26/2003"
Range("B3:B6").Select
Selection.FillDown
Range("C3").Select
ActiveCell.FormulaR1C1 = "100"
Range("C3:C6").Select
Selection.FillDown
Range("D3").Select
ActiveCell.FormulaR1C1 = "68"
Range("D4").Select
ActiveCell.FormulaR1C1 = "55"
Range("D5").Select
ActiveCell.FormulaR1C1 = "58"
Range("D6").Select
ActiveCell.FormulaR1C1 = "32"
Range("E3").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-2]"
Range("E3:E6").Select
Selection.FillDown
Range("F5").Select
Columns("J:J").Select
Selection.NumberFormat = "$#,##0;[Red]#,##0"
Range("a1").Select
Range("a9") = "you may clear the stock data"
Range("a10") = "and enter up to 99 tickers"
ActiveWorkbook.Names.Add Name:="website", _
RefersToR1C1:="=dummy!R1C1"
End Sub
Sub master()
buildwebsite
scoupdata
findstartpos
readdata
End Sub
Sub buildwebsite()
Dim i As Integer
Dim ticker As String
i = 1
While Range("ticker").Offset(i, 0) <> ""
ticker = ticker & " " & UCase(Range("ticker").Offset(i))
i = i + 1
Wend
Range("website") = _
"URL;http://finance.yahoo.com/q?s=" & ticker & "+&d=v1"
End Sub
Sub scoupdata()
Dim website As String
Application.ScreenUpdating = False
Sheets("website").Select
Cells.Clear
website = Range("website")
With ActiveSheet.QueryTables.Add(Connection:= _
website, _
Destination:=Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Cells.Select
With Selection
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Range("a1").Select
End Sub
Sub findstartpos()
Dim what As String
Cells.Find(what:="Symbol", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:= _
False).Activate
End Sub
Sub readdata()
Dim i As Integer
Dim ticker As String
i = 1
While Range("ticker").Offset(i) <> ""
ticker = UCase(Range("ticker").Offset(i))
While Selection.Value <> ticker
Selection.Offset(1, 0).Select
Wend
Range("last").Offset(i, 0) = Selection.Offset(0, 2)
'Range("change").Offset(i, 0) = Selection.Offset(0, 3)
'Range("value").Offset(i, 0) = _
' Range("shares").Offset(i, 0) * _
' Range("last").Offset(i, 0)
' Range("tdaychg").Offset(i, 1) = _
'Range("change").Offset(i, 1) * _
'Range("shares").Offset(i, 1)
'Range("profit").Offset(i) = _
'Range("value").Offset(i) - Range("cost").Offset(i)
i = i + 1
Wend
Sheets("home").Select
Application.ScreenUpdating = True
End Sub