Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Correct a Stock Option Download Script
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default Correct a Stock Option Download Script
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,337
Default Correct a Stock Option Download Script
I Sent workbook privately.

"JB" wrote in message
om...
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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Where it is possible to download MSN MoneyCentral stock quotes Philippe Excel Discussion (Misc queries) 1 May 28th 10 03:04 AM
How do I make a web query file to download stock data? Samie New Users to Excel 1 July 22nd 07 01:10 PM
download stock quotes in excel? rr76012 Excel Discussion (Misc queries) 1 April 8th 07 08:32 AM
How do you download reatime stock prices into a spreadsheet? Buzz Bennett Excel Worksheet Functions 2 February 26th 07 03:12 PM
Can't Download Data in Thai Script Victor Viguilla Excel Discussion (Misc queries) 0 June 1st 06 03:51 AM


All times are GMT +1. The time now is 10:23 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement