Kamis, 18 Juni 2015

[smf_addin] Digest Number 3435

5 Messages

Digest #3435

Messages

Wed Jun 17, 2015 8:22 am (PDT) . Posted by:

bkipersztok

Hello -


I use =smfGetOptionQuotes("ticker month year strike call/put","b",0,"OX2") to get the vast majority of the option prices that I am looking for. I run the above formula and then the same formula but switch "b" with "a" to get the bid/ask spreads, and then I use the midpoint of the b/a spread as the market price.


I have found that I am unable to get the following formula to work for only one company - Berkshire Hathaway. I am currently using "BRK-B" as the ticker in the text string, but I have tried a variety of different tickers to see if that might work, and I've had no luck. So first question - does anyone know how to pull BRK-B option prices without an "Error" message?


Second question: I have used the same formula to get Weekly option pricing, but I seem to only be able to get the front two months of Weeklies. Any time I try to use "ticker W3 year strike call/put" or "ticker W4 year strike call/put" or "ticker W5 year strike call/put" in the text string for =smfGetOptionQuotes(), it doesn't seem to pull the correct Weekly I am looking for. What is the proper way to pull accurate pricing on Weeklies?


Thanks in advance for your response,


Bill

Wed Jun 17, 2015 9:15 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Sorry, but as is, OptionXPress can't be used for that ticker symbol. That's
because the web page of the option chains is under ticker symbol "BRK/B",
but the option links themselves use "BRKB". The processing I use expects
them to be the same.

A workaround would be to code the RCHGetTableCell() function yourself. For
example:

=RCHGetTableCell("
https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Range=All&lstMarket=0&ChainType=14&lstMonths=06/19/2015;7&Symbol=BRK/B",
-15, "BRKB^^150619C00140000")

...would get the bid price of the $140 call for expiration date 6/19/2015.

I'll look into automating the dropping of the "/" from the ticker symbol
when looking up the option. It shouldn't be that hard to do. But I have to
remember to do it for all three OptionXpress routines. At the moment, I'm
kind of ticked at them because they closed my free account. I guess I can't
blame them -- I've had it a number of years and only used the Virtual
Trading feature.

------------------------------

As for the second question...

Currently, "W3" isn't working because of the July 4th holiday. I don't have
any processing in place to account for holidays.

But, otherwise, I was able to get the correct bid prices for MMM for W1
thru W7 using the OX2 data source. However, Yahoo is missing data for the
7/31/2015 expiration date (i.e. W7).

Here's an example of what I'm using for testing:

=smfGetOptionQuotes("MMM W4 2015 $160 Call","b",,"OX2")
=smfGetOptionQuotes("MMM W7 2015 $160 Call","b",,"OX2")

The W3/W4/W5 were added on 2015-08-18 and W6/W7 were added on 2015-02-21,
so you would need to be using a beta version of the add-in released after
those dates in order to be able to use the respective weekly designations.

------------------------------

On Wed, Jun 17, 2015 at 8:22 AM, bkipersztok@... wrote:

> I use =smfGetOptionQuotes("ticker month year strike
> call/put","b",0,"OX2") to get the vast majority of the option prices that I
> am looking for. I run the above formula and then the same formula but
> switch "b" with "a" to get the bid/ask spreads, and then I use the midpoint
> of the b/a spread as the market price.
>
> I have found that I am unable to get the following formula to work for
> only one company - Berkshire Hathaway. I am currently using "BRK-B" as the
> ticker in the text string, but I have tried a variety of different tickers
> to see if that might work, and I've had no luck. So first question - does
> anyone know how to pull BRK-B option prices without an "Error" message?
>
> Second question: I have used the same formula to get Weekly option
> pricing, but I seem to only be able to get the front two months of
> Weeklies. Any time I try to use "ticker W3 year strike call/put" or "ticker
> W4 year strike call/put" or "ticker W5 year strike call/put" in the text
> string for =smfGetOptionQuotes(), it doesn't seem to pull the correct
> Weekly I am looking for. What is the proper way to pull accurate pricing on
> Weeklies?
>
> Thanks in advance for your response,
>
>

Wed Jun 17, 2015 10:39 am (PDT) . Posted by:

bkipersztok

Randy,

Thanks for your response. I'm having a bit of trouble figuring out how to code the RCHGetTableCell()function, but I've solved the weekly pricing problem by downloading the newest beta version of the add-in.


Can you explain the process for coding the RCHGetTableCell() function? I read the supporting documentation and tried fiddling around with the code, but I wasn't able to get it to work the way I wanted. Also, is the URL important? I don't know how you arrived at the URL you arrived at in your example. When I search for the BRK/B pricing on OptionsXpress, the URL I get is generic and devoid of descriptive information (https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?SESSIONID=).


Thanks again,


Bill



Wed Jun 17, 2015 11:57 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

The URL is important, because it gets you to the web page you need without
needing to log in to OptionsXPress. Otherwise, you'd need to manually
handle the "sessionid" variable. It took a bit to figure out how to create
that URL. :)

The URL can be created with something like:

="
https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?Range=All&lstMarket=0&ChainType=14&lstMonths="
& TEXT(D7, "mm/dd/yyyy") & ";7&Symbol=" & D8

...where the expiration date is in cell D7 and the ticker symbol (e.g.
"BRK/B") is in cell D8.

The search string (i.e. OptionXPress's option ticker symbol) can be created
with something like:

=LEFT(SUBSTITUTE(D8,"/","")&"^^^^^^",6)&TEXT(D7,"yymmdd")&D12&TEXT(D9*1000,"00000000")

...where D8 contains your ticker symbol (e.g. "BRK/B"), D7 contains the
expiration date, D12 contains either a "C" or a "P" depending on whether
you want a call or a put, and D9 contains the strike price.

The "pCells" parameter is the number of table cells to go in from the end
of the row that the search string is found in. I think it ranges from -1 to
-18, depending on which data item on the line you are after, going right to
left, put info first, then call.

On Wed, Jun 17, 2015 at 10:39 AM, bkipersztok@.... wrote:

>
> Thanks for your response. I'm having a bit of trouble figuring out how to
> code the RCHGetTableCell()function, but I've solved the weekly pricing
> problem by downloading the newest beta version of the add-in.
>
> Can you explain the process for coding the RCHGetTableCell() function? I
> read the supporting documentation and tried fiddling around with the code,
> but I wasn't able to get it to work the way I wanted. Also, is the URL
> important? I don't know how you arrived at the URL you arrived at in your
> example. When I search for the BRK/B pricing on OptionsXpress, the URL I
> get is generic and devoid of descriptive information (
> https://www.optionsxpress.com/OXNetTools/Chains/index.aspx?SESSIONID=).
>
>

Wed Jun 17, 2015 12:23 pm (PDT) . Posted by:

bkipersztok

Awesome, Randy. After studying your explanation, and I've got it working perfectly now. Many thanks for your work on this add-in and your patience and willingness to answer questions in support. This add-in is invaluable to me and I assume to many others.

Bill
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF

Tidak ada komentar:

Poskan Komentar