Correct a Stock Option Download Script

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

Don Guillett

I Sent workbook privately.

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

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

Named Range maker code 9
Referencing a cell as a row number 5
Deleting blank (Cells/Rows) in Excel-VBA 1
VBA Pivot Table Error 1
File Name changes with dates and Deleting Rows of Zeros 0
InputBox to name a range 2
Resize Range for Column 3
Issue with Solver 3

Top