[smf_addin] Digest Number 4476

EXCEL Stock Market Functions Add-in Group

4 Messages

Digest #4476

1a

smfgetoptionstrikes returning blanks by pete_cook2001

1b

Re: smfgetoptionstrikes returning blanks by "Randy Harmelink" rharmelink

1c

Re: smfgetoptionstrikes returning blanks by "Peter Cook" pete_cook2001

1d

Re: smfgetoptionstrikes returning blanks by "Randy Harmelink" rharmelink

Messages

1a

smfgetoptionstrikes returning blanks

Thu Jan 31, 2019 10:22 am (PST) . Posted by:

pete_cook2001

Somehow, recently smfgetoptionstrikes has been returning all blanks. Right now I'm testing a 27-cell vertical array using the array formula


=smfGetOptionStrikes("AAPL",nextmonthly,"C","B")


where nextmonthly is currently set to 2/15/19.


I've used it successfully in the past. Don't remember where I got the source "B", but I've tried Y, G, and 8 and they all return blanks.


Any ideas?

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

1b

Re: smfgetoptionstrikes returning blanks

Thu Jan 31, 2019 1:03 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Not a clue. It's working fine here. Possibly just try the
smfForceRecalculation macro? You may have had a temporary Yahoo glitch.

Otherwise, what do you get with:

=RCHGetElementNumber("Version")
=RCHGetWebData("
https://query1.finance.yahoo.com/v7/finance/options/AAPL?date=1550188800
",,200)

At the moment, Yahoo ("Y") is the only working source. However, Barchart
("B") and Google ("G") just go get it from Yahoo anyway, because I
redirected them to Yahoo when those services dropped their available
options data. I haven't use 888options "8" for a while, as it requires a
security cookie to be created first. An, IIRC, the IE object in EXCEL
wouldn't allow me to login properly last time I tried it.

On Thu, Jan 31, 2019 at 11:25 AM peterscottcook@... wrote:

> Somehow, recently smfgetoptionstrikes has been returning all blanks. Right
> now I'm testing a 27-cell vertical array using the array formula
>
> =smfGetOptionStrikes("AAPL",nextmonthly,"C","B")
>
> where nextmonthly is currently set to 2/15/19.
>
> I've used it successfully in the past. Don't remember where I got the
> source "B", but I've tried Y, G, and 8 and they all return blanks.
>
> Any ideas?
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

1c

Re: smfgetoptionstrikes returning blanks

Thu Jan 31, 2019 1:24 pm (PST) . Posted by:

"Peter Cook" pete_cook2001

I did the recalc.

=RCHGetElementNumber("Version")
Stock Market Functions add-in, Version 2.1.2018.01.24 (C:\Program Files\SMF
Add-In; Windows (64-bit) NT 6.02; 14.0; ; ; 1)

=RCHGetWebData("
https://query1.finance.yahoo.com/v7/finance/options/AAPL?date=1550188800
",,200)
{"optionChain":{"result":[{"underlyingSymbol":"AAPL","expirationDates":[1561075200,1563494400,1579219200],"strikes":[],"hasMiniOptions":false,"quote":{"language":"en-US","region":"US","quoteType":"EQU

On Thu, Jan 31, 2019 at 4:05 PM Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:

>
>
> Not a clue. It's working fine here. Possibly just try the
> smfForceRecalculation macro? You may have had a temporary Yahoo glitch.
>
> Otherwise, what do you get with:
>
> =RCHGetElementNumber("Version")
> =RCHGetWebData("
> https://query1.finance.yahoo.com/v7/finance/options/AAPL?date=1550188800
> ",,200)
>
> At the moment, Yahoo ("Y") is the only working source. However, Barchart
> ("B") and Google ("G") just go get it from Yahoo anyway, because I
> redirected them to Yahoo when those services dropped their available
> options data. I haven't use 888options "8" for a while, as it requires a
> security cookie to be created first. An, IIRC, the IE object in EXCEL
> wouldn't allow me to login properly last time I tried it.
>
> On Thu, Jan 31, 2019 at 11:25 AM peterscottcook@... wrote:
>
>> Somehow, recently smfgetoptionstrikes has been returning all blanks.
>> Right now I'm testing a 27-cell vertical array using the array formula
>>
>> =smfGetOptionStrikes("AAPL&quot;,nextmonthly,"C","B")
>>
>> where nextmonthly is currently set to 2/15/19.
>>
>> I've used it successfully in the past. Don't remember where I got the
>> source "B", but I've tried Y, G, and 8 and they all return blanks.
>>
>> Any ideas?
>>
>>
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

1d

Re: smfgetoptionstrikes returning blanks

Thu Jan 31, 2019 3:38 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

My first 200 bytes from that URL returns a lot more expiration dates:

{"optionChain":{"result":[{"underlyingSymbol":"AAPL","expirationDates":[1548979200,1549584000,1550188800,1550793600,1551398400,1552608000,1555545600,1561075200,1563494400,1579219200,1592524800,1610668

Do you have your Internet Explorer options set up to always get a new copy
of the web page? Otherwise, the Microsoft DLL library could be picking up
an old cached page.

You can display the raw data of the time stamps returned on that query with:

=RCHGetWebData("
https://query1.finance.yahoo.com/v7/finance/options/AAPL?date=1550188800
","postMarketTime",50)
=RCHGetWebData("
https://query1.finance.yahoo.com/v7/finance/options/AAPL?date=1550188800
","regularMarketTime",50)

I currently get:

postMarketTime":1548977237,"epsTrailingTwelveMonth
regularMarketTime":1548968401,"regularMarketChange

You can translate those dates with:

=TEXT(smfUnix2Date(smfstrExtr(RCHGetWebData("
https://query1.finance.yahoo.com/v7/finance/options/AAPL?date=1550188800","postMarketTime",50),":",",")),"yyyy-mm-dd
hh:mm:ss")
=TEXT(smfUnix2Date(smfstrExtr(RCHGetWebData("
https://query1.finance.yahoo.com/v7/finance/options/AAPL?date=1550188800","regularMarketTime",50),":",",")),"yyyy-mm-dd
hh:mm:ss")

I currently get:

2019-01-31 23:27:17
2019-01-31 21:00:01

The time is GMT time.

On Thu, Jan 31, 2019 at 2:24 PM Peter Cook peterscottcook@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:

>
> I did the recalc.
>
> =RCHGetElementNumber("Version")
> Stock Market Functions add-in, Version 2.1.2018.01.24 (C:\Program
> Files\SMF Add-In; Windows (64-bit) NT 6.02; 14.0; ; ; 1)
>
> =RCHGetWebData("
> https://query1.finance.yahoo.com/v7/finance/options/AAPL?date=1550188800
> ",,200)
>
> {"optionChain":{"result":[{"underlyingSymbol":"AAPL","expirationDates":[1561075200,1563494400,1579219200],"strikes":[],"hasMiniOptions":false,"quote":{"language":"en-US","region":"US","quoteType":"EQU
>
>
> On Thu, Jan 31, 2019 at 4:05 PM Randy Harmelink rharmelink@gmail.com
> [smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>>
>> Not a clue. It's working fine here. Possibly just try the
>> smfForceRecalculation macro? You may have had a temporary Yahoo glitch.
>>
>> Otherwise, what do you get with:
>>
>> =RCHGetElementNumber("Version")
>> =RCHGetWebData("
>> https://query1.finance.yahoo.com/v7/finance/options/AAPL?date=1550188800
>> ",,200)
>>
>> At the moment, Yahoo ("Y") is the only working source. However, Barchart
>> ("B") and Google ("G") just go get it from Yahoo anyway, because I
>> redirected them to Yahoo when those services dropped their available
>> options data. I haven't use 888options "8" for a while, as it requires a
>> security cookie to be created first. An, IIRC, the IE object in EXCEL
>> wouldn't allow me to login properly last time I tried it.
>>
>> On Thu, Jan 31, 2019 at 11:25 AM peterscottcook@... wrote:
>>
>>> Somehow, recently smfgetoptionstrikes has been returning all blanks.
>>> Right now I'm testing a 27-cell vertical array using the array formula
>>>
>>> =smfGetOptionStrikes("AAPL&quot;,nextmonthly,"C","B")
>>>
>>> where nextmonthly is currently set to 2/15/19.
>>>
>>> I've used it successfully in the past. Don't remember where I got the
>>> source "B", but I've tried Y, G, and 8 and they all return blanks.
>>>
>>> Any ideas?
>>>
>>

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

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

Visit Your Group

• Privacy • Unsubscribe • Terms of Use