5 New Messages
Digest #2951
Messages
Fri Jan 31, 2014 7:28 pm (PST) . Posted by:
"Marc ." whichwaytobeach
Hello,
I am using the Google's Get Element #s: 3007, 3042, 3052, 3057, 3072, 3102, 3142, 3197, and 596. Most of the times, I do get numbers that come back. But, there are times when I get #VALUE! strung across all of them, but yet the data is there when you look at the Google Quarterly Balance Sheet. I am not talking Financials here. For instance, when I type in ANV, I get #VALUE!, yet, when you go to https://www.google.com/finance?q=AMEX:ANV&fstype=ii all of the data is there.
Why?
Thanks for any help.
Regards,
Marc
I am using the Google's Get Element #s: 3007, 3042, 3052, 3057, 3072, 3102, 3142, 3197, and 596. Most of the times, I do get numbers that come back. But, there are times when I get #VALUE! strung across all of them, but yet the data is there when you look at the Google Quarterly Balance Sheet. I am not talking Financials here. For instance, when I type in ANV, I get #VALUE!, yet, when you go to https://www.
Why?
Thanks for any help.
Regards,
Marc
Fri Jan 31, 2014 7:33 pm (PST) . Posted by:
mikemcq802
Use AMEX:ANV as the ticker for the RchGetElementNumber function.
Google requires more explicit tickers often.
Google requires more explicit tickers often.
Fri Jan 31, 2014 7:38 pm (PST) . Posted by:
"Marc ." whichwaytobeach
Thank you, Mike. It works like a charm!
I appreciate your writing back quickly!
Marc
To: smf_addin@yahoogroups.com
From: mikemcq802@yahoo.com
Date: Fri, 31 Jan 2014 19:33:29 -0800
Subject: [smf_addin] RE: #VALUE! Even Though The Data IS There...
Use AMEX:ANV as the ticker for the RchGetElementNumber function.
Google requires more explicit tickers often.
I appreciate your writing back quickly!
Marc
To: smf_addin@yahoogrou
From: mikemcq802@yahoo.
Date: Fri, 31 Jan 2014 19:33:29 -0800
Subject: [smf_addin] RE: #VALUE! Even Though The Data IS There...
Use AMEX:ANV as the ticker for the RchGetElementNumber function.
Google requires more explicit tickers often.
Fri Jan 31, 2014 8:38 pm (PST) . Posted by:
adam_sommers
Right - the formatting shouldn't matter. In my referenced formula =smfGetYahooOptionQuote(D16,"P",DATE(YEAR($E$1),MONTH($E$1),DAY($E$1)),H16,"a")
Cell D16 is COH
Cell E1 is 3/21/2014
And where I've identified the problem, cell H16 is 45.00 (formatting is irrelevant).
If I reference cell H16 in the formula, I get an error. If I type in "OTM2" it works just fine. I'm still stymied, and hope I explained my problem more succinctly.
Adam
---In smf_addin@yahoogroups.com, <rharmelink@...> wrote:
How a cell is formatted should be irrelevant. And whether it's a cell reference or a number or a literal should be irrelevant. An EXCEL (or user-defined) function would only use the value of the parameter.
Without knowing your cell values, I have no details to look at.
I just tried an example with both the literal "OTM1" and a cell with the value of "OTM1". Both worked fine for
me. I even added a numeric format on the "OTM1" cell value.
But I will mentions that "OTMx" and "ITMx" are unreliable with Yahoo on web page where they combine multiple expiration dates. It can also be unreliable on equities with a small number of options, since the ITM/OTM decisions key off of the shading Yahoo uses. So if there is no change in shading, the function will fail.
I always use smfGetOptionStrikes() to determine my ITM and OTM strike prices.
I probably should obsolete the "ITMx" and "OTMx" designations. Or change them to use the smfGetOptionStrikes() function instead -- which would allow it to be done for all sources, but slow down data retrieval times.
On Fri, Jan 31, 2014 at 2:47 PM, <adam@... mailto:adam@...> wrote:
Thank you, Randy for the updated version. But now that I've installed it, my SMFGetYahooOptionQuote function is no longer working when I refer to a strike price in a cell that is formatted as number or currency. I can get it to work if I put "OTM1" in the formula, but if I put in a cell reference for the strike of a Put, it generates "Error".'
Here is my formula: =smfGetYahooOptionQuote(D16,"P",DATE(YEAR($E$1),MONTH($E$1),DAY($E$1)),H16,"a"
Do you know why this is happening, and can it be fixed?
Cell D16 is COH
Cell E1 is 3/21/2014
And where I've identified the problem, cell H16 is 45.00 (formatting is irrelevant).
If I reference cell H16 in the formula, I get an error. If I type in "OTM2" it works just fine. I'm still stymied, and hope I explained my problem more succinctly.
Adam
---In smf_addin@yahoogrou
How a cell is formatted should be irrelevant. And whether it's a cell reference or a number or a literal should be irrelevant. An EXCEL (or user-defined) function would only use the value of the parameter.
Without knowing your cell values, I have no details to look at.
I just tried an example with both the literal "OTM1" and a cell with the value of "OTM1"
me. I even added a numeric format on the "OTM1" cell value.
But I will mentions that "OTMx" and "ITMx" are unreliable with Yahoo on web page where they combine multiple expiration dates. It can also be unreliable on equities with a small number of options, since the ITM/OTM decisions key off of the shading Yahoo uses. So if there is no change in shading, the function will fail.
I always use smfGetOptionStrikes
I probably should obsolete the "ITMx" and "OTMx" designations. Or change them to use the smfGetOptionStrikes
On Fri, Jan 31, 2014 at 2:47 PM, <adam@... mailto:adam@
Thank you, Randy for the updated version. But now that I've installed it, my SMFGetYahooOptionQu
Here is my formula: =smfGetYahooOptionQ
Do you know why this is happening, and can it be fixed?
Fri Jan 31, 2014 9:09 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
OK. The problem is the date. The March expiration date is 3/22/2014, not
3/21/2014. Options expire on Saturday, not Friday. Friday is just the last
date they can be traded.
The reason it works for "OTM2" is that such a parameter means the function
doesn't use the exact date provided. It just uses the month. That's because
there is only one change of shading on each monthly page, which is then
what triggers the lookup to find the strike price and retrieve its data.
Basically, "OTM2" says to get the strike price that is 2 rows above the
shading change.
BTW, you can get the March monthly expiration date with:
=smfGetOptionExpiry(2014,3,"M")
On Fri, Jan 31, 2014 at 9:38 PM, <adam@sommersfinancial.com> wrote:
> Right - the formatting shouldn't matter. In my referenced formula
> =smfGetYahooOptionQuote(D16,"P",DATE(YEAR($E$1),MONTH($E$1),DAY(
> $E$1)),H16,"a")
>
> Cell D16 is COH
>
> Cell E1 is 3/21/2014
>
> And where I've identified the problem, cell H16 is 45.00 (formatting is
> irrelevant).
>
> If I reference cell H16 in the formula, I get an error. If I type in
> "OTM2" it works just fine. I'm still stymied, and hope I explained my
> problem more succinctly.
>
3/21/2014. Options expire on Saturday, not Friday. Friday is just the last
date they can be traded.
The reason it works for "OTM2" is that such a parameter means the function
doesn't use the exact date provided. It just uses the month. That's because
there is only one change of shading on each monthly page, which is then
what triggers the lookup to find the strike price and retrieve its data.
Basically, "OTM2" says to get the strike price that is 2 rows above the
shading change.
BTW, you can get the March monthly expiration date with:
=smfGetOptionExpiry
On Fri, Jan 31, 2014 at 9:38 PM, <adam@sommersfinanci
> Right - the formatting shouldn'
> =smfGetYahooOptionQ
> $E$1)),H16,"
>
> Cell D16 is COH
>
> Cell E1 is 3/21/2014
>
> And where I've identified the problem, cell H16 is 45.00 (formatting is
> irrelevant).
>
> If I reference cell H16 in the formula, I get an error. If I type in
> "OTM2" it works just fine. I'm still stymied, and hope I explained my
> problem more succinctly.
>
Tidak ada komentar:
Posting Komentar