15 Messages
Digest #4182
Messages
Wed Oct 25, 2017 8:38 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Oops. Lazy reader. Sorry. Here's what I get right now:
SPY SPY SPY SPY SPY SPY
Y B G 8 OX N
2017-10-27 2017-10-27 Invalid Data Source: OX 2017-10-27
2017-11-01 2017-11-01 2017-11-01
2017-11-03 2017-11-03 2017-11-03
2017-11-08 2017-11-08 2017-11-08
2017-11-10 2017-11-10 2017-11-10
2017-11-15 2017-11-15 None None 2017-11-15
The OX change to flag as invalid is a recent one. As I said before,
Google is unreliable. "8" requires a PITA security cookie.
The others are misleading because "B" and "N" both go to Yahoo anyway. I
just allowed them as passable sources so the source could be a same value
for all of the various option functions, and not require a different one
for each. Barchart and NASDAQ would both be difficult to extract available
expiration dates.
The Yahoo processing has been the same since March.
It's basically pulling the expiration dates right out of the related JSON
file:
https://query2.finance.yahoo.com/v7/finance/options/SPY
You can check to see if you have issues with it. This should give you a
comma-delimited list of dates (as UNIX dates):
=smfStrExtr(RCHGetWebData("
https://query2.finance.yahoo.com/v7/finance/options/SPY
","""expirationDates"":"),"[","]")
The function just pulls them off one by one and converts each to an EXCEL
serial date value. You could grab them yourself with:
=INT(smfUNIX2Date(smfWord(smfStrExtr(RCHGetWebData("
https://query2.finance.yahoo.com/v7/finance/options/SPY
","""expirationDates"":"),"[","]"),1,",")))
Just increment that last value of 1 as needed.
On Wed, Oct 25, 2017 at 6:01 PM, jwalkergh@centurytel.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Thanks Randy but I was referring to Options Expirations not Options Quote.
> What are you using now for smfGetOptionExpirations?
> I am using a symbol of course >> e.g.smfGetOptionExpirations("SPY","B")
> (or any other source) returns "None" as the 1st element.
>
>
SPY SPY SPY SPY SPY SPY
Y B G 8 OX N
2017-10-27 2017-10-27 Invalid Data Source: OX 2017-10-27
2017-11-01 2017-11-01 2017-11-01
2017-11-03 2017-11-03 2017-11-03
2017-11-08 2017-11-08 2017-11-08
2017-11-10 2017-11-10 2017-11-10
2017-11-15 2017-11-15 None None 2017-11-15
The OX change to flag as invalid is a recent one. As I said before,
Google is unreliable. "8" requires a PITA security cookie.
The others are misleading because "B" and "N" both go to Yahoo anyway. I
just allowed them as passable sources so the source could be a same value
for all of the various option functions, and not require a different one
for each. Barchart and NASDAQ would both be difficult to extract available
expiration dates.
The Yahoo processing has been the same since March.
It's basically pulling the expiration dates right out of the related JSON
file:
https://query2.finance.yahoo.com/v7/finance/options/SPY
You can check to see if you have issues with it. This should give you a
comma-delimited list of dates (as UNIX dates):
=smfStrExtr(RCHGetWebData("
https://query2.finance.yahoo.com/v7/finance/options/SPY
","""expirationDates"":"),"[","]")
The function just pulls them off one by one and converts each to an EXCEL
serial date value. You could grab them yourself with:
=INT(smfUNIX2Date(smfWord(smfStrExtr(RCHGetWebData("
https://query2.finance.yahoo.com/v7/finance/options/SPY
","""expirationDates"":"),"[","]"),1,",")))
Just increment that last value of 1 as needed.
On Wed, Oct 25, 2017 at 6:01 PM, jwalkergh@centurytel.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Thanks Randy but I was referring to Options Expirations not Options Quote.
> What are you using now for smfGetOptionExpirat
> I am using a symbol of course >> e.g.smfGetOptionExp
> (or any other source) returns "None" as the 1st element.
>
>
Wed Oct 25, 2017 9:08 pm (PDT) . Posted by:
johnyahoo
OK, when I do "smfStrExtr(RCHGetWebData("https://query2.finance.yahoo.com/v7/finance/options/SPY","""expirationDates"":"),"[","]")" I do get a comma delimited list of dates - great, I can parse these if I have to.
But when I declare a variable say
dim vdates as variant
and then call
vdates = smfGetOptionExpirations("SPY","Y")
the 1st element is always "None" and the rest are "" (blank)
This has worked for years so I don't see why it doesn't work now.
Are you actually calling smfGetOptionExpirations(<symbol>, <source>) with success?
John
But when I declare a variable say
dim vdates as variant
and then call
vdates = smfGetOptionExpirat
the 1st element is always "None" and the rest are "" (blank)
This has worked for years so I don't see why it doesn't work now.
Are you actually calling smfGetOptionExpirat
John
Thu Oct 26, 2017 1:14 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
If I run this in VBA:
Dim vDates As Variant
vDates = smfGetOptionExpirations("SPY", "Y")
Range("B1:B25") = vDates
Because the default of smfGetOptionExpirations() is to:return 20 dates (if
available), I get this in that range:
2017-10-27
2017-11-01
2017-11-03
2017-11-08
2017-11-10
2017-11-15
2017-11-17
2017-11-22
2017-11-24
2017-11-29
2017-12-01
2017-12-15
2017-12-29
2018-01-19
2018-02-16
2018-03-16
2018-03-29
2018-06-15
2018-06-29
2018-07-20
#N/A
#N/A
#N/A
#N/A
#N/A
Can you step it through what it's doing in the smfGetOptionExpirations()
routine? For that routine, "None" is set as the default error condition,
when an unexpected error is encountered.
On Wed, Oct 25, 2017 at 9:08 PM, jwalkergh@
...
wrote:
>
> OK, when I do "smfStrExtr(RCHGetWebData("https://query2.finance.yahoo.com/
> v7/finance/options/SPY","""expirationDates"":"),"[","]")" I do get a
> comma delimited list of dates - great, I can parse these if I have to.
>
> But when I declare a variable say
> dim vdates as variant
> and then call
> vdates = smfGetOptionExpirations("SPY","Y")
>
> the 1st element is always "None" and the rest are "" (blank)
>
> This has worked for years so I don't see why it doesn't work now.
>
> Are you actually calling smfGetOptionExpirations(<symbol>, <source>) with
> success?
>
Dim vDates As Variant
vDates = smfGetOptionExpirations("SPY", "Y")
Range("B1:B25") = vDates
Because the default of smfGetOptionExpirations() is to:return 20 dates (if
available), I get this in that range:
2017-10-27
2017-11-01
2017-11-03
2017-11-08
2017-11-10
2017-11-15
2017-11-17
2017-11-22
2017-11-24
2017-11-29
2017-12-01
2017-12-15
2017-12-29
2018-01-19
2018-02-16
2018-03-16
2018-03-29
2018-06-15
2018-06-29
2018-07-20
#N/A
#N/A
#N/A
#N/A
#N/A
Can you step it through what it's doing in the smfGetOptionExpirations()
routine? For that routine, "None" is set as the default error condition,
when an unexpected error is encountered.
On Wed, Oct 25, 2017 at 9:08 PM, jwalkergh@
...
wrote:
>
> OK, when I do "smfStrExtr(RCHGetWebData("https://query2.finance.yahoo.com/
> v7/finance/options/
> comma delimited list of dates - great, I can parse these if I have to.
>
> But when I declare a variable say
> dim vdates as variant
> and then call
> vdates = smfGetOptionExpirat
>
> the 1st element is always "None" and the rest are "" (blank)
>
> This has worked for years so I don't see why it doesn't work now.
>
> Are you actually calling smfGetOptionExpirat
> success?
>
Thu Oct 26, 2017 9:32 am (PDT) . Posted by:
johnyahoo
When I run that exact code it returns "None" then blanks all the way down the column.
Stepping thru smfGetOptionExpirations:
in the SourceYahoo code section:
surl = http://query.yahooapis.com/v1/public/yql?q=SELECT%20*%20FROM%20yahoo.finance.options%20WHERE%20symbol=%22SPY%22%20AND%20expiration%20in%20%28SELECT%20contract%20FROM%20yahoo.finance.option_contracts%20WHERE%20symbol=%22SPY%22%29&env=http%3A%2F%2Fdatatables.org%2Falltables.env
s2 = RCHGetWebData(surl, s1, 400) where s1 = "SPY171026C"
s2 returns "Error"
So, over to RCHGetWebData where the line
s1 = smfGetWebPage(pURL, pUseIE, 0) returns "" for s1
pUseIE = 0
since s1 is empty then RCHGetWebData returns "Error"
So, on to smfGetWebPage:
aData(iData, 1) = 0:http://query.yahooapis.com/v1/public/yql?q=SELECT%20*%20FROM%20yahoo.finance.options%20WHERE%20symbol=%22SPY%22%20AND%20expiration%20in%20%28SELECT%20contract%20FROM%20yahoo.finance.option_contracts%20WHERE%20symbol=%22SPY%22%29&env=http%3A%2F%2Fdatatables.org%2Falltables.env
then
Case aData(iData, 1) = pUseIE & ":" & pURL
smfGetWebPage = aData(iData, 2)
Exit Function
aData(iData, 2) is "" so function exits
So it seems smfGetWebPage is at the heart of the matter - it returns blank.
John
Stepping thru smfGetOptionExpirations:
in the SourceYahoo code section:
surl = http://query.yahooapis.com/v1/public/yql?q=SELECT%20*%20FROM%20yahoo.finance.options%20WHERE%20symbol=%22SPY%22%20AND%20expiration%20in%20%28SELECT%20contract%20FROM%20yahoo.finance.option_contracts%20WHERE%20symbol=%22SPY%22%29&env=http%3A%2F%2Fdatatables.org%2Falltables.env
s2 = RCHGetWebData(surl, s1, 400) where s1 = "SPY171026C"
s2 returns "Error"
So, over to RCHGetWebData where the line
s1 = smfGetWebPage(pURL, pUseIE, 0) returns "" for s1
pUseIE = 0
since s1 is empty then RCHGetWebData returns "Error"
So, on to smfGetWebPage:
aData(iData, 1) = 0:http://query.yahooapis.com/v1/public/yql?q=SELECT%20*%20FROM%20yahoo.finance.options%20WHERE%20symbol=%22SPY%22%20AND%20expiration%20in%20%28SELECT%20contract%20FROM%20yahoo.finance.option_contracts%20WHERE%20symbol=%22SPY%22%29&env=http%3A%2F%2Fdatatables.org%2Falltables.env
then
Case aData(iData, 1) = pUseIE & ":" & pURL
smfGetWebPage = aData(iData, 2)
Exit Function
aData(iData, 2) is "" so function exits
So it seems smfGetWebPage is at the heart of the matter - it returns blank.
John
Thu Oct 26, 2017 11:24 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
That's not the code for the 2017.09.17 version of the add-in. It's much
older. Check your version:
=RCHGetElementNumber("Version")
On Thu, Oct 26, 2017 at 9:32 AM, jwalkergh@
...
wrote:
>
> When I run that exact code it returns "None" then blanks all the way down
> the column.
>
> Stepping thru smfGetOptionExpirations:
> in the SourceYahoo code section:
>
> surl = http://query.yahooapis.com/v1/public/yql?q=SELECT%20*%
> 20FROM%20yahoo.finance.options%20WHERE%20symbol=%
> 22SPY%22%20AND%20expiration%20in%20%28SELECT%20contract%
> 20FROM%20yahoo.finance.option_contracts%20WHERE%20symbol=%
> 22SPY%22%29&env=http%3A%2F%2Fdatatables.org%2Falltables.env
>
> s2 = RCHGetWebData(surl, s1, 400) where s1 = "SPY171026C"
> s2 returns "Error"
>
> So, over to RCHGetWebData where the line
>
> s1 = smfGetWebPage(pURL, pUseIE, 0) returns "" for s1
> pUseIE = 0
> since s1 is empty then RCHGetWebData returns "Error"
>
> So, on to smfGetWebPage:
> aData(iData, 1) = 0:http://query.yahooapis.com/
> v1/public/yql?q=SELECT%20*%20FROM%20yahoo.finance.
> options%20WHERE%20symbol=%22SPY%22%20AND%20expiration%
> 20in%20%28SELECT%20contract%20FROM%20yahoo.finance.option_
> contracts%20WHERE%20symbol=%22SPY%22%29&env=http%3A%2F%
> 2Fdatatables.org%2Falltables.env
>
> then
> Case aData(iData, 1) = pUseIE & ":" & pURL
> smfGetWebPage = aData(iData, 2)
> Exit Function
> aData(iData, 2) is "" so function exits
>
> So it seems smfGetWebPage is at the heart of the matter - it returns
> blank.
>
>
older. Check your version:
=RCHGetElementNumber("Version")
On Thu, Oct 26, 2017 at 9:32 AM, jwalkergh@
...
wrote:
>
> When I run that exact code it returns "None" then blanks all the way down
> the column.
>
> Stepping thru smfGetOptionExpirations:
> in the SourceYahoo code section:
>
> surl = http://query.yahooapis.com/v1/public/yql?q=SELECT%20*%
> 20FROM%20yahoo.finance.options%20WHERE%20symbol=%
> 22SPY%22%20AND%20expiration%20in%20%28SELECT%20contract%
> 20FROM%20yahoo.finance.option_contracts%20WHERE%20symbol=%
> 22SPY%22%29&env=http%3A%2F%2Fdatatables.org%2Falltables.env
>
> s2 = RCHGetWebData(surl, s1, 400) where s1 = "SPY171026C"
> s2 returns "Error"
>
> So, over to RCHGetWebData where the line
>
> s1 = smfGetWebPage(pURL, pUseIE, 0) returns "" for s1
> pUseIE = 0
> since s1 is empty then RCHGetWebData returns "Error"
>
> So, on to smfGetWebPage:
> aData(iData, 1) = 0:http://query.yahooapis.com/
> v1/public/yql?
> options%20WHERE%
> 20in%20%28SELECT%
> contracts%20WHERE%
> 2Fdatatables.
>
> then
> Case aData(iData, 1) = pUseIE & ":" & pURL
> smfGetWebPage = aData(iData, 2)
> Exit Function
> aData(iData, 2) is "" so function exits
>
> So it seems smfGetWebPage is at the heart of the matter - it returns
> blank.
>
>
Thu Oct 26, 2017 11:48 am (PDT) . Posted by:
johnyahoo
When I execute RCHGetElementNumber("Version") I get:
Stock Market Functions add-in, Version 2.1.2017.09.17 (E:\Prog\excel\RCH_Excel_Functions; Windows (32-bit) NT 6.01; 14.0; ; ; 1)
The files came in RCH_Stock_Market_Functions-2.1.2017.09.17.zip which I downloaded 2 days ago.
I shut down excel before moving the files to the correct folder.
The .xla file is RCH_Stock_Market_Functions.xla, size 803,840 dated 9/17/17.
In the Excel File - Options - Add-ins window Excel is pointed to the correct directory and the file above.
John
Stock Market Functions add-in, Version 2.1.2017.09.
The files came in RCH_Stock_Market_
I shut down excel before moving the files to the correct folder.
The .xla file is RCH_Stock_Market_
In the Excel File - Options - Add-ins window Excel is pointed to the correct directory and the file above.
John
Thu Oct 26, 2017 12:55 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
That's truly bizarre. The code you cited isn't in my version of the add-in
at all.
My version of the "Source_Yahoo" code in smfGetOptionExpirations looks like
this:
'------------------> Yahoo processing after 2017-03-15
Source_Yahoo:
sURL = "https://query2.finance.yahoo.com/v7/finance/options/" &
UCase(pTicker)
s1 = smfStrExtr(RCHGetWebData(sURL, """expirationDates"":[", 500), "[",
"]")
For iRow = 1 To kRows
s2 = smfWord(s1, iRow, ",")
If s2 = "" Then Exit For
vData(iRow, 1) = Int((s2 - 18000) / 86400 + 25570)
Next iRow
GoTo ExitFunction
As I recall, the "http://query.yahooapis.com" service has been gone for a
few years? Based on my change log, that code was probably added in 2013 and
then replaced in 2014 when Yahoo had one of their many changes over the
years..
Try this:
1. Go into the add-in manager and uncheck the SMF add-in entry (or entries?)
2. Exit EXCEL (this should remove any pointers to the add-in)
3. Restart EXCEL
4. Go into the add-in manager and add the SMF add-in back using the
"Browse" option
5. Exit and restart EXCEL (to save changes)
6. Check the version again
7. Try the smfGetOptionExpirations() function again,
On Thu, Oct 26, 2017 at 11:48 AM, jwalkergh@centurytel.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> When I execute RCHGetElementNumber("Version") I get:
>
> Stock Market Functions add-in, Version 2.1.2017.09.17
> (E:\Prog\excel\RCH_Excel_Functions; Windows (32-bit) NT 6.01; 14.0; ; ; 1)
>
> The files came in RCH_Stock_Market_Functions-2.1.2017.09.17.zip which I
> downloaded 2 days ago.
> I shut down excel before moving the files to the correct folder.
>
> The .xla file is RCH_Stock_Market_Functions.xla, size 803,840 dated
> 9/17/17.
>
> In the Excel File - Options - Add-ins window Excel is pointed to the
> correct directory and the file above.
>
>
at all.
My version of the "Source_Yahoo" code in smfGetOptionExpirations looks like
this:
'------------------> Yahoo processing after 2017-03-15
Source_Yahoo:
sURL = "https://query2.finance.yahoo.com/v7/finance/options/" &
UCase(pTicker)
s1 = smfStrExtr(RCHGetWebData(sURL, """expirationDates"":[", 500), "[",
"]")
For iRow = 1 To kRows
s2 = smfWord(s1, iRow, ",")
If s2 = "" Then Exit For
vData(iRow, 1) = Int((s2 - 18000) / 86400 + 25570)
Next iRow
GoTo ExitFunction
As I recall, the "http://query.yahooapis.com" service has been gone for a
few years? Based on my change log, that code was probably added in 2013 and
then replaced in 2014 when Yahoo had one of their many changes over the
years..
Try this:
1. Go into the add-in manager and uncheck the SMF add-in entry (or entries?)
2. Exit EXCEL (this should remove any pointers to the add-in)
3. Restart EXCEL
4. Go into the add-in manager and add the SMF add-in back using the
"Browse" option
5. Exit and restart EXCEL (to save changes)
6. Check the version again
7. Try the smfGetOptionExpirations() function again,
On Thu, Oct 26, 2017 at 11:48 AM, jwalkergh@centurytel.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> When I execute RCHGetElementNumber
>
> Stock Market Functions add-in, Version 2.1.2017.09.
> (E:\Prog&#
>
> The files came in RCH_Stock_Market_
> downloaded 2 days ago.
> I shut down excel before moving the files to the correct folder.
>
> The .xla file is RCH_Stock_Market_
> 9/17/17.
>
> In the Excel File - Options - Add-ins window Excel is pointed to the
> correct directory and the file above.
>
>
Thu Oct 26, 2017 4:42 pm (PDT) . Posted by:
johnyahoo
Bizarre indeed.
We have met the enemy and they are us.
As usual Randy, it was not a problem with your code, it was a user problem.
The file was correct, the location was correct, the references were correct.
I did all the above and still had the same problem so then just for kicks I ran the code with no reference to your .xla file and it ran. With the same error but it ran and it should not have.
Obviously Excel should have thrown an error when it couldn't find that function. But it could! Reason: couple of years ago I extracted that module from your .xla file to do something or other and forgot is was down in my modules section.
Excel checks first in local modules, then external, so it found it, and executed it.
Strange thing is it worked correctly at least as recently as September. Yahoo must have stopped allowing that call.
Now that I know the problem I will clean up my modules section.
I apologize for taking up your time on this.
Thanks
John
We have met the enemy and they are us.
As usual Randy, it was not a problem with your code, it was a user problem.
The file was correct, the location was correct, the references were correct.
I did all the above and still had the same problem so then just for kicks I ran the code with no reference to your .xla file and it ran. With the same error but it ran and it should not have.
Obviously Excel should have thrown an error when it couldn't find that function. But it could! Reason: couple of years ago I extracted that module from your .xla file to do something or other and forgot is was down in my modules section.
Excel checks first in local modules, then external, so it found it, and executed it.
Strange thing is it worked correctly at least as recently as September. Yahoo must have stopped allowing that call.
Now that I know the problem I will clean up my modules section.
I apologize for taking up your time on this.
Thanks
John
Wed Oct 25, 2017 9:11 pm (PDT) . Posted by:
"Jonathan" jbr863
Hey Randy, smfGetGuruFocusItem worked for Free Cash Flow (Item # 137) but not for Enterprise Value (Item # 151) and Net Net Working Capital (no Item # was included). I got the item numbers from the smfLoadGuruFocusItems2 function. I got the item numbers for the smfGetGuruFocusData function from smfLoadGuruFocusItems.
On Wednesday, October 25, 2017, 1:45:26 PM PDT, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
Try using smfGetGuruFocusItem() instead. smfGetGuruFocusData() is an older version of that routine and was never really mentioned other than in the add-in change log? Or did I announce it? Where did you get the #203 and #41?
On Wed, Oct 25, 2017 at 7:27 AM, jbr863@... wrote:
When I tried smfGetGuruFocusData(" AGX", 203, "Y") recently, where Item # 203 represents Free Cash Flow, it just returned the company name. From what I can tell the URL is forming correctly so it may be something in the extract and data processing lines.
I believe that I'm seeing a similar issue with Net Net Working Capital (Item # 41). Your help in figuring out why it's not pulling the correct numerical data will be appreciated, as always.
#yiv4222155652 #yiv4222155652 -- #yiv4222155652ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv4222155652 #yiv4222155652ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv4222155652 #yiv4222155652ygrp-mkp #yiv4222155652hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv4222155652 #yiv4222155652ygrp-mkp #yiv4222155652ads {margin-bottom:10px;}#yiv4222155652 #yiv4222155652ygrp-mkp .yiv4222155652ad {padding:0 0;}#yiv4222155652 #yiv4222155652ygrp-mkp .yiv4222155652ad p {margin:0;}#yiv4222155652 #yiv4222155652ygrp-mkp .yiv4222155652ad a {color:#0000ff;text-decoration:none;}#yiv4222155652 #yiv4222155652ygrp-sponsor #yiv4222155652ygrp-lc {font-family:Arial;}#yiv4222155652 #yiv4222155652ygrp-sponsor #yiv4222155652ygrp-lc #yiv4222155652hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv4222155652 #yiv4222155652ygrp-sponsor #yiv4222155652ygrp-lc .yiv4222155652ad {margin-bottom:10px;padding:0 0;}#yiv4222155652 #yiv4222155652actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv4222155652 #yiv4222155652activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv4222155652 #yiv4222155652activity span {font-weight:700;}#yiv4222155652 #yiv4222155652activity span:first-child {text-transform:uppercase;}#yiv4222155652 #yiv4222155652activity span a {color:#5085b6;text-decoration:none;}#yiv4222155652 #yiv4222155652activity span span {color:#ff7900;}#yiv4222155652 #yiv4222155652activity span .yiv4222155652underline {text-decoration:underline;}#yiv4222155652 .yiv4222155652attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv4222155652 .yiv4222155652attach div a {text-decoration:none;}#yiv4222155652 .yiv4222155652attach img {border:none;padding-right:5px;}#yiv4222155652 .yiv4222155652attach label {display:block;margin-bottom:5px;}#yiv4222155652 .yiv4222155652attach label a {text-decoration:none;}#yiv4222155652 blockquote {margin:0 0 0 4px;}#yiv4222155652 .yiv4222155652bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv4222155652 .yiv4222155652bold a {text-decoration:none;}#yiv4222155652 dd.yiv4222155652last p a {font-family:Verdana;font-weight:700;}#yiv4222155652 dd.yiv4222155652last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv4222155652 dd.yiv4222155652last p span.yiv4222155652yshortcuts {margin-right:0;}#yiv4222155652 div.yiv4222155652attach-table div div a {text-decoration:none;}#yiv4222155652 div.yiv4222155652attach-table {width:400px;}#yiv4222155652 div.yiv4222155652file-title a, #yiv4222155652 div.yiv4222155652file-title a:active, #yiv4222155652 div.yiv4222155652file-title a:hover, #yiv4222155652 div.yiv4222155652file-title a:visited {text-decoration:none;}#yiv4222155652 div.yiv4222155652photo-title a, #yiv4222155652 div.yiv4222155652photo-title a:active, #yiv4222155652 div.yiv4222155652photo-title a:hover, #yiv4222155652 div.yiv4222155652photo-title a:visited {text-decoration:none;}#yiv4222155652 div#yiv4222155652ygrp-mlmsg #yiv4222155652ygrp-msg p a span.yiv4222155652yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv4222155652 .yiv4222155652green {color:#628c2a;}#yiv4222155652 .yiv4222155652MsoNormal {margin:0 0 0 0;}#yiv4222155652 o {font-size:0;}#yiv4222155652 #yiv4222155652photos div {float:left;width:72px;}#yiv4222155652 #yiv4222155652photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv4222155652 #yiv4222155652photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv4222155652 #yiv4222155652reco-category {font-size:77%;}#yiv4222155652 #yiv4222155652reco-desc {font-size:77%;}#yiv4222155652 .yiv4222155652replbq {margin:4px;}#yiv4222155652 #yiv4222155652ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv4222155652 #yiv4222155652ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv4222155652 #yiv4222155652ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv4222155652 #yiv4222155652ygrp-mlmsg select, #yiv4222155652 input, #yiv4222155652 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv4222155652 #yiv4222155652ygrp-mlmsg pre, #yiv4222155652 code {font:115% monospace;}#yiv4222155652 #yiv4222155652ygrp-mlmsg * {line-height:1.22em;}#yiv4222155652 #yiv4222155652ygrp-mlmsg #yiv4222155652logo {padding-bottom:10px;}#yiv4222155652 #yiv4222155652ygrp-msg p a {font-family:Verdana;}#yiv4222155652 #yiv4222155652ygrp-msg p#yiv4222155652attach-count span {color:#1E66AE;font-weight:700;}#yiv4222155652 #yiv4222155652ygrp-reco #yiv4222155652reco-head {color:#ff7900;font-weight:700;}#yiv4222155652 #yiv4222155652ygrp-reco {margin-bottom:20px;padding:0px;}#yiv4222155652 #yiv4222155652ygrp-sponsor #yiv4222155652ov li a {font-size:130%;text-decoration:none;}#yiv4222155652 #yiv4222155652ygrp-sponsor #yiv4222155652ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv4222155652 #yiv4222155652ygrp-sponsor #yiv4222155652ov ul {margin:0;padding:0 0 0 8px;}#yiv4222155652 #yiv4222155652ygrp-text {font-family:Georgia;}#yiv4222155652 #yiv4222155652ygrp-text p {margin:0 0 1em 0;}#yiv4222155652 #yiv4222155652ygrp-text tt {font-size:120%;}#yiv4222155652 #yiv4222155652ygrp-vital ul li:last-child {border-right:none !important;}#yiv4222155652
On Wednesday, October 25, 2017, 1:45:26 PM PDT, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
Try using smfGetGuruFocusItem
On Wed, Oct 25, 2017 at 7:27 AM, jbr863@... wrote:
When I tried smfGetGuruFocu
I believe that I'm seeing a similar issue with Net Net Working Capital (Item # 41). Your help in figuring out why it's not pulling the correct numerical data will be appreciated, as always.
#yiv4222155652 #yiv4222155652 -- #yiv4222155652ygrp-
Thu Oct 26, 2017 2:19 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
You should use the template from the web site to get the item numbers:
smfGetGuruFocusItem-Full-Template.xls
<http://ogres-crypt.com/SMF/Works-In-Progress/smfGetGuruFocusItem-Full-Template.xls>
Both Free Cash Flow and Enterprise Value seem to be working for AGX when I
try it there:
*AGX*
Fiscal Period Jan03 Jan04 Jan05
Free Cash Flow 0.2 -0.7 -2.5
Enterprise Value 3.7 5.3 18.2
GuruFocus must have added the "Net-Net" line item since I wrote the routine
originally.
Are you a subscriber to GuruFocus? The next release of the add-in will be
able to extract data from the 30-year CSV file. For example, something like:
=smfGetGuruFocusCSVItem("AGX","Net-Net Working Capital",2)
That last parameter can be 1 thru 153 or "A29" thru "A0" or "TTM" or "Q119"
thru "Q0". For example:
*Line Item* *2* *31* *32* *34* *153* *A29* *A0* *TTM* *Q119* *Q0*
*Fiscal Period* 198701 201701 TTM/current 198404 201707 198701 201701
TTM/current 198404 201707
*Net-Net Working Capital* 0 13.68 16.45 0 16.45 0 13.68 16.45 0 16.45
Much quicker and a lot more data. Of course, the quickest way to get all of
the data would simply be:
=smfGetCSVFile("
https://www.gurufocus.com/download_financials_in_CSV.php?symbol=AGX")
It's a lot of data! :)
On Wed, Oct 25, 2017 at 9:07 PM, Jonathan jbr863@
...
wrote:
>
> smfGetGuruFocusItem worked for Free Cash Flow (Item # 137) but not for
> Enterprise Value (Item # 151) and Net Net Working Capital (no Item # was
> included). I got the item numbers from the smfLoadGuruFocusItems2
> function. I got the item numbers for the smfGetGuruFocusData function
> from smfLoadGuruFocusItems.
>
> On Wednesday, October 25, 2017, 1:45:26 PM PDT, Randy Harmelink
> rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
> Try using smfGetGuruFocusItem() instead. smfGetGuruFocusData() is an older
> version of that routine and was never really mentioned other than in the
> add-in change log? Or did I announce it? Where did you get the #203 and #41?
>
> On Wed, Oct 25, 2017 at 7:27 AM, jbr863@
> ...
> wrote:
>
> When I tried smfGetGuruFocusData(" AGX", 203, "Y") recently, where Item
> # 203 represents Free Cash Flow, it just returned the company name. From
> what I can tell the URL is forming correctly so it may be something in the
> extract and data processing lines.
>
> I believe that I'm seeing a similar issue with Net Net Working Capital
> (Item # 41). Your help in figuring out why it's not pulling the correct
> numerical data will be appreciated, as always.
>
>
>
smfGetGuruFocusItem-Full-Template.xls
<http://ogres-crypt.com/SMF/Works-In-Progress/smfGetGuruFocusItem-Full-Template.xls>
Both Free Cash Flow and Enterprise Value seem to be working for AGX when I
try it there:
*AGX*
Fiscal Period Jan03 Jan04 Jan05
Free Cash Flow 0.2 -0.7 -2.5
Enterprise Value 3.7 5.3 18.2
GuruFocus must have added the "Net-Net" line item since I wrote the routine
originally.
Are you a subscriber to GuruFocus? The next release of the add-in will be
able to extract data from the 30-year CSV file. For example, something like:
=smfGetGuruFocusCSVItem("AGX","Net-Net Working Capital",2)
That last parameter can be 1 thru 153 or "A29" thru "A0" or "TTM" or "Q119"
thru "Q0". For example:
*Line Item* *2* *31* *32* *34* *153* *A29* *A0* *TTM* *Q119* *Q0*
*Fiscal Period* 198701 201701 TTM/current 198404 201707 198701 201701
TTM/current 198404 201707
*Net-Net Working Capital* 0 13.68 16.45 0 16.45 0 13.68 16.45 0 16.45
Much quicker and a lot more data. Of course, the quickest way to get all of
the data would simply be:
=smfGetCSVFile("
https://www.gurufocus.com/download_financials_in_CSV.php?symbol=AGX")
It's a lot of data! :)
On Wed, Oct 25, 2017 at 9:07 PM, Jonathan jbr863@
...
wrote:
>
> smfGetGuruFocusItem worked for Free Cash Flow (Item # 137) but not for
> Enterprise Value (Item # 151) and Net Net Working Capital (no Item # was
> included). I got the item numbers from the smfLoadGuruFocusItems2
> function. I got the item numbers for the smfGetGuruFocusData function
> from smfLoadGuruFocusItems.
>
> On Wednesday, October 25, 2017, 1:45:26 PM PDT, Randy Harmelink
> rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
> Try using smfGetGuruFocusItem
> version of that routine and was never really mentioned other than in the
> add-in change log? Or did I announce it? Where did you get the #203 and #41?
>
> On Wed, Oct 25, 2017 at 7:27 AM, jbr863@
> ...
> wrote:
>
> When I tried smfGetGuruFocusData
> # 203 represents Free Cash Flow, it just returned the company name. From
> what I can tell the URL is forming correctly so it may be something in the
> extract and data processing lines.
>
> I believe that I'm seeing a similar issue with Net Net Working Capital
> (Item # 41). Your help in figuring out why it's not pulling the correct
> numerical data will be appreciated, as always.
>
>
>
Thu Oct 26, 2017 10:13 am (PDT) . Posted by:
cere0003
Sorry if this has been explained but I wasn't able to find it searching. I'm trying to pull the analyst price targets from yahoo. high, low average. The page is https://finance.yahoo.com/quote/TSS/chart?p=TSS https://finance.yahoo.com/quote/TSS/chart?p=TSS I used to be able to do it with =RCHGetTableCell("https://finance.yahoo.com/q/ao?s="&G3,1,">mean Target")
I know Yahoo has changed some things and my limited tech knowledge has not been able to figure it out. In my old formula, G3 is the excel cell with the ticker. I'm not sure RCHGetTableCell is the right one to use anymore either. The targets used to be in a table in the main body of the page but it is not anymore.
Thanks for any help!
Kevin
I know Yahoo has changed some things and my limited tech knowledge has not been able to figure it out. In my old formula, G3 is the excel cell with the ticker. I'm not sure RCHGetTableCell is the right one to use anymore either. The targets used to be in a table in the main body of the page but it is not anymore.
Thanks for any help!
Kevin
Thu Oct 26, 2017 12:41 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
You're right...the analyst web page no longer contains the data. The web
page is built dynamically from data retrieved from a JSON call. You'd now
need to extract the data from that JSON file with something like:
=smfGetYahooJSONField("TSS","financialData","quoteSummary.result.0.financialData.targetMeanPrice.raw")
=smfGetYahooJSONField("TSS","financialData","quoteSummary.result.0.financialData.targetMedianPrice.raw")
The stocks XLS file here has a list of JSON modules (i.e. "financialData")
and field names known to me:
http://ogres-crypt.com/SMF/Elements/
On Thu, Oct 26, 2017 at 10:13 AM, cere0003@
...
wrote:
> Sorry if this has been explained but I wasn't able to find it searching.
> I'm trying to pull the analyst price targets from yahoo. high, low
> average. The page is https://finance.yahoo.com/quote/TSS/chart?p=TSS I
> used to be able to do it with =RCHGetTableCell("https:/
> /finance.yahoo.com/q/ao?s="&G3,1,">mean Target")
>
> I know Yahoo has changed some things and my limited tech knowledge has not
> been able to figure it out. In my old formula, G3 is the excel cell with
> the ticker. I'm not sure RCHGetTableCell is the right one to use anymore
> either. The targets used to be in a table in the main body of the page but
> it is not anymore.
>
>
>
page is built dynamically from data retrieved from a JSON call. You'd now
need to extract the data from that JSON file with something like:
=smfGetYahooJSONField("TSS","financialData","quoteSummary.result.0.financialData.targetMeanPrice.raw")
=smfGetYahooJSONField("TSS","financialData","quoteSummary.result.0.financialData.targetMedianPrice.raw")
The stocks XLS file here has a list of JSON modules (i.e. "financialData")
and field names known to me:
http://ogres-crypt.com/SMF/Elements/
On Thu, Oct 26, 2017 at 10:13 AM, cere0003@
...
wrote:
> Sorry if this has been explained but I wasn't able to find it searching.
> I'm trying to pull the analyst price targets from yahoo. high, low
> average. The page is https://finance.yahoo.com/quote/TSS/chart?p=TSS I
> used to be able to do it with =RCHGetTableCell(
> /finance.yahoo.
>
> I know Yahoo has changed some things and my limited tech knowledge has not
> been able to figure it out. In my old formula, G3 is the excel cell with
> the ticker. I'm not sure RCHGetTableCell is the right one to use anymore
> either. The targets used to be in a table in the main body of the page but
> it is not anymore.
>
>
>
Thu Oct 26, 2017 2:57 pm (PDT) . Posted by:
john_hoel
I just noticed that the SMF additions to the context menu, such as 'calculate selection', are missing with Excel 365. How can I activate them?
Thu Oct 26, 2017 6:38 pm (PDT) . Posted by:
timdbui
Hello Randy,
Do you have an Element Number to get the CIK code of a company?
Thank you!
Do you have an Element Number to get the CIK code of a company?
Thank you!
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar