15 Messages
Digest #4332
Messages
Tue May 15, 2018 7:52 am (PDT) . Posted by:
jeffesch
Thanks Randy for the quick reply. After a series of trial and errors, I found that the offending function is smfGetYahooJSONField. After removing those references, the issue goes away.
I did check the "Access data sources across domains" setting in the Internet Options and it is already disabled. Do you have any other suggestions?
I did check the "Access data sources across domains" setting in the Internet Options and it is already disabled. Do you have any other suggestions?
Tue May 15, 2018 7:56 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
What is the exact formula you're invoking?
On Tue, May 15, 2018 at 7:50 AM, jeffesch@
...
wrote:
>
> Thanks Randy for the quick reply. After a series of trial and errors, I
> found that the offending function is smfGetYahooJSONField. After removing
> those references, the issue goes away.
>
> I did check the "Access data sources across domains" setting in the
> Internet Options and it is already disabled. Do you have any other
> suggestions?
>
>
>
On Tue, May 15, 2018 at 7:50 AM, jeffesch@
...
wrote:
>
> Thanks Randy for the quick reply. After a series of trial and errors, I
> found that the offending function is smfGetYahooJSONFiel
> those references, the issue goes away.
>
> I did check the "Access data sources across domains" setting in the
> Internet Options and it is already disabled. Do you have any other
> suggestions?
>
>
>
Tue May 15, 2018 8:38 am (PDT) . Posted by:
jeffesch
Here are a few examples: =smfGetYahooJSONField(B3,"topHoldings";,"quoteSummary.result.0.topHoldings.holdings.0.symbol")
=smfGetYahooJSONField(B3,"topHoldings";,"quoteSummary.result.0.topHoldings.holdings.0.holdingPercent.raw")
=smfGetYahooJSONField(B3,"defaultKeyStatistics","quoteSummary.result.0.defaultKeyStatistics.morningStarOverallRating.raw")
=smfGetYahooJSONFie
=smfGetYahooJSONFie
Tue May 15, 2018 10:24 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Hmm. Not getting any type of message here. Do you get a message if you do:
=RCHGetWebData("
https://query1.finance.yahoo.com/v10/finance/quoteSummary/SPY?modules=topHoldings
","holdings")
On Tue, May 15, 2018 at 8:33 AM, jeffesch@
...
wrote:
>
> Here are a few examples:
>
> =smfGetYahooJSONField(B3,"topHoldings";,"quoteSummary.
> result.0.topHoldings.holdings.0.symbol")
> =smfGetYahooJSONField(B3,"topHoldings";,"quoteSummary.
> result.0.topHoldings.holdings.0.holdingPercent.raw")
> =smfGetYahooJSONField(B3,"defaultKeyStatistics","quoteSummary.result.0.
> defaultKeyStatistics.morningStarOverallRating.raw")
>
>
=RCHGetWebData("
https://query1.finance.yahoo.com/v10/finance/quoteSummary/SPY?modules=topHoldings
","
On Tue, May 15, 2018 at 8:33 AM, jeffesch@
...
wrote:
>
> Here are a few examples:
>
> =smfGetYahooJSONFie
> result.0.topHolding
> =smfGetYahooJSONFie
> result.0.topHolding
> =smfGetYahooJSONFie
> defaultKeyStatistic
>
>
Tue May 15, 2018 11:27 am (PDT) . Posted by:
jeffesch
No I don't get any security warning.
I'm not sure if this additional information would be helpful - but during my experimentation this morning, I found that when I loaded Excel program (the first time) and then loaded the file with the offending JSON function, I would get a dozen or so dialog boxes to accept while the various worksheets refreshed. If I closed the file (but keep the current Excel instance open) and then reload the file with the offending JSON functions, I do not get any of the security warnings.
I'm not sure if this additional information would be helpful - but during my experimentation this morning, I found that when I loaded Excel program (the first time) and then loaded the file with the offending JSON function, I would get a dozen or so dialog boxes to accept while the various worksheets refreshed. If I closed the file (but keep the current Excel instance open) and then reload the file with the offending JSON functions, I do not get any of the security warnings.
Tue May 15, 2018 1:10 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
(Read my last item first -- it may fix the problem?)
That's because if you don't close EXCEL, the VBA environment is preserved
and the original web page retrievals are still saved in a VBA array. The
add-in retrieves a web page once, saves it in an array, and then does
extractions from the saved copy of the web page. That's so you can get
multiple items from the web page without having to retrieve it over and
over.
The smfForceRecalculation macro is used to have the add-in purge all of
those saved copies and re-retrieve the web pages. It's necessary if you
want to recalculate add-in functions with a fresh copy of a web page.
By the way, you would have needed to do the RCHGetWebData() function
WITHOUT doing the other functions. Otherwise, the RCHGetWebData() functions
I gave you would just use the already retrieved copy of the web page that
is stored in VBA. To test the RCHGetWebData() function, you really need to
close EXCEL, start it, and use the RCHGetWebData() function in a new
workbook, without having used any add-in function on that web page.
Or, easier would be to change the ticker symbol of the URL in the function
to be an ETF you haven't used yet?
The RCHGetWebData() function SHOULD give the same error, as that is what
the smfGetYahooJSONField() function used to get the data in the first
place.
What do you get with:
=RCHGetElementNumber("Version")
What operating system, IE version, and EXCEL version do you use?
Later...
Hmm. This web page
<https://kite203.wordpress.com/2010/04/30/issues-solving-tip%E2%80%9Dthis-page-is-accessing-information-that-is-not-under-its-control-this-poses-a-security-risk-do-you-want-to-continue%E2%80%9D/>
says
to *ENABLE* the "Access data sources across domains" setting under the
security section of Internet Options to get rid of the problem? Since yours
in not enabled, maybe that's the issue? It could be Yahoo is going to a
different domain to satisfy the JSON request (maybe their data provider)?
You could try the URL in your IE browser and see if you get the message
there?
Either I read the earlier web page incorrectly, or it stated it incorrectly..
On Tue, May 15, 2018 at 11:27 AM, jeffesch@
...
wrote:
>
> No I don't get any security warning.
>
> I'm not sure if this additional information would be helpful - but during
> my experimentation this morning, I found that when I loaded Excel program
> (the first time) and then loaded the file with the offending JSON function,
> I would get a dozen or so dialog boxes to accept while the various
> worksheets refreshed. If I closed the file (but keep the current Excel
> instance open) and then reload the file with the offending JSON functions,
> I do not get any of the security warnings.
>
That's because if you don't close EXCEL, the VBA environment is preserved
and the original web page retrievals are still saved in a VBA array. The
add-in retrieves a web page once, saves it in an array, and then does
extractions from the saved copy of the web page. That's so you can get
multiple items from the web page without having to retrieve it over and
over.
The smfForceRecalculation macro is used to have the add-in purge all of
those saved copies and re-retrieve the web pages. It's necessary if you
want to recalculate add-in functions with a fresh copy of a web page.
By the way, you would have needed to do the RCHGetWebData() function
WITHOUT doing the other functions. Otherwise, the RCHGetWebData() functions
I gave you would just use the already retrieved copy of the web page that
is stored in VBA. To test the RCHGetWebData() function, you really need to
close EXCEL, start it, and use the RCHGetWebData() function in a new
workbook, without having used any add-in function on that web page.
Or, easier would be to change the ticker symbol of the URL in the function
to be an ETF you haven't used yet?
The RCHGetWebData() function SHOULD give the same error, as that is what
the smfGetYahooJSONField() function used to get the data in the first
place.
What do you get with:
=RCHGetElementNumber("Version")
What operating system, IE version, and EXCEL version do you use?
Later...
Hmm. This web page
<https://kite203.wordpress.com/2010/04/30/issues-solving-tip%E2%80%9Dthis-page-is-accessing-information-that-is-not-under-its-control-this-poses-a-security-risk-do-you-want-to-continue%E2%80%9D/>
says
to *ENABLE* the "Access data sources across domains" setting under the
security section of Internet Options to get rid of the problem? Since yours
in not enabled, maybe that's the issue? It could be Yahoo is going to a
different domain to satisfy the JSON request (maybe their data provider)?
You could try the URL in your IE browser and see if you get the message
there?
Either I read the earlier web page incorrectly, or it stated it incorrectly..
On Tue, May 15, 2018 at 11:27 AM, jeffesch@
...
wrote:
>
> No I don't get any security warning.
>
> I'm not sure if this additional information would be helpful - but during
> my experimentation this morning, I found that when I loaded Excel program
> (the first time) and then loaded the file with the offending JSON function,
> I would get a dozen or so dialog boxes to accept while the various
> worksheets refreshed. If I closed the file (but keep the current Excel
> instance open) and then reload the file with the offending JSON functions,
> I do not get any of the security warnings.
>
Sat May 19, 2018 11:21 am (PDT) . Posted by:
jeffesch
Randy,
Sorry for my late reply. The change in the "Access data sources across domains" setting to ENABLE did the trick!
Thanks for all the help. I really love what you've put together in this excellent add-in.
Sorry for my late reply. The change in the "Access data sources across domains" setting to ENABLE did the trick!
Thanks for all the help. I really love what you've put together in this excellent add-in.
Tue May 15, 2018 9:20 am (PDT) . Posted by:
ssztaba
Randy,
I'm trying to understand all the characters which are involved in assembling an option Ticker symbol when using the =smfGetYahooPortfolioView function.
The example template shows SPY191220C00260000 as the Ticker Symbol for the SPY Dec 2019 260.000 call.
The code up until the "C" in the assembled symbol is obvious to me: the SPY call expiring on Dec 20 2019.
The last 6 characters in the assembled symbol are also obvious: the strike price out to 3 digits.
My question is what is the significance of the two zeros immediately after the "C" and before the "260" ??
The sample Ticker Symbol has 18 characters. Is 18 characters a requirement so that for a stock having a $55 strike option I should pad out the strike price in order to get to 18 characters ??
Thanks
Stan
I'm trying to understand all the characters which are involved in assembling an option Ticker symbol when using the =smfGetYahooPortfol
The example template shows SPY191220C00260000 as the Ticker Symbol for the SPY Dec 2019 260.000 call.
The code up until the "C" in the assembled symbol is obvious to me: the SPY call expiring on Dec 20 2019.
The last 6 characters in the assembled symbol are also obvious: the strike price out to 3 digits.
My question is what is the significance of the two zeros immediately after the "C" and before the "260" ??
The sample Ticker Symbol has 18 characters. Is 18 characters a requirement so that for a stock having a $55 strike option I should pad out the strike price in order to get to 18 characters ??
Thanks
Stan
Tue May 15, 2018 10:55 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
If you had the four items in cells with related names, an EXCEL formula to
create the ticker symbol:
=UPPER(sTicker & TEXT(pExpiry, "yymmdd") & LEFT(sPutCall,1) & TEXT(1000 *
pStrike, "00000000";))
Or you could just use:
=smfGetYahooOptionQuote(sTicker,sPutCall,pExpiry,pStrike,"z")
If you want to use the more descriptive option ticker symbols that
smfGetOptionStrikes() uses, you can also do:
=smfGetOptionQuotes("MMM 5/18 2018 $205.00 Call","z")
I have some spreadsheets where I just use the smfGetOptionStrikes()
function to generate a group of them for me. For example, array-entering
this would get me the first ITM/OTM Yahoo option ticker symbols for a given
ticker and expiration date:
=smfGetOptionQuotes(smfGetOptionStrikes(sTicker,pExpiry,"Call","Y",1),"z")
So, in my primary option analysis workbook, I first do an array-entered:
=smfGetOptionExpirations(sTicker,"Y",,,,,,"WM")
....to get the weekly and monthly expiration dates of the ticker. Then I do
up to 14 of the smfGetOptionStrikes() functions to get all of the first 12
ITM and OTM option ticker symbols. Then I do the smfGetYahooPortfolioView()
function on the underlying stock and those hundreds of option ticker
symbols. So I can get over 300 of the first ITM/OTM quotes with just over a
dozen internet accesses. Because the JSON file retrievals are very quick,
it only takes several seconds to get all the necessary data. Then I have
four worksheets that do different strategies and views on that returned
data. I used to do the four worksheets as independent workbooks, but
decided this was a better way -- to just get all the relevant data quickly
-- because each used an overlap of the others' needed data. And all I have
to do is enter the ticker symbol of the underlying equity.
On Tue, May 15, 2018 at 9:14 AM, ssztaba@
...
wrote:
> I'm trying to understand all the characters which are involved in
> assembling an option Ticker symbol when using the =smfGetYahooPortfolioView
> function.
>
> The example template shows SPY191220C00260000 as the Ticker Symbol for the
> SPY Dec 2019 260.000 call.
>
> The code up until the "C" in the assembled symbol is obvious to me: the
> SPY call expiring on Dec 20 2019.
>
> The last 6 characters in the assembled symbol are also obvious: the strike
> price out to 3 digits.
>
> My question is what is the significance of the two zeros immediately after
> the "C" and before the "260" ??
>
> The sample Ticker Symbol has 18 characters. Is 18 characters a
> requirement so that for a stock having a $55 strike option I should pad
> out the strike price in order to get to 18 characters ??
>
>
>
create the ticker symbol:
=UPPER(sTicker & TEXT(pExpiry, "yymmdd"
pStrike, "00000000"
Or you could just use:
=smfGetYahooOptionQ
If you want to use the more descriptive option ticker symbols that
smfGetOptionStrikes
=smfGetOptionQuotes
I have some spreadsheets where I just use the smfGetOptionStrikes
function to generate a group of them for me. For example, array-entering
this would get me the first ITM/OTM Yahoo option ticker symbols for a given
ticker and expiration date:
=smfGetOptionQuotes
So, in my primary option analysis workbook, I first do an array-entered:
=smfGetOptionExpira
....to get the weekly and monthly expiration dates of the ticker. Then I do
up to 14 of the smfGetOptionStrikes
ITM and OTM option ticker symbols. Then I do the smfGetYahooPortfoli
function on the underlying stock and those hundreds of option ticker
symbols. So I can get over 300 of the first ITM/OTM quotes with just over a
dozen internet accesses. Because the JSON file retrievals are very quick,
it only takes several seconds to get all the necessary data. Then I have
four worksheets that do different strategies and views on that returned
data. I used to do the four worksheets as independent workbooks, but
decided this was a better way -- to just get all the relevant data quickly
-- because each used an overlap of the others' needed data. And all I have
to do is enter the ticker symbol of the underlying equity.
On Tue, May 15, 2018 at 9:14 AM, ssztaba@
...
wrote:
> I'm trying to understand all the characters which are involved in
> assembling an option Ticker symbol when using the =smfGetYahooPortfol
> function.
>
> The example template shows SPY191220C00260000 as the Ticker Symbol for the
> SPY Dec 2019 260.000 call.
>
> The code up until the "C" in the assembled symbol is obvious to me: the
> SPY call expiring on Dec 20 2019.
>
> The last 6 characters in the assembled symbol are also obvious: the strike
> price out to 3 digits.
>
> My question is what is the significance of the two zeros immediately after
> the "C" and before the "260" ??
>
> The sample Ticker Symbol has 18 characters. Is 18 characters a
> requirement so that for a stock having a $55 strike option I should pad
> out the strike price in order to get to 18 characters ??
>
>
>
Tue May 15, 2018 1:33 pm (PDT) . Posted by:
ssztaba
Randy,
This is very interesting. It seems, and my tests verify, that whereas before I would use smfGETOptionQuotes to actually get the price quotes, that now I am using the smfGETOptionQuotes to correctly assemble the ticker symbol which gets fed into the smfGetYahooPortfolioView routine to actually get the pricing.
I already had a routine to assemble the string for the smfGETOptionQuotes routine, so now I'll just continue to assemble the string and then feed that into the smfGetYahooPortfolioView routine.
Very cleaverly done.
Thank you.
Stan
This is very interesting. It seems, and my tests verify, that whereas before I would use smfGETOptionQuotes to actually get the price quotes, that now I am using the smfGETOptionQuotes to correctly assemble the ticker symbol which gets fed into the smfGetYahooPortfoli
I already had a routine to assemble the string for the smfGETOptionQuotes routine, so now I'll just continue to assemble the string and then feed that into the smfGetYahooPortfoli
Very cleaverly done.
Thank you.
Stan
Wed May 16, 2018 5:47 pm (PDT) . Posted by:
pratcliffe1969
Hello Randy- I started using smf add in a few years ago (Thanks!) and am now getting back into it. I am trying to get historical quotes.. I used to use RCHGetYahooHistory() but its not working and I see that its obsolete now? Is there a replacement/fix for this query. Thanks
Wed May 16, 2018 6:20 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
RCHGetYahooHistory() wasn't obsoleted, but it now calls
smfGetYahooHistory(). But it can't do everything it used to do. What
invocation of RCHGetYahooHistory() are you having problems with -- what
parameters are you using?
Otherwise, just use smfGetYahooHistory() instead.
On Wed, May 16, 2018 at 5:46 PM, paulratcliffe@
...
wrote:
> Hello Randy- I started using smf add in a few years ago (Thanks!) and am
> now getting back into it. I am trying to get historical quotes. I used to
> use RCHGetYahooHistory() but its not working and I see that its obsolete
> now? Is there a replacement/fix for this query. Thanks
>
>
>
smfGetYahooHistory(
invocation of RCHGetYahooHistory(
parameters are you using?
Otherwise, just use smfGetYahooHistory(
On Wed, May 16, 2018 at 5:46 PM, paulratcliffe@
...
wrote:
> Hello Randy- I started using smf add in a few years ago (Thanks!) and am
> now getting back into it. I am trying to get historical quotes. I used to
> use RCHGetYahooHistory(
> now? Is there a replacement/
>
>
>
Tue May 22, 2018 4:15 pm (PDT) . Posted by:
garyhartling
My Nasdaq formula for Dividend Pay Date gets an error. Is there a new or better formula for this?
This doesn't work:
RCHGetTableCell("http://www.nasdaq.com/symbol/"&$E4&"/dividend-history",5,">Payment Date",2)
Also, https:// doesn't seem to help either.
Thx,
Gary H.
This doesn't work:
RCHGetTableCell("http://www.nasdaq.com/symbol/"&$E4&"
Also, https:// doesn't seem to help either.
Thx,
Gary H.
Tue May 22, 2018 4:17 pm (PDT) . Posted by:
garyhartling
Never mind. The formula with "https" worked after recalculating. --gh
Tue May 22, 2018 4:20 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Glad to hear you got it fixed. Such URL changes have been a common trend
the last year or so...
On Tue, May 22, 2018 at 4:17 PM, Gary.Hartling@
...
wrote:
>
> Never mind. The formula with "https" worked after recalculating. --gh
>
>
>
the last year or so...
On Tue, May 22, 2018 at 4:17 PM, Gary.Hartling@
...
wrote:
>
> Never mind. The formula with "https" worked after recalculating. --gh
>
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar