5 Messages
Digest #3752
Messages
Sat Jul 23, 2016 8:11 am (PDT) . Posted by:
yvrflyguy
Hi Randy,
I'm trying to implement use of the UpdateDownloadTable macro on a data table where given a last quoted price of $xx.xx the closest to the ATM strike is computed.
Right now to do this I use the formula =TRANSPOSE(smfGetOptionStrikes(C5,F5,"P","OX",,2,1)) array entered over two adjacent cells (G5:H5) in a row to determine the ITM1 and OTM1 strikes. I then use =INDEX(G5:H5,MATCH(TRUE,INDEX(ABS(G5:H5-D5)=MIN(INDEX(ABS(G5:H5-D5),,)),,),0)) to determine which is the ATM strike being closest to the current price.
I get how to use the referback reference to insert the expiry date (F5) into the formula, but I can't see a means of getting the array entered formula itself to be compatible with the way that the macro works. Is there a method to do this that I'm not seeing?
I also attempted the alternative process of using either smfGetOptionsQuote or smfGETYahooOptionsQuote with ITM1 and OTM1 inserted as the strikes, but these functions are now returning "error", though I know they've worked for me in the past. By example =smfGetYahooOptionQuote("SPY","C",DATE(2016,8,12),200,"s") works, but
=smfGetYahooOptionQuote("SPY","C",DATE(2016,8,12),"ITM1","s") does not.
Any suggestions on how to solve this would be greatly appreciated.
Thanks,
Peter
I'm trying to implement use of the UpdateDownloadTable macro on a data table where given a last quoted price of $xx.xx the closest to the ATM strike is computed.
Right now to do this I use the formula =TRANSPOSE(smfGetOp
I get how to use the referback reference to insert the expiry date (F5) into the formula, but I can't see a means of getting the array entered formula itself to be compatible with the way that the macro works. Is there a method to do this that I'm not seeing?
I also attempted the alternative process of using either smfGetOptionsQuote or smfGETYahooOptionsQ
=smfGetYahooOptionQ
Any suggestions on how to solve this would be greatly appreciated.
Thanks,
Peter
Sat Jul 23, 2016 8:50 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
In a similar worksheet of mine, I retrieve the option ticker symbols for
the first ITM and first OTM strikes, using:
="INDEX(smfGetOptionStrikes(""~~~~~"",~~~"&COLUMNS(C:J)-1&"~~~,""Call"",""OX"",1,10,1),5)"
="INDEX(smfGetOptionStrikes(""~~~~~"",~~~"&COLUMNS(C:P)-1&"~~~,""Call"",""OX"",1,10,1),6)"
...where column C is what contains my expiration date. Because the
smfGetOptionStrikes() returns 10 strikes, the first 5 will be ITM and the
last 5 will be OTM, so my INDEX() formulas just need to pick up the 5th and
6th prices.
I then use those two option ticker symbols to determine whether the ITM or
the OTM option has the best extrinsic value.
On Sat, Jul 23, 2016 at 8:11 AM, a330pete@
...wrote:
> I'm trying to implement use of the UpdateDownloadTable macro on a data
> table where given a last quoted price of $xx.xx the closest to the ATM
> strike is computed.
>
> Right now to do this I use the formula
> =TRANSPOSE(smfGetOptionStrikes(C5,F5,"P","OX",,2,1)) array entered over two
> adjacent cells (G5:H5) in a row to determine the ITM1 and OTM1 strikes. I
> then use =INDEX(G5:H5,MATCH(TRUE,INDEX(ABS(G5:H5-D5)=MIN(INDEX(ABS(G5:H5-D5),,)),,),0))
> to determine which is the ATM strike being closest to the current price.
>
> I get how to use the referback reference to insert the expiry date (F5)
> into the formula, but I can't see a means of getting the array entered
> formula itself to be compatible with the way that the macro works. Is there
> a method to do this that I'm not seeing?
>
> I also attempted the alternative process of using either
> smfGetOptionsQuote or smfGETYahooOptionsQuote with ITM1 and OTM1 inserted
> as the strikes, but these functions are now returning "error", though I
> know they've worked for me in the past. By example
> =smfGetYahooOptionQuote("SPY","C",DATE(2016,8,12),200,"s") works, but
>
> =smfGetYahooOptionQuote("SPY","C",DATE(2016,8,12),"ITM1","s") does not.
>
> Any suggestions on how to solve this would be greatly appreciated.
>
>
the first ITM and first OTM strikes, using:
="INDEX(
="INDEX(
...where column C is what contains my expiration date. Because the
smfGetOptionStrikes
last 5 will be OTM, so my INDEX() formulas just need to pick up the 5th and
6th prices.
I then use those two option ticker symbols to determine whether the ITM or
the OTM option has the best extrinsic value.
On Sat, Jul 23, 2016 at 8:11 AM, a330pete@
...wrote:
> I'm trying to implement use of the UpdateDownloadTable macro on a data
> table where given a last quoted price of $xx.xx the closest to the ATM
> strike is computed.
>
> Right now to do this I use the formula
> =TRANSPOSE(smfGetOp
> adjacent cells (G5:H5) in a row to determine the ITM1 and OTM1 strikes. I
> then use =INDEX(G5:H5,
> to determine which is the ATM strike being closest to the current price.
>
> I get how to use the referback reference to insert the expiry date (F5)
> into the formula, but I can't see a means of getting the array entered
> formula itself to be compatible with the way that the macro works. Is there
> a method to do this that I'm not seeing?
>
> I also attempted the alternative process of using either
> smfGetOptionsQuote or smfGETYahooOptionsQ
> as the strikes, but these functions are now returning "error"
> know they've worked for me in the past. By example
> =smfGetYahooOptionQ
>
> =smfGetYahooOptionQ
>
> Any suggestions on how to solve this would be greatly appreciated.
>
>
Sat Jul 23, 2016 9:52 pm (PDT) . Posted by:
yvrflyguy
Hi Randy,
Thanks for the quick response. This was just what I was looking for. Thanks also for the insight into the function's process. That will help me in building a few other tailored functions to get the info I need into the spreadsheet.
Cheers,
Peter
Thanks for the quick response. This was just what I was looking for. Thanks also for the insight into the function'
Cheers,
Peter
Sat Jul 23, 2016 10:05 pm (PDT) . Posted by:
joshi_mandar
Hi All,
I am trying to build a model where I am looking for stock quote and option quote from a past date and time. For example: I am looking for price of AAPL at 1 PM ET on July 5th 2016. Also looking for quote for AAPL Jan 18 $90 call at same time. Is there any way to get that using the add-in?
I use RCHGetYahooQuotes within an array to get stock quotes usually.
And I usually use below formula for real-time options quotes.
=smfGetOptionQuotes("aapl Jan 18 90 call","l",0,"Y")
M
I am trying to build a model where I am looking for stock quote and option quote from a past date and time. For example: I am looking for price of AAPL at 1 PM ET on July 5th 2016. Also looking for quote for AAPL Jan 18 $90 call at same time. Is there any way to get that using the add-in?
I use RCHGetYahooQuotes within an array to get stock quotes usually.
And I usually use below formula for real-time options quotes.
=smfGetOptionQuotes
M
Sat Jul 23, 2016 10:34 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Ummm....the smfGetOptionQuotes() function you cite below is returning
delayed quotes from Yahoo. Not real-time quotes.
I don't know of any place to get historical option quotes free. If you have
a source of the quotes, the add-in MIGHT be able to access it. It would
depend on how they present the data on the web page and what type of
security they have for accessing the data. Keep in mind that "last traded
price" is pretty worthless on options. You'd want those historical option
quotes to be bid and ask.
You could get the stock quote at a past date and time from barchart.com --
take a look at the quotes extract template here:
https://groups.yahoo.com/neo/groups/smf_addin/files/Templates%20and%20Examples/RCHGetWebData/
On Sat, Jul 23, 2016 at 10:05 PM, joshi_mandar@
...wrote:
> I am trying to build a model where I am looking for stock quote and option
> quote from a past date and time. For example: I am looking for price of
> AAPL at 1 PM ET on July 5th 2016. Also looking for quote for AAPL Jan 18
> $90 call at same time. Is there any way to get that using the add-in?
>
> I use RCHGetYahooQuotes within an array to get stock quotes usually.
>
> And I usually use below formula for real-time options quotes.
>
> =smfGetOptionQuotes("aapl Jan 18 90 call","l",0,"Y")
>
delayed quotes from Yahoo. Not real-time quotes.
I don't know of any place to get historical option quotes free. If you have
a source of the quotes, the add-in MIGHT be able to access it. It would
depend on how they present the data on the web page and what type of
security they have for accessing the data. Keep in mind that "last traded
price" is pretty worthless on options. You'd want those historical option
quotes to be bid and ask.
You could get the stock quote at a past date and time from barchart.com --
take a look at the quotes extract template here:
https://groups.yahoo.com/neo/groups/smf_addin/files/Templates%20and%20Examples/RCHGetWebData/
On Sat, Jul 23, 2016 at 10:05 PM, joshi_mandar@
...wrote:
> I am trying to build a model where I am looking for stock quote and option
> quote from a past date and time. For example: I am looking for price of
> AAPL at 1 PM ET on July 5th 2016. Also looking for quote for AAPL Jan 18
> $90 call at same time. Is there any way to get that using the add-in?
>
> I use RCHGetYahooQuotes within an array to get stock quotes usually.
>
> And I usually use below formula for real-time options quotes.
>
> =smfGetOptionQuotes
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar