Sabtu, 02 Februari 2019

[smf_addin] Digest Number 4478

15 Messages

Digest #4478
1a
Re: Retrieve business breakdown table by "Randy Harmelink" rharmelink
1c
Re: Retrieve business breakdown table by "Randy Harmelink" rharmelink
1e
Re: Retrieve business breakdown table by "Randy Harmelink" rharmelink
2a
Re: smfgetoptionstrikes returning blanks by "Peter Cook" pete_cook2001
2b
Re: smfgetoptionstrikes returning blanks by "Peter Cook" pete_cook2001
2c
Re: smfgetoptionstrikes returning blanks by "Peter Cook" pete_cook2001
2d
Re: smfgetoptionstrikes returning blanks by "Randy Harmelink" rharmelink
2e
Re: smfgetoptionstrikes returning blanks by "Peter Cook" pete_cook2001
2f
Re: smfgetoptionstrikes returning blanks by "Peter Cook" pete_cook2001
2g
Re: smfgetoptionstrikes returning blanks by "Randy Harmelink" rharmelink
2h
Re: smfgetoptionstrikes returning blanks by "Peter Cook" pete_cook2001
2i
Re: smfgetoptionstrikes returning blanks by "Randy Harmelink" rharmelink
2j
Re: smfgetoptionstrikes returning blanks by "Peter Cook" pete_cook2001

Messages

Sat Feb 2, 2019 8:46 am (PST) . Posted by:

"Randy Harmelink" rharmelink

A little tricky, because it has a table within a table, but otherwise easy
peasy:

=RCHGetHTMLTable("https://www.marketscreener.com/APPLE-4849/company/","Sales
per Businesses",-2,,2)

=RCHGetHTMLTable("https://www.marketscreener.com/APPLE-4849/company/","Sales
per Region",-2,,2)

On Sat, Feb 2, 2019 at 2:06 AM lawrence.leesh@... wrote:

> Is there a way to retrieve data in the Sales per businesses and Sales per
> regions tables?
>
> https://www.marketscreener.com/APPLE-4849/company/
>
> The business of the companies change so I am wondering if the formula will
> work even with product name change (e.g. no longer iPhone and United
> States).
>
>
>

Sat Feb 2, 2019 1:38 pm (PST) . Posted by:

lawrence.leesh

Hi Randy, what I meant was whether it is possible to retrieve the info within the tables.

Sales per Businesses 2017 2018 Delta USD (in Million) % USD (in Million) % iPhone 141,319 61.8% 166,699 62.7% +17.96% Services 29,980 13.1% 37,190 14% +24.05% Mac 25,850 11.3% 25,484 9.6% -1.42% iPad 19,222 8.4% 18,805 7.1% -2.17% Other Products 12,863 5.6% 17,417 6.6% +35.4% Sales per Regions 2017 2018 Delta USD (in Million) % USD (in Million) % United States 84,339 36.9% 98,061 36.9% +16.27% Europe 54,938 24% 62,420 23.5% +13.62% Greater China 44,764 19.6% 51,942 19.5% +16.04% Japan 17,733 7.8% 21,733 8.2% +22.56% Rest of Asia Pacific 15,199 6.6% 17,407 6.5% +14.53% Americas 12,261 5.4% 14,032 5.3% +14.44%

Sat Feb 2, 2019 3:43 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

How so? Just blindly pull any data, whatever it happens to be?

On Sat, Feb 2, 2019 at 2:38 PM lawrence.leesh@... wrote:

>
> Hi Randy, what I meant was whether it is possible to retrieve the info
> within the tables.
>
> *Sales per Businesses*
> *2017* *2018* *Delta*
> USD *(in Million)* % USD *(in Million)* %
> iPhone 141,319 *61.8%* 166,699 *62.7%* +17.96%
> Services 29,980 *13.1%* 37,190 *14%* +24.05%
> Mac 25,850 *11.3%* 25,484 *9.6%* -1.42%
> iPad 19,222 *8.4%* 18,805 *7.1%* -2.17%
> Other Products 12,863 *5.6%* 17,417 *6.6%* +35.4%
> *Sales per Regions*
> *2017* *2018* *Delta*
> USD *(in Million)* % USD *(in Million)* %
> United States 84,339 *36.9%* 98,061 *36.9%* +16.27%
> Europe 54,938 *24%* 62,420 *23.5%* +13.62%
> Greater China 44,764 *19.6%* 51,942 *19.5%* +16.04%
> Japan 17,733 *7.8%* 21,733 *8.2%* +22.56%
> Rest of Asia Pacific 15,199 *6.6%* 17,407 *6.5%* +14.53%
> Americas 12,261 *5.4%* 14,032 *5.3%* +14.44%
>
>
>

Sat Feb 2, 2019 4:51 pm (PST) . Posted by:

lawrence.leesh

Yes, for APPLE it is iPhone and United States but will be different for other companies. I just want to pull whatever data is in the two tables.

Sat Feb 2, 2019 5:37 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

I don't understand how the two RCHGetHTMLTable() function don't do that?

Do you mean you want to retrieve the data one cell of the table at a time?
Either way, you have to have a big enough range for the whole table, so I
don't see the point?

On Sat, Feb 2, 2019 at 5:51 PM lawrence.leesh@... wrote:

>
> Yes, for APPLE it is iPhone and United States but will be different for
> other companies. I just want to pull whatever data is in the two tables.
>
>
>

Sat Feb 2, 2019 1:17 pm (PST) . Posted by:

"Peter Cook" pete_cook2001

I'm not grasping everything you're telling me, but a couple of things.

- I'm using windows 10, which has both the Edge and IE browsers
installed, but my default is set to Chrome. I've tried changing the default
but the problem still occurs.
- In the Internet Options control panel, when I try to set website data
settings "check for newer settings" to "Every time I visit the webpage" it
keeps changing back to "Automatically." I can't figure out why but maybe
that's the issue.

Pete

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

>
>
> 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?
>>>>
>>>
>
>

Sat Feb 2, 2019 1:25 pm (PST) . Posted by:

"Peter Cook" pete_cook2001

Also, I'm not sure this was clear - it's the strike prices I'm having
trouble with, not the expiration dates.

On Sat, Feb 2, 2019 at 4:17 PM Peter Cook <peterscottcook@gmail.com> wrote:

> I'm not grasping everything you're telling me, but a couple of things.
>
> - I'm using windows 10, which has both the Edge and IE browsers
> installed, but my default is set to Chrome. I've tried changing the default
> but the problem still occurs.
> - In the Internet Options control panel, when I try to set website
> data settings "check for newer settings" to "Every time I visit the
> webpage" it keeps changing back to "Automatically." I can't figure out why
> but maybe that's the issue.
>
> Pete
>
> On Thu, Jan 31, 2019 at 6:39 PM Randy Harmelink rharmelink@gmail.com
> [smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>>
>>
>> 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?
>>>>>
>>>>
>>
>>
>
>

Sat Feb 2, 2019 1:58 pm (PST) . Posted by:

"Peter Cook" pete_cook2001

Ok, ignore my last two messages. I discovered that the problem depends on
the ticker and the expiration date.

For example, if I use ticker F and exp date 3/15/19, or GM and exp 3/15/19,
I get the correct array of strike prices.

If I change the date, or if I use other tickers such as AAPL or WBA, I get
an array of blanks.

Any ideas?

On Sat, Feb 2, 2019 at 4:24 PM Peter Cook <peterscottcook@gmail.com> wrote:

> Also, I'm not sure this was clear - it's the strike prices I'm having
> trouble with, not the expiration dates.
>
> On Sat, Feb 2, 2019 at 4:17 PM Peter Cook <peterscottcook@gmail.com>
> wrote:
>
>> I'm not grasping everything you're telling me, but a couple of things.
>>
>> - I'm using windows 10, which has both the Edge and IE browsers
>> installed, but my default is set to Chrome. I've tried changing the default
>> but the problem still occurs.
>> - In the Internet Options control panel, when I try to set website
>> data settings "check for newer settings" to "Every time I visit the
>> webpage" it keeps changing back to "Automatically." I can't figure out why
>> but maybe that's the issue.
>>
>> Pete
>>
>> On Thu, Jan 31, 2019 at 6:39 PM Randy Harmelink rharmelink@gmail.com
>> [smf_addin] <smf_addin@yahoogroups.com> wrote:
>>
>>>
>>>
>>> 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?
>>>>>>
>>>>>
>>>
>>>
>>
>>

Sat Feb 2, 2019 3:45 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

It doesn't matter what you use as a browser. What EXCEL uses is a version
of IE, so it's options are used.

Do you still get the same limited list of expiration dates, if you use
smfForceRecalculation, or close and restart EXCEL?

On Sat, Feb 2, 2019 at 2:18 PM Peter Cook peterscottcook@...wrote:

>
> I'm not grasping everything you're telling me, but a couple of things.
>
> - I'm using windows 10, which has both the Edge and IE browsers
> installed, but my default is set to Chrome. I've tried changing the default
> but the problem still occurs.
> - In the Internet Options control panel, when I try to set website
> data settings "check for newer settings" to "Every time I visit the
> webpage" it keeps changing back to "Automatically." I can't figure out why
> but maybe that's the issue.
>
> Pete
>
> On Thu, Jan 31, 2019 at 6:39 PM Randy Harmelink rharmelink@gmail.com
> [smf_addin] <smf_addin@yahoogroups..com <smf_addin@yahoogroups.com>>
> wrote:
>
>>
>>
>> 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?
>>>>>
>>>>
>

Sat Feb 2, 2019 3:47 pm (PST) . Posted by:

"Peter Cook" pete_cook2001

Yes. I get blanks for most, but not all, tickers and expiration dates.

On 2/2/19, Randy Harmelink rharmelink@gmail.com [smf_addin]
<smf_addin@yahoogroups.com> wrote:
> It doesn't matter what you use as a browser. What EXCEL uses is a version
> of IE, so it's options are used.
>
> Do you still get the same limited list of expiration dates, if you use
> smfForceRecalculation, or close and restart EXCEL?
>
> On Sat, Feb 2, 2019 at 2:18 PM Peter Cook peterscottcook@...wrote:
>
>>
>> I'm not grasping everything you're telling me, but a couple of things.
>>
>> - I'm using windows 10, which has both the Edge and IE browsers
>> installed, but my default is set to Chrome. I've tried changing the
>> default
>> but the problem still occurs.
>> - In the Internet Options control panel, when I try to set website
>> data settings "check for newer settings" to "Every time I visit the
>> webpage" it keeps changing back to "Automatically." I can't figure out
>> why
>> but maybe that's the issue.
>>
>> Pete
>>
>> On Thu, Jan 31, 2019 at 6:39 PM Randy Harmelink rharmelink@gmail.com
>> [smf_addin] <smf_addin@yahoogroups..com <smf_addin@yahoogroups.com>>
>> wrote:
>>
>>>
>>>
>>> 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?
>>>>>>
>>>>>
>>
>

Sat Feb 2, 2019 3:47 pm (PST) . Posted by:

"Peter Cook" pete_cook2001

Yes. I get blanks for most, but not all, tickers and expiration dates
after restarting excel and/or forcing a recalc.

On 2/2/19, Randy Harmelink rharmelink@gmail.com [smf_addin]
<smf_addin@yahoogroups.com> wrote:
> It doesn't matter what you use as a browser. What EXCEL uses is a version
> of IE, so it's options are used.
>
> Do you still get the same limited list of expiration dates, if you use
> smfForceRecalculation, or close and restart EXCEL?
>
> On Sat, Feb 2, 2019 at 2:18 PM Peter Cook peterscottcook@...wrote:
>
>>
>> I'm not grasping everything you're telling me, but a couple of things.
>>
>> - I'm using windows 10, which has both the Edge and IE browsers
>> installed, but my default is set to Chrome. I've tried changing the
>> default
>> but the problem still occurs.
>> - In the Internet Options control panel, when I try to set website
>> data settings "check for newer settings" to "Every time I visit the
>> webpage" it keeps changing back to "Automatically." I can't figure out
>> why
>> but maybe that's the issue.
>>
>> Pete
>>
>> On Thu, Jan 31, 2019 at 6:39 PM Randy Harmelink rharmelink@gmail.com
>> [smf_addin] <smf_addin@yahoogroups..com <smf_addin@yahoogroups.com>>
>> wrote:
>>
>>>
>>>
>>> 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?
>>>>>>
>>>>>
>>
>

Sat Feb 2, 2019 5:43 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

What happens if you use this URL directly in the IE object browser within
EXCEL (alt+d+d+w):

https://query1.finance.yahoo.com/v7/finance/options/AAPL?date=1550188800

How many expiration dates do you see?

What result do you get with this:

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

On Sat, Feb 2, 2019 at 2:58 PM Peter Cook peterscottcook@... wrote:

>
> Ok, ignore my last two messages. I discovered that the problem depends on
> the ticker and the expiration date.
>
> For example, if I use ticker F and exp date 3/15/19, or GM and exp
> 3/15/19, I get the correct array of strike prices.
>
> If I change the date, or if I use other tickers such as AAPL or WBA, I get
> an array of blanks.
>
> Any ideas?
>
>
>

Sat Feb 2, 2019 5:55 pm (PST) . Posted by:

"Peter Cook" pete_cook2001

https://query1.finance.yahoo.com/v7/finance/options/AAPL?date=1550188800

RESULT:
It's looking for a program to open to open AAPL.json.

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

RESULT:
2019-02-01 21:00:01
<https://query1.finance.yahoo..com/v7/finance/options/AAPL?date=1550188800>

On Sat, Feb 2, 2019 at 8:43 PM Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:

>
>
> What happens if you use this URL directly in the IE object browser within
> EXCEL (alt+d+d+w):
>
> https://query1.finance.yahoo.com/v7/finance/options/AAPL?date=1550188800
>
> How many expiration dates do you see?
>
> What result do you get with this:
>
> =TEXT(smfUnix2Date(smfstrExtr(RCHGetWebData("
> https://query1.finance.yahoo.com/v7/finance/options/AAPL?date=1550188800
> <https://query1.finance.yahoo..com/v7/finance/options/AAPL?date=1550188800>","regularMarketTime",50),":",",")),"yyyy-mm-dd
> hh:mm:ss")
>
>
> On Sat, Feb 2, 2019 at 2:58 PM Peter Cook peterscottcook@... wrote:
>
>>
>> Ok, ignore my last two messages. I discovered that the problem depends on
>> the ticker and the expiration date.
>>
>> For example, if I use ticker F and exp date 3/15/19, or GM and exp
>> 3/15/19, I get the correct array of strike prices.
>>
>> If I change the date, or if I use other tickers such as AAPL or WBA, I
>> get an array of blanks.
>>
>> Any ideas?
>>
>>
>>
>
>

Sat Feb 2, 2019 6:06 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Did the JSON file have more expiration dates?

The date and time you got is the same one I get.

On Sat, Feb 2, 2019 at 6:55 PM Peter Cook peterscottcook@... wrote:

>
> https://query1.finance.yahoo.com/v7/finance/options/AAPL?date=1550188800
>
> RESULT:
> It's looking for a program to open to open AAPL.json.
>
> =TEXT(smfUnix2Date(smfstrExtr(RCHGetWebData("
> https://query1.finance.yahoo.com/v7/finance/options/AAPL?date=1550188800
> <https://query1.finance.yahoo..com/v7/finance/options/AAPL?date=1550188800>
> ","regularMarketTime",50),":",",")),"yyyy-mm-dd hh:mm:ss")
>
> RESULT:
> 2019-02-01 21:00:01
> <https://query1.finance.yahoo..com/v7/finance/options/AAPL?date=1550188800>
>
>

Sat Feb 2, 2019 6:12 pm (PST) . Posted by:

"Peter Cook" pete_cook2001

I see. I opened it with notepad and I see what look like three expiration
dates:

{"optionChain":{"result":[{"underlyingSymbol":"AAPL","expirationDates":[1561075200,1563494400,1579219200],"strikes":[],"hasMiniOptions":false,"quote":{"language":"en-US","region":"US","quoteType":"EQUITY","quoteSourceName":"Delayed
Quote","currency":"USD","epsTrailingTwelveMonths":12.121,"epsForward":13.33,"esgPopulated":false,"tradeable":true,"marketState":"CLOSED","sharesOutstanding":4729800192,"bookValue":24.925,"fiftyDayAverage":155.8203,"fiftyDayAverageChange":10.699707,"fiftyDayAverageChangePercent":0.068666965,"twoHundredDayAverage":194.54417,"twoHundredDayAverageChange":-28.02417,"twoHundredDayAverageChangePercent":-0.14405042,"marketCap":787606339584,"forwardPE":12.492124,"priceToBook":6.680843,"sourceInterval":15,"exchangeTimezoneName":"America/New_York","exchangeTimezoneShortName":"EST","gmtOffSetMilliseconds":-18000000,"shortName":"Apple
Inc.","exchange":"NMS","averageDailyVolume10Day":35547925,"regularMarketPrice":166.52,"regularMarketTime":1549054801,"regularMarketChange":0.08000183,"regularMarketOpen":166.96,"regularMarketDayHigh":168.98,"regularMarketDayLow":165.93,"regularMarketVolume":32668138,"postMarketChangePercent":0.01801512,"postMarketTime":1549069187,"postMarketPrice":166.55,"postMarketChange":0.02999878,"regularMarketChangePercent":0.04806647,"regularMarketDayRange":"165.93
-
168.98","regularMarketPreviousClose":166.44,"bid":166.52,"ask":166.55,"bidSize":13,"askSize":10,"messageBoardId":"finmb_24937","fullExchangeName":"NasdaqGS","longName":"Apple
Inc.","financialCurrency":"USD","averageDailyVolume3Month":42946132,"market":"us_market","priceHint":2,"exchangeDataDelayedBy":0,"fiftyTwoWeekLowChange":24.520004,"fiftyTwoWeekLowChangePercent":0.17267609,"fiftyTwoWeekRange":"142.0
-
233.47","fiftyTwoWeekHighChange":-66.95,"fiftyTwoWeekHighChangePercent":-0.2867606,"fiftyTwoWeekLow":142.0,"fiftyTwoWeekHigh":233.47,"dividendDate":1542240000,"earningsTimestamp":1548795600,"earningsTimestampStart":1556535540,"earningsTimestampEnd":1556884800,"trailingAnnualDividendRate":2.82,"trailingPE":13.73814,"trailingAnnualDividendYield":0.016943041,"symbol":"AAPL"},"options":[{"expirationDate":1550188800,"hasMiniOptions":false,"calls":[],"puts":[]}]}],"error":null}}

On Sat, Feb 2, 2019 at 9:06 PM Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:

>
>
> Did the JSON file have more expiration dates?
>
> The date and time you got is the same one I get.
>
> On Sat, Feb 2, 2019 at 6:55 PM Peter Cook peterscottcook@... wrote:
>
>>
>> https://query1.finance.yahoo.com/v7/finance/options/AAPL?date=1550188800
>>
>>
>> RESULT:
>> It's looking for a program to open to open AAPL.json.
>>
>> =TEXT(smfUnix2Date(smfstrExtr(RCHGetWebData("
>> https://query1.finance.yahoo.com/v7/finance/options/AAPL?date=1550188800
>> <https://query1.finance.yahoo..com/v7/finance/options/AAPL?date=1550188800>
>> ","regularMarketTime",50),":",",")),"yyyy-mm-dd hh:mm:ss")
>>
>> RESULT:
>> 2019-02-01 21:00:01
>> <https://query1.finance.yahoo..com/v7/finance/options/AAPL?date=1550188800>
>>
>>
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 18.12

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)