7 Messages
Digest #4372
Messages
Wed Aug 1, 2018 7:32 am (PDT) . Posted by:
"Dan Smith" dan.smith33220@gmail.com
Answers to your questions:
Stock Market Functions add-in, Version 2.1.2018.01.24 (C:\SMF Add-In; Windows (32-bit) NT :.00; 15.0; ; ; 1)
1. After performing the steps in my initial post, I get: Stock Market Functions add-in, Version 2.1.2018.01.24 (C:\SMF Add-In; Windows (32-bit) NT :.00; 15.0; ; ; 1)
2. #NAME?
3. a. The formula is ='C:\SMF Add-In\RCH_Stock_Market_Functions.xla'!smfPricesByDates(AB$2,$Z3) if I choose DON'T UPDATE when the file opens
b. The formula is =smfPricesByDates(AB$2,$Z3) if I DO UPDATE when the file opens and perform the steps indicated in my first post (in which case, the formulas work)
As additional information, the fix links macro is NOT available for 2 and 3a above.
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Wednesday, August 1, 2018 2:40 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] The dreaded #NAME?
It can't be enabling macros. When you install an add-in, you've already given it permission. It doesn't need any more.
Three things:
1. What do you get with:
=RCHGetElementNumber("Version")
2. Exit EXCEL, open EXCEL with an empty worksheet, and try that function again. Do you get the same thing?
3. On the worksheet you're having troubles with, what does the formula in one of the #NAME? cells look like? ,
On Tue, Jul 31, 2018 at 7:46 PM, 'Dan Smith' dan.smith33220@
....
wrote:
Thanks for the quick response.
Ran the macro, saved the file, closed file and reopened. Same problem.
I'm not sure the problem is unresolved links. I'm able to solve the problem by making the choices on the Excel messages as previously indicated. This ends up simply enabling macros. It seems the links are ok, it's just that Excel doesn't want to execute the macros without them being manually enabled.
Stock Market Functions add-in, Version 2.1.2018.01.24 (C:\SMF Add-In; Windows (32-bit) NT :.00; 15.0; ; ; 1)
1. After performing the steps in my initial post, I get: Stock Market Functions add-in, Version 2.1.2018.01.24 (C:\SMF Add-In; Windows (32-bit) NT :.00; 15.0; ; ; 1)
2. #NAME?
3. a. The formula is ='C:\SMF Add-In\RCH_Stock_Market_Functions.xla'!smfPricesByDates(AB$2,$Z3) if I choose DON'T UPDATE when the file opens
b. The formula is =smfPricesByDates(AB$2,$Z3) if I DO UPDATE when the file opens and perform the steps indicated in my first post (in which case, the formulas work)
As additional information, the fix links macro is NOT available for 2 and 3a above.
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Wednesday, August 1, 2018 2:40 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] The dreaded #NAME?
It can't be enabling macros. When you install an add-in, you've already given it permission. It doesn't need any more.
Three things:
1. What do you get with:
=RCHGetElementNumbe
2. Exit EXCEL, open EXCEL with an empty worksheet, and try that function again. Do you get the same thing?
3. On the worksheet you're having troubles with, what does the formula in one of the #NAME? cells look like? ,
On Tue, Jul 31, 2018 at 7:46 PM, 'Dan Smith' dan.smith33220@
....
wrote:
Thanks for the quick response.
Ran the macro, saved the file, closed file and reopened. Same problem.
I'm not sure the problem is unresolved links. I'm able to solve the problem by making the choices on the Excel messages as previously indicated. This ends up simply enabling macros. It seems the links are ok, it's just that Excel doesn't want to execute the macros without them being manually enabled.
Wed Aug 1, 2018 3:26 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Because of (2) -- getting #NAME? when you first start up EXCEL -- I would
assume that add-in is not activated. The macro not being available pretty
much confirms that. When you resolve the links through EXCEL, going from
(3a) to (3b), you're using the add-in simply as links, not as an add-in..
Which can cause different issues in different versions of EXCEL.
So, make sure the add-in is activated in the add-in manager. (keyboard
shortcut alt+t+i).
You may have the issue that first occurred in July of 2016 because
MicroSoft issued some security updates, which prevented EXCEL from loading
the add-in when EXCEL starts up. For a workaround suggested by Microsoft,
until they supposedly fix the issue, see:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/30568
If the add-in isn't be loaded by EXCEL at startup, you would need to
resolve links every time you open a workbook. Some people were doing a
workaround of manually activating the add-in every time they opened EXCEL.
But the Microsoft workaround above should fix the problem, if that's the
problem you have?
On Wed, Aug 1, 2018 at 7:32 AM, 'Dan Smith' dan.smith33220@
....
wrote:
>
> Answers to your questions:
>
>
>
> Stock Market Functions add-in, Version 2.1.2018..01.24 (C:\SMF Add-In;
> Windows (32-bit) NT :.00; 15.0; ; ; 1)
>
> 1. After performing the steps in my initial post, I get: Stock
> Market Functions add-in, Version 2.1.2018.01.24 (C:\SMF Add-In; Windows
> (32-bit) NT :.00; 15.0; ; ; 1)
>
> 2. #NAME?
>
> 3. a. The formula is ='C:\SMF Add-In\RCH_Stock_Market_
> Functions.xla'!smfPricesByDates(AB$2,$Z3) if I choose DON'T UPDATE when
> the file opens
>
> b. The formula is =smfPricesByDates(AB$2,$Z3) if I DO UPDATE when the file
> opens and perform the steps indicated in my first post (in which case, the
> formulas work)
>
>
>
> As additional information, the fix links macro is NOT available for 2 and
> 3a above.
>
>
>
> *From:* smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
> *Sent:* Wednesday, August 1, 2018 2:40 AM
> *To:* smf_addin@yahoogroups.com
> *Subject:* Re: [smf_addin] The dreaded #NAME?
>
>
>
>
>
> It can't be enabling macros. When you install an add-in, you've already
> given it permission. It doesn't need any more.
>
>
>
> Three things:
>
>
>
> 1. What do you get with:
>
>
>
> =RCHGetElementNumber("Version")
>
>
>
> 2. Exit EXCEL, open EXCEL with an empty worksheet, and try that function
> again. Do you get the same thing?
>
>
>
> 3. On the worksheet you're having troubles with, what does the formula in
> one of the #NAME? cells look like? ,
>
>
>
>
>
assume that add-in is not activated. The macro not being available pretty
much confirms that. When you resolve the links through EXCEL, going from
(3a) to (3b), you're using the add-in simply as links, not as an add-in..
Which can cause different issues in different versions of EXCEL.
So, make sure the add-in is activated in the add-in manager. (keyboard
shortcut alt+t+i).
You may have the issue that first occurred in July of 2016 because
MicroSoft issued some security updates, which prevented EXCEL from loading
the add-in when EXCEL starts up. For a workaround suggested by Microsoft,
until they supposedly fix the issue, see:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/30568
If the add-in isn't be loaded by EXCEL at startup, you would need to
resolve links every time you open a workbook. Some people were doing a
workaround of manually activating the add-in every time they opened EXCEL.
But the Microsoft workaround above should fix the problem, if that's the
problem you have?
On Wed, Aug 1, 2018 at 7:32 AM, 'Dan Smith' dan.smith33220@
....
wrote:
>
> Answers to your questions:
>
>
>
> Stock Market Functions add-in, Version 2.1.2018..01.24 (C:\SMF Add-In;
> Windows (32-bit) NT :.00; 15.0; ; ; 1)
>
> 1. After performing the steps in my initial post, I get: Stock
> Market Functions add-in, Version 2.1.2018.01.24 (C:\SMF Add-In; Windows
> (32-bit) NT :.00; 15.0; ; ; 1)
>
> 2. #NAME?
>
> 3. a. The formula is ='C:\SMF Add-In\RCH_Stock_Market_
> Functions.xla'!smfPricesByDates(AB$2,$Z3) if I choose DON'T UPDATE when
> the file opens
>
> b. The formula is =smfPricesByDates(AB$2,$Z3) if I DO UPDATE when the file
> opens and perform the steps indicated in my first post (in which case, the
> formulas work)
>
>
>
> As additional information, the fix links macro is NOT available for 2 and
> 3a above.
>
>
>
> *From:* smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
> *Sent:* Wednesday, August 1, 2018 2:40 AM
> *To:* smf_addin@yahoogroups.com
> *Subject:* Re: [smf_addin] The dreaded #NAME?
>
>
>
>
>
> It can't be enabling macros. When you install an add-in, you've already
> given it permission. It doesn't need any more.
>
>
>
> Three things:
>
>
>
> 1. What do you get with:
>
>
>
> =RCHGetElementNumbe
>
>
>
> 2. Exit EXCEL, open EXCEL with an empty worksheet, and try that function
> again. Do you get the same thing?
>
>
>
> 3. On the worksheet you're having troubles with, what does the formula in
> one of the #NAME? cells look like? ,
>
>
>
>
>
Wed Aug 1, 2018 4:05 pm (PDT) . Posted by:
"Dan Smith" dan.smith33220@gmail.com
Add-in was active. It turned out to be the Microsoft Security issue you referenced. I unblocked the xla file and everything appears to be fine now.
Thanks for all of the help and explanation.
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Wednesday, August 1, 2018 6:26 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] The dreaded #NAME?
Because of (2) -- getting #NAME? when you first start up EXCEL -- I would assume that add-in is not activated. The macro not being available pretty much confirms that. When you resolve the links through EXCEL, going from (3a) to (3b), you're using the add-in simply as links, not as an add-in. Which can cause different issues in different versions of EXCEL.
So, make sure the add-in is activated in the add-in manager. (keyboard shortcut alt+t+i).
You may have the issue that first occurred in July of 2016 because MicroSoft issued some security updates, which prevented EXCEL from loading the add-in when EXCEL starts up. For a workaround suggested by Microsoft, until they supposedly fix the issue, see:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/30568
If the add-in isn't be loaded by EXCEL at startup, you would need to resolve links every time you open a workbook. Some people were doing a workaround of manually activating the add-in every time they opened EXCEL. But the Microsoft workaround above should fix the problem, if that's the problem you have?
On Wed, Aug 1, 2018 at 7:32 AM, 'Dan Smith' dan.smith33220@
....
wrote:
Answers to your questions:
Stock Market Functions add-in, Version 2.1.2018..01.24 (C:\SMF Add-In; Windows (32-bit) NT :.00; 15.0; ; ; 1)
1. After performing the steps in my initial post, I get: Stock Market Functions add-in, Version 2.1.2018.01.24 (C:\SMF Add-In; Windows (32-bit) NT :.00; 15.0; ; ; 1)
2. #NAME?
3. a. The formula is ='C:\SMF Add-In\RCH_Stock_Market_Functions.xla'!smfPricesByDates(AB$2,$Z3) if I choose DON'T UPDATE when the file opens
b. The formula is =smfPricesByDates(AB$2,$Z3) if I DO UPDATE when the file opens and perform the steps indicated in my first post (in which case, the formulas work)
As additional information, the fix links macro is NOT available for 2 and 3a above.
From: smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com> [mailto:smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com> ]
Sent: Wednesday, August 1, 2018 2:40 AM
To: smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com>
Subject: Re: [smf_addin] The dreaded #NAME?
It can't be enabling macros. When you install an add-in, you've already given it permission. It doesn't need any more.
Three things:
1. What do you get with:
=RCHGetElementNumber("Version")
2. Exit EXCEL, open EXCEL with an empty worksheet, and try that function again. Do you get the same thing?
3. On the worksheet you're having troubles with, what does the formula in one of the #NAME? cells look like? ,
Thanks for all of the help and explanation.
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Wednesday, August 1, 2018 6:26 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] The dreaded #NAME?
Because of (2) -- getting #NAME? when you first start up EXCEL -- I would assume that add-in is not activated. The macro not being available pretty much confirms that. When you resolve the links through EXCEL, going from (3a) to (3b), you're using the add-in simply as links, not as an add-in. Which can cause different issues in different versions of EXCEL.
So, make sure the add-in is activated in the add-in manager. (keyboard shortcut alt+t+i).
You may have the issue that first occurred in July of 2016 because MicroSoft issued some security updates, which prevented EXCEL from loading the add-in when EXCEL starts up. For a workaround suggested by Microsoft, until they supposedly fix the issue, see:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/30568
If the add-in isn't be loaded by EXCEL at startup, you would need to resolve links every time you open a workbook. Some people were doing a workaround of manually activating the add-in every time they opened EXCEL. But the Microsoft workaround above should fix the problem, if that's the problem you have?
On Wed, Aug 1, 2018 at 7:32 AM, 'Dan Smith' dan.smith33220@
....
wrote:
Answers to your questions:
Stock Market Functions add-in, Version 2.1.2018..01.24 (C:\SMF Add-In; Windows (32-bit) NT :.00; 15.0; ; ; 1)
1. After performing the steps in my initial post, I get: Stock Market Functions add-in, Version 2.1.2018.01.24 (C:\SMF Add-In; Windows (32-bit) NT :.00; 15.0; ; ; 1)
2. #NAME?
3. a. The formula is ='C:\SMF Add-In\RCH_Stock_Market_Functions.xla'!smfPricesByDates(AB$2,$Z3) if I choose DON'T UPDATE when the file opens
b. The formula is =smfPricesByDates(AB$2,$Z3) if I DO UPDATE when the file opens and perform the steps indicated in my first post (in which case, the formulas work)
As additional information, the fix links macro is NOT available for 2 and 3a above.
From: smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com> [mailto:smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com> ]
Sent: Wednesday, August 1, 2018 2:40 AM
To: smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com>
Subject: Re: [smf_addin] The dreaded #NAME?
It can't be enabling macros. When you install an add-in, you've already given it permission. It doesn't need any more.
Three things:
1. What do you get with:
=RCHGetElementNumbe
2. Exit EXCEL, open EXCEL with an empty worksheet, and try that function again. Do you get the same thing?
3. On the worksheet you're having troubles with, what does the formula in one of the #NAME? cells look like? ,
Wed Aug 1, 2018 4:07 pm (PDT) . Posted by:
"Sundeep Chadha" sundeep_chadha
Hi Randy -
This example file downloads some key statistics data from yahoo directly into excel -> RCHGetElementNumber-Template-Yahoo-Key-Statistics.xls
What I would like is to have a similar template that will download the financial information (both quarterly and annual) from yahoo. You don't need to build it. I will built it and share it with the community. However in order to do that I need to know the element number for financial data - like this 952 stands for profit margin ttm
=RCHGetElementNumber(Ticker,952)
How do I know (I can dig if you tell me how to) what would be the element number for say - Goodwill or Retained Earnings?
It goes without saying - THANK YOU for all that you do.
Thanks and regards, Sundeep Chadha
This example file downloads some key statistics data from yahoo directly into excel -> RCHGetElementN
What I would like is to have a similar template that will download the financial information (both quarterly and annual) from yahoo. You don't need to build it. I will built it and share it with the community. However in order to do that I need to know the element number for financial data - like this 952 stands for profit margin ttm
=RCHGetElementNumbe
How do I know (I can dig if you tell me how to) what would be the element number for say - Goodwill or Retained Earnings?
It goes without saying - THANK YOU for all that you do.
Thanks and regards, Sundeep Chadha
Wed Aug 1, 2018 4:29 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
All of the elements I've defined are listed in
the RCHGetElementNumber-Element-Definitions.xls workbook. I never defined
all of the Yahoo financial statements. I stopped when I found out they
"pigeon holed" their data incorrectly for some of the analysis I was doing.
For example, they put "Restricted Cash" into "Cash & Equivalents". I don't
know if they've fixed that or not. But, for example, at that time, you
couldn't get to the "Cash" amount listed on the Key Statistics page, using
their own financial statements data. Also, since they had so few annual and
quarterly periods, it just didn't seem worth the effort. So I never went
beyond a few data items from the balance sheet.
Now, the best way would be to use the data in the JSON files. However:
Have you looked at the templates for GuruFocus or AdvFN or Morningstar?
On Wed, Aug 1, 2018 at 4:07 PM, Sundeep Chadha sundeep_chadha@
....
wrote:
>
> This example file downloads some key statistics data from yahoo directly
> into excel -> RCHGetElementNumber-Template-Yahoo-Key-Statistics.xls
> <http://ogres-crypt.com/SMF/Templates/RCHGetElementNumber-Template-Yahoo-Key-Statistics.xls>
>
> What I would like is to have a similar template that will download the
> financial information (both quarterly and annual) from yahoo. You don't
> need to build it. I will built it and share it with the community.
> However in order to do that I need to know the element number for
> financial data - like this 952 stands for profit margin ttm
> =RCHGetElementNumber(Ticker,952)
>
> How do I know (I can dig if you tell me how to) what would be the element
> number for say - Goodwill or Retained Earnings?
>
> It goes without saying - THANK YOU for all that you do.
>
>
>
the RCHGetElementNumber-Element-Definitions.xls workbook. I never defined
all of the Yahoo financial statements. I stopped when I found out they
"pigeon holed" their data incorrectly for some of the analysis I was doing.
For example, they put "Restricted Cash" into "Cash & Equivalents". I don't
know if they've fixed that or not. But, for example, at that time, you
couldn't get to the "Cash" amount listed on the Key Statistics page, using
their own financial statements data. Also, since they had so few annual and
quarterly periods, it just didn't seem worth the effort. So I never went
beyond a few data items from the balance sheet.
Now, the best way would be to use the data in the JSON files. However:
Have you looked at the templates for GuruFocus or AdvFN or Morningstar?
On Wed, Aug 1, 2018 at 4:07 PM, Sundeep Chadha sundeep_chadha@
....
wrote:
>
> This example file downloads some key statistics data from yahoo directly
> into excel -> RCHGetElementNumber-Template-Yahoo-Key-Statistics.xls
> <http://ogres-crypt.com/SMF/Templates/RCHGetElementNumber-Template-Yahoo-Key-Statistics.xls>
>
> What I would like is to have a similar template that will download the
> financial information (both quarterly and annual) from yahoo. You don't
> need to build it. I will built it and share it with the community.
> However in order to do that I need to know the element number for
> financial data - like this 952 stands for profit margin ttm
> =RCHGetElementNumbe
>
> How do I know (I can dig if you tell me how to) what would be the element
> number for say - Goodwill or Retained Earnings?
>
> It goes without saying - THANK YOU for all that you do.
>
>
>
Wed Aug 1, 2018 7:33 pm (PDT) . Posted by:
"Sundeep Chadha" sundeep_chadha
Thank you for replying promptly.
A Question:
1. You wrote "Now, the best way would be to use the data in the JSON files" <- Can you please elaborate. I do not understand what you mean here
I will also try to look at Gurufocus and Morningstar. However my experience has been that there is NO perfect data. Everybody has their own "way" of calculating some fields and there is always something wrong....some more than other or less than others.
Thanks and regards, Sundeep Chadha
On Wednesday, August 1, 2018, 6:30:02 PM CDT, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
All of the elements I've defined are listed in the RCHGetElementNumber-Element-Definitions.xls workbook. I never defined all of the Yahoo financial statements. I stopped when I found out they "pigeon holed" their data incorrectly for some of the analysis I was doing.. For example, they put "Restricted Cash" into "Cash & Equivalents". I don't know if they've fixed that or not. But, for example, at that time, you couldn't get to the "Cash" amount listed on the Key Statistics page, using their own financial statements data. Also, since they had so few annual and quarterly periods, it just didn't seem worth the effort. So I never went beyond a few data items from the balance sheet.
Now, the best way would be to use the data in the JSON files. However:
Have you looked at the templates for GuruFocus or AdvFN or Morningstar?
On Wed, Aug 1, 2018 at 4:07 PM, Sundeep Chadha sundeep_chadha@... wrote:
This example file downloads some key statistics data from yahoo directly into excel -> RCHGetElementNumber- Template-Yahoo-Key-Statistics. xls
What I would like is to have a similar template that will download the financial information (both quarterly and annual) from yahoo. You don't need to build it. I will built it and share it with the community. However in order to do that I need to know the element number for financial data - like this 952 stands for profit margin ttm
=RCHGetElementNumber(Ticker, 952)
How do I know (I can dig if you tell me how to) what would be the element number for say - Goodwill or Retained Earnings?
It goes without saying - THANK YOU for all that you do.
#yiv8858483911 #yiv8858483911 -- #yiv8858483911ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv8858483911 #yiv8858483911ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv8858483911 #yiv8858483911ygrp-mkp #yiv8858483911hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv8858483911 #yiv8858483911ygrp-mkp #yiv8858483911ads {margin-bottom:10px;}#yiv8858483911 #yiv8858483911ygrp-mkp .yiv8858483911ad {padding:0 0;}#yiv8858483911 #yiv8858483911ygrp-mkp .yiv8858483911ad p {margin:0;}#yiv8858483911 #yiv8858483911ygrp-mkp .yiv8858483911ad a {color:#0000ff;text-decoration:none;}#yiv8858483911 #yiv8858483911ygrp-sponsor #yiv8858483911ygrp-lc {font-family:Arial;}#yiv8858483911 #yiv8858483911ygrp-sponsor #yiv8858483911ygrp-lc #yiv8858483911hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv8858483911 #yiv8858483911ygrp-sponsor #yiv8858483911ygrp-lc .yiv8858483911ad {margin-bottom:10px;padding:0 0;}#yiv8858483911 #yiv8858483911actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv8858483911 #yiv8858483911activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv8858483911 #yiv8858483911activity span {font-weight:700;}#yiv8858483911 #yiv8858483911activity span:first-child {text-transform:uppercase;}#yiv8858483911 #yiv8858483911activity span a {color:#5085b6;text-decoration:none;}#yiv8858483911 #yiv8858483911activity span span {color:#ff7900;}#yiv8858483911 #yiv8858483911activity span .yiv8858483911underline {text-decoration:underline;}#yiv8858483911 .yiv8858483911attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv8858483911 .yiv8858483911attach div a {text-decoration:none;}#yiv8858483911 .yiv8858483911attach img {border:none;padding-right:5px;}#yiv8858483911 .yiv8858483911attach label {display:block;margin-bottom:5px;}#yiv8858483911 .yiv8858483911attach label a {text-decoration:none;}#yiv8858483911 blockquote {margin:0 0 0 4px;}#yiv8858483911 .yiv8858483911bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv8858483911 .yiv8858483911bold a {text-decoration:none;}#yiv8858483911 dd.yiv8858483911last p a {font-family:Verdana;font-weight:700;}#yiv8858483911 dd.yiv8858483911last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv8858483911 dd.yiv8858483911last p span.yiv8858483911yshortcuts {margin-right:0;}#yiv8858483911 div.yiv8858483911attach-table div div a {text-decoration:none;}#yiv8858483911 div.yiv8858483911attach-table {width:400px;}#yiv8858483911 div.yiv8858483911file-title a, #yiv8858483911 div.yiv8858483911file-title a:active, #yiv8858483911 div.yiv8858483911file-title a:hover, #yiv8858483911 div.yiv8858483911file-title a:visited {text-decoration:none;}#yiv8858483911 div.yiv8858483911photo-title a, #yiv8858483911 div.yiv8858483911photo-title a:active, #yiv8858483911 div.yiv8858483911photo-title a:hover, #yiv8858483911 div.yiv8858483911photo-title a:visited {text-decoration:none;}#yiv8858483911 div#yiv8858483911ygrp-mlmsg #yiv8858483911ygrp-msg p a span.yiv8858483911yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv8858483911 .yiv8858483911green {color:#628c2a;}#yiv8858483911 .yiv8858483911MsoNormal {margin:0 0 0 0;}#yiv8858483911 o {font-size:0;}#yiv8858483911 #yiv8858483911photos div {float:left;width:72px;}#yiv8858483911 #yiv8858483911photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv8858483911 #yiv8858483911photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv8858483911 #yiv8858483911reco-category {font-size:77%;}#yiv8858483911 #yiv8858483911reco-desc {font-size:77%;}#yiv8858483911 .yiv8858483911replbq {margin:4px;}#yiv8858483911 #yiv8858483911ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv8858483911 #yiv8858483911ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv8858483911 #yiv8858483911ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv8858483911 #yiv8858483911ygrp-mlmsg select, #yiv8858483911 input, #yiv8858483911 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv8858483911 #yiv8858483911ygrp-mlmsg pre, #yiv8858483911 code {font:115% monospace;}#yiv8858483911 #yiv8858483911ygrp-mlmsg * {line-height:1.22em;}#yiv8858483911 #yiv8858483911ygrp-mlmsg #yiv8858483911logo {padding-bottom:10px;}#yiv8858483911 #yiv8858483911ygrp-msg p a {font-family:Verdana;}#yiv8858483911 #yiv8858483911ygrp-msg p#yiv8858483911attach-count span {color:#1E66AE;font-weight:700;}#yiv8858483911 #yiv8858483911ygrp-reco #yiv8858483911reco-head {color:#ff7900;font-weight:700;}#yiv8858483911 #yiv8858483911ygrp-reco {margin-bottom:20px;padding:0px;}#yiv8858483911 #yiv8858483911ygrp-sponsor #yiv8858483911ov li a {font-size:130%;text-decoration:none;}#yiv8858483911 #yiv8858483911ygrp-sponsor #yiv8858483911ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv8858483911 #yiv8858483911ygrp-sponsor #yiv8858483911ov ul {margin:0;padding:0 0 0 8px;}#yiv8858483911 #yiv8858483911ygrp-text {font-family:Georgia;}#yiv8858483911 #yiv8858483911ygrp-text p {margin:0 0 1em 0;}#yiv8858483911 #yiv8858483911ygrp-text tt {font-size:120%;}#yiv8858483911 #yiv8858483911ygrp-vital ul li:last-child {border-right:none !important;}#yiv8858483911
A Question:
1. You wrote "Now, the best way would be to use the data in the JSON files" <- Can you please elaborate. I do not understand what you mean here
I will also try to look at Gurufocus and Morningstar. However my experience has been that there is NO perfect data. Everybody has their own "way" of calculating some fields and there is always something wrong....some more than other or less than others.
Thanks and regards, Sundeep Chadha
On Wednesday, August 1, 2018, 6:30:02 PM CDT, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
All of the elements I've defined are listed in the RCHGetElementNum
Now, the best way would be to use the data in the JSON files. However:
Have you looked at the templates for GuruFocus or AdvFN or Morningstar?
On Wed, Aug 1, 2018 at 4:07 PM, Sundeep Chadha sundeep_chadha@
This example file downloads some key statistics data from yahoo directly into excel -> RCHGetElementN
What I would like is to have a similar template that will download the financial information (both quarterly and annual) from yahoo. You don't need to build it. I will built it and share it with the community. However in order to do that I need to know the element number for financial data - like this 952 stands for profit margin ttm
=RCHGetElementNumbe
How do I know (I can dig if you tell me how to) what would be the element number for say - Goodwill or Retained Earnings?
It goes without saying - THANK YOU for all that you do.
#yiv8858483911 #yiv8858483911 -- #yiv8858483911ygrp-
Wed Aug 1, 2018 8:06 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
As a JSON example, you asked about goodwill -- these would retrieve that
four annual amounts from Yahoo:
=smfGetYahooJSONField("MMM","balanceSheetHistory",
"quoteSummary.result.0.balanceSheetHistory.balanceSheetStatements.0.goodWill.raw")
=smfGetYahooJSONField("MMM","balanceSheetHistory",
"quoteSummary.result.0.balanceSheetHistory.balanceSheetStatements.1.goodWill.raw")
=smfGetYahooJSONField("MMM","balanceSheetHistory",
"quoteSummary.result.0.balanceSheetHistory.balanceSheetStatements.2.goodWill.raw")
=smfGetYahooJSONField("MMM","balanceSheetHistory",
"quoteSummary.result.0.balanceSheetHistory.balanceSheetStatements.3.goodWill.raw")
That's ticker symbol, Yahoo module, and JSON field name. I've tried to
document the available modules and field names in workbooks found here:
http://ogres-crypt.com/SMF/Elements/
I'm just not sure what I want to do with them. I'm thinking the best option
is to parse the modules into a range that would look like the CSV file
delivered from Morningstar, with everything in fixed locations, because
using the formulas above to extract a large number of values would be
relatively slow. Or, just convert it sequentially into a CSV file for
smfGetCSVFile(). As a GuruFocus subscriber, I can easily download a CSV
file with 40 years of annual and quarterly financial statements data for
any given company. That's another option.
A better method might be using Python or PHP. They can "unload" JSON files
into a usable format quickly and easily.
One concern I have is people attempting to build their own databases and
doing a large number of retrievals from Yahoo, encouraging them to move the
JSON files behind some type of protection, similar to what Barchart just
did. But that's an issue I've had concerns over ever since I started
sharing the add-in. I might still have access to Barchart data if I hadn't
released add-in functions that accessed their data. :(
On Wed, Aug 1, 2018 at 7:33 PM, Sundeep Chadha sundeep_chadha@yahoo.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
> Thank you for replying promptly.
>
> A Question:
>
> 1. You wrote "Now, the best way would be to use the data in the JSON
> files" <- Can you please elaborate. I do not understand what you mean here
>
> I will also try to look at Gurufocus and Morningstar. However my
> experience has been that there is NO perfect data.
> Everybody has their own "way" of calculating some fields and there is
> always something wrong...some more than other or less than others.
>
>
>
four annual amounts from Yahoo:
=smfGetYahooJSONField("MMM","balanceSheetHistory",
"quoteSummary.result.0.balanceSheetHistory.balanceSheetStatements.0.goodWill.raw")
=smfGetYahooJSONField("MMM","balanceSheetHistory",
"quoteSummary.result.0.balanceSheetHistory.balanceSheetStatements.1.goodWill.raw")
=smfGetYahooJSONField("MMM","balanceSheetHistory",
"quoteSummary.result.0.balanceSheetHistory.balanceSheetStatements.2.goodWill.raw")
=smfGetYahooJSONField("MMM","balanceSheetHistory",
"quoteSummary.result.0.balanceSheetHistory.balanceSheetStatements.3.goodWill.raw")
That's ticker symbol, Yahoo module, and JSON field name. I've tried to
document the available modules and field names in workbooks found here:
http://ogres-crypt.com/SMF/Elements/
I'm just not sure what I want to do with them. I'm thinking the best option
is to parse the modules into a range that would look like the CSV file
delivered from Morningstar, with everything in fixed locations, because
using the formulas above to extract a large number of values would be
relatively slow. Or, just convert it sequentially into a CSV file for
smfGetCSVFile(). As a GuruFocus subscriber, I can easily download a CSV
file with 40 years of annual and quarterly financial statements data for
any given company. That's another option.
A better method might be using Python or PHP. They can "unload" JSON files
into a usable format quickly and easily.
One concern I have is people attempting to build their own databases and
doing a large number of retrievals from Yahoo, encouraging them to move the
JSON files behind some type of protection, similar to what Barchart just
did. But that's an issue I've had concerns over ever since I started
sharing the add-in. I might still have access to Barchart data if I hadn't
released add-in functions that accessed their data. :(
On Wed, Aug 1, 2018 at 7:33 PM, Sundeep Chadha sundeep_chadha@yahoo.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
> Thank you for replying promptly.
>
> A Question:
>
> 1. You wrote "Now, the best way would be to use the data in the JSON
> files" <- Can you please elaborate. I do not understand what you mean here
>
> I will also try to look at Gurufocus and Morningstar. However my
> experience has been that there is NO perfect data.
> Everybody has their own "way" of calculating some fields and there is
> always something wrong...some more than other or less than others.
>
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
