15 New Messages
Digest #2842
Messages
Tue Nov 5, 2013 1:42 pm (PST) . Posted by:
rharmelink
I've updated the following files to the "Works in Progress" folder:
@Change-log.html -- Shows a log of changes to the add-in since the last official release
RCHGetElementNumber-Element-Definitions.xls -- The current documentation of element definitions, including the many recent changes. I've added a "Usage" column to indicate if an element is for Stocks ("S"), ETFs ("E"), and/or Mutual Funds ("F").
smf-elements-8.txt -- Contains the updates of the earnings.com earnings announcement element definitions, #1286 thru #1400, with element definitions from Zacks. I replaced the old "Prev Year Actual" elements from earnings.com with the new "Reported Time" from Zacks. Otherwise, the elements mapped one-to-one. You could even open up the old earnings.com template to see the data for the new element definitions.
SMF-Template-Zacks-Earnings-Annoucements.xls -- A new template, to display elements #1286 thru #1400 in context. To install the updates to the element definitions, replace the smf-elements-8.txt in your SMF add-in folder with this new file. Then, you'll either need to exit and restart EXCEL, or run the smfForceRecalculation macro, in order for the add-in to load the new definitions.
One thing that does concern me about the Zacks earnings announcement table is that the older quarters have an "Estimated EPS", but not a "Reported EPS". That seem a little strange to me.
These are not trivial extractions, since the data was extracted from JavaScript code. For example, the extraction formula for element #1287 looks something like:
=smfConvertData(smfstrExtr(smfstrExtr(smfWord(smfWord(","&smfstrExtr(smfGetTagContent("http://www.zacks.com/stock/research/IBM/earnings-announcements","script",-1,"Period Ending"),"""data"" : [ ","]"),1,"}"),4,","),": ","~"),"""",""""))
But once I got the pattern down, it went fairly quickly. :)
@Change-log.
RCHGetElementNumber
smf-elements-
SMF-Template-
One thing that does concern me about the Zacks earnings announcement table is that the older quarters have an "Estimated EPS", but not a "Reported EPS". That seem a little strange to me.
These are not trivial extractions, since the data was extracted from JavaScript code. For example, the extraction formula for element #1287 looks something like:
=smfConvertData(
But once I got the pattern down, it went fairly quickly. :)
Tue Nov 5, 2013 3:46 pm (PST) . Posted by:
"Thomas Schumacher" mrtomschu
Hi Randy,
Thanks for all you do.
If a group of people wanted to share spreadsheets, could we place the
SMF-Addin files at some public location like DropBox.com so that Excel
would look there instead of our individual computers. Or is there a better
way. Not many in our group are power users of Excel, or computers for that
matter. They would like to use my templates, but do not have the knowledge
to maintain the SMF files.
Tom Schumacher.
On Tue, Nov 5, 2013 at 3:42 PM, <rharmelink@gmail.com> wrote:
>
>
> I've updated the following files to the "Works in Progress" folder:
>
> - @Change-log.html -- Shows a log of changes to the add-in since the
> last official release
>
> - RCHGetElementNumber-Element-Definitions.xls -- The current
> documentation of element definitions, including the many recent changes.
> I've added a "Usage" column to indicate if an element is for Stocks ("S"),
> ETFs ("E"), and/or Mutual Funds ("F").
>
> - smf-elements-8.txt -- Contains the updates of the earnings.comearnings announcement element definitions, #1286 thru #1400, with element
> definitions from Zacks. I replaced the old "Prev Year Actual" elements from
> earnings.com with the new "Reported Time" from Zacks. Otherwise, the
> elements mapped one-to-one. You could even open up the old earnings.comtemplate to see the data for the new element definitions.
>
> - SMF-Template-Zacks-Earnings-Annoucements.xls -- A new template, to
> display elements #1286 thru #1400 in context.
>
> To install the updates to the element definitions, replace the
> smf-elements-8.txt in your SMF add-in folder with this new file. Then,
> you'll either need to exit and restart EXCEL, or run the
> smfForceRecalculation macro, in order for the add-in to load the new
> definitions.
>
> One thing that does concern me about the Zacks earnings announcement table
> is that the older quarters have an "Estimated EPS", but not a "Reported
> EPS". That seem a little strange to me.
>
> These are not trivial extractions, since the data was extracted from
> JavaScript code. For example, the extraction formula for element #1287
> looks something like:
>
>
> =smfConvertData(smfstrExtr(smfstrExtr(smfWord(smfWord(","&smfstrExtr(smfGetTagContent("
> http://www.zacks.com/stock/research/IBM/earnings-announcements","script",-1,"Period
> Ending"),"""data"" : [ ","]"),1,"}"),4,","),": ","~"),"""",""""))
>
> But once I got the pattern down, it went fairly quickly. :)
>
>
>
Thanks for all you do.
If a group of people wanted to share spreadsheets, could we place the
SMF-Addin files at some public location like DropBox.com so that Excel
would look there instead of our individual computers. Or is there a better
way. Not many in our group are power users of Excel, or computers for that
matter. They would like to use my templates, but do not have the knowledge
to maintain the SMF files.
Tom Schumacher.
On Tue, Nov 5, 2013 at 3:42 PM, <rharmelink@gmail.
>
>
> I've updated the following files to the "Works in Progress" folder:
>
> - @Change-log.
> last official release
>
> - RCHGetElementNumber
> documentation of element definitions, including the many recent changes.
> I've added a "Usage" column to indicate if an element is for Stocks ("S"
> ETFs ("E"
>
> - smf-elements-
> definitions from Zacks. I replaced the old "Prev Year Actual" elements from
> earnings.com with the new "Reported Time" from Zacks. Otherwise, the
> elements mapped one-to-one. You could even open up the old earnings.comtemplat
>
> - SMF-Template-
> display elements #1286 thru #1400 in context.
>
> To install the updates to the element definitions, replace the
> smf-elements-
> you'll either need to exit and restart EXCEL, or run the
> smfForceRecalculati
> definitions.
>
> One thing that does concern me about the Zacks earnings announcement table
> is that the older quarters have an "Estimated EPS", but not a "Reported
> EPS". That seem a little strange to me.
>
> These are not trivial extractions, since the data was extracted from
> JavaScript code. For example, the extraction formula for element #1287
> looks something like:
>
>
> =smfConvertData(
> http://www.zacks.
> Ending"
>
> But once I got the pattern down, it went fairly quickly. :)
>
>
>
Tue Nov 5, 2013 3:48 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
I think someone tried an Internet source, but EXCEL wouldn't resolve the
links to there.
On Tue, Nov 5, 2013 at 4:46 PM, Thomas Schumacher <mrtomschu@gmail.com>wrote:
>
> Hi Randy,
> Thanks for all you do.
> If a group of people wanted to share spreadsheets, could we place the
> SMF-Addin files at some public location like DropBox.com so that Excel
> would look there instead of our individual computers. Or is there a better
> way. Not many in our group are power users of Excel, or computers for that
> matter. They would like to use my templates, but do not have the knowledge
> to maintain the SMF files.
> Tom Schumacher.
>
links to there.
On Tue, Nov 5, 2013 at 4:46 PM, Thomas Schumacher <mrtomschu@gmail.
>
> Hi Randy,
> Thanks for all you do.
> If a group of people wanted to share spreadsheets, could we place the
> SMF-Addin files at some public location like DropBox.com so that Excel
> would look there instead of our individual computers. Or is there a better
> way. Not many in our group are power users of Excel, or computers for that
> matter. They would like to use my templates, but do not have the knowledge
> to maintain the SMF files.
> Tom Schumacher.
>
Tue Nov 5, 2013 5:18 pm (PST) . Posted by:
"Sheila O'Grady"
Google drive, maybe? Would need to somehow give invites to those here.
Sent from my iPad
> On Nov 5, 2013, at 18:48, Randy Harmelink <rharmelink@gmail.com> wrote:
>
> I think someone tried an Internet source, but EXCEL wouldn't resolve the links to there.
>
>> On Tue, Nov 5, 2013 at 4:46 PM, Thomas Schumacher <mrtomschu@gmail.com> wrote:
>>
>> Hi Randy,
>> Thanks for all you do.
>> If a group of people wanted to share spreadsheets, could we place the SMF-Addin files at some public location like DropBox.com so that Excel would look there instead of our individual computers. Or is there a better way. Not many in our group are power users of Excel, or computers for that matter. They would like to use my templates, but do not have the knowledge to maintain the SMF files.
>> Tom Schumacher.
>
>
Sent from my iPad
> On Nov 5, 2013, at 18:48, Randy Harmelink <rharmelink@gmail.
>
> I think someone tried an Internet source, but EXCEL wouldn't resolve the links to there.
>
>> On Tue, Nov 5, 2013 at 4:46 PM, Thomas Schumacher <mrtomschu@gmail.
>>
>> Hi Randy,
>> Thanks for all you do.
>> If a group of people wanted to share spreadsheets, could we place the SMF-Addin files at some public location like DropBox.com so that Excel would look there instead of our individual computers. Or is there a better way. Not many in our group are power users of Excel, or computers for that matter. They would like to use my templates, but do not have the knowledge to maintain the SMF files.
>> Tom Schumacher.
>
>
Tue Nov 5, 2013 1:43 pm (PST) . Posted by:
mrmonopoly88
Good day,
My spreadsheet(keystatistics-spritelight) is uploaded in the files section.
The spreadsheet is calculating alot of ratios and statistiscs correctly, however I sometimes use alternative routes (other websites) to get my data, and I doubt they will last forever.
I am in need of codes for following paramaters:
CAPE-ratio
Long Term Debt
current liabilities
current assets
Free Cash Flow
I get alot of errors lately on the ADvFN sources, any solution for that?
Greets,
Dennis
My spreadsheet(
The spreadsheet is calculating alot of ratios and statistiscs correctly, however I sometimes use alternative routes (other websites) to get my data, and I doubt they will last forever.
I am in need of codes for following paramaters:
CAPE-ratio
Long Term Debt
current liabilities
current assets
Free Cash Flow
I get alot of errors lately on the ADvFN sources, any solution for that?
Greets,
Dennis
Tue Nov 5, 2013 1:44 pm (PST) . Posted by:
mrmonopoly88
Good day,
My spreadsheet is uploaded here in attachment
The spreadsheet is calculating alot of ratios and statistiscs correctly, however I sometimes use alternative routes (other websites) to get my data, and I doubt they will last forever.
I am in need of codes for following paramaters:
CAPE-ratio
Long Term Debt
current liabilities
current assets
Free Cash Flow
I get alot of errors lately on the ADvFN sources, any solution for that?
Greets,
Dennis
My spreadsheet is uploaded here in attachment
The spreadsheet is calculating alot of ratios and statistiscs correctly, however I sometimes use alternative routes (other websites) to get my data, and I doubt they will last forever.
I am in need of codes for following paramaters:
CAPE-ratio
Long Term Debt
current liabilities
current assets
Free Cash Flow
I get alot of errors lately on the ADvFN sources, any solution for that?
Greets,
Dennis
Attachments with this message:
1 of 1 File(s)
Tue Nov 5, 2013 2:08 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Problems with the AdvFN site have been the topic of many conversations here
over the past few months. The conclusion can be found here:
http://groups.yahoo.com/neo/groups/smf_addin/conversations/topics/24009
However, it appears the regular website is not being updated promptly, as
they are working on a new "beta" format of their web page presentation. I
won't update the add-in for that until they've put it into place and have
it working properly.
On Tue, Nov 5, 2013 at 2:43 PM, <montegnies.dennis@gmail.com> wrote:
> I get alot of errors lately on the ADvFN sources, any solution for that?
>
over the past few months. The conclusion can be found here:
http://groups.
However, it appears the regular website is not being updated promptly, as
they are working on a new "beta" format of their web page presentation. I
won't update the add-in for that until they've put it into place and have
it working properly.
On Tue, Nov 5, 2013 at 2:43 PM, <montegnies.dennis@
> I get alot of errors lately on the ADvFN sources, any solution for that?
>
Tue Nov 5, 2013 5:04 pm (PST) . Posted by:
dbltapp00
Suddenly I'm getting mostly wrong quotes. Anyone know if something happened at yahoo finance?
Tue Nov 5, 2013 5:48 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Define "wrong quotes". Examples?
If "old", have you updated your Internet Options so that a fresh copy of
the web page is retrieved every time you retrieve the web page?
On Tue, Nov 5, 2013 at 6:04 PM, <dbltapp00@yahoo.com> wrote:
>
> Suddenly I'm getting mostly wrong quotes. Anyone know if something
> happened at yahoo finance?
>
If "old"
the web page is retrieved every time you retrieve the web page?
On Tue, Nov 5, 2013 at 6:04 PM, <dbltapp00@yahoo.
>
> Suddenly I'm getting mostly wrong quotes. Anyone know if something
> happened at yahoo finance?
>
Tue Nov 5, 2013 5:49 pm (PST) . Posted by:
ddbohrer
I noticed the file no longer works with WFC. The revenue is not found. I tried to modify the file but did not understand all the parameters
RCHGetTableCell("http://www.gurufocus.com/financials/"&Ticker,C$4,"except for per share",">"&$B9)
I don't understand what the > sign does
I tried to replace "except for per share" with "Income Statement" to advance the pointer but this did not seem to work. Any ideas
thanks
---In smf_addin@yahoogroups.com, <dan-carroll@...> wrote:
Go to Files > Uploads by Forum Members
http://groups.yahoo.com/neo/groups/smf_addin/files/Uploads%20by%20forum%20members
it's the third file down, named "10 year model - GuruFocus.xlsx http://f1.grp.yahoofs.com/v1/mNIkUsEt8C2lIEfaQ_aVdzHZHO08bX3CHI5ui2J5yaLVhMrcj_FEVEStrN3p-otm0kJBLbf1eT29zG5S1cdeV38B45DcEFw/Uploads%20by%20forum%20members/10%20year%20model%20-%20GuruFocus.xlsx"
--- In smf_addin@yahoogroups.com, <ddbohrer@...> wrote:
Could you point me to the location of the file to download that has the Guru 10 year statements
--- In smf_addin@yahoogroups.com, <dan-carroll@...> wrote:
Thanks for the reminder - was too lazy to get to it before :-)
New version (5) posted.
Removed all those hyperlinks but added a master hyperlink to the source sheet on GuruFocus at the top.
Cleaned up the formatting a bit so that it should print better.
--- In smf_addin@yahoogroups.com, <J_M_Kuehl@...> wrote:
Hi Dan, All the Field descriptions, are Hyperlinked to MMM, If you want to remove the hyper links.
1. Enter 1 into a blank cell,
2. Copy the cell Cntrl-C
3. Highlight all the field Descriptions.
4. Select Edit Special
5. Select the Multiply option and then past.
Thanks for the Sheet
--- In smf_addin@yahoogroups.com, <amt2100@...> wrote:
Hi Dan,
I finally got the bugs worked out with my “location†problems. Thanks for this file. I looks like you’ve learned a lot about using the add-in instructions.
A great spreadsheet.
Thanks again,
Jim
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of dan-carroll@...
Sent: Sunday, September 01, 2013 7:34 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] RE: Fixes to 10 year model - GuruFocus
Version 4 is up.
I changed the dates at the top so that you have a numeric date there now instead of a text string.
Data that is pulled from GuruFocus is taken over with an ISNUMBER check to clean up the sheet.
--- In smf_addin@yahoogroups.com, <dan-carroll@...> wrote:
This string is to isolate fixes needed on the 10 year GuruFocus-based model. I will flag that I am nowhere near in the league of Randy & co, so if VB skills are needed, I'm out. This is just my best effort at producing something to replace the 10 year ADVfn model by Mr. McGinnis, which was so helpful to me.
Formatting changes I can do, but I'd rather prioritize bugs and errors.
I'd also welcome anyone else having a go at building on this. Just please attach a version number and flag the changes on this thread.
One last note: though Gurufocus does standardize the data, not all fields occur for each stock. If the sheet attempts to pull data that isn't there, you will get "Error". In some cases where I've gotten this I've modified the rows to put zeroes if "Error" is returned, but I haven't done it for the whole sheet (too lazy I guess :-)) The data for "Inventory" and "Days Inventory" and "Inventory Turnover" were examples. See the light green blocked data on rows 117/118 of v3 for an example.
I have just uploaded Version 3 of the sheet
Changes made in previous iteration:
· Stock Price in A4 â€ÂÂ" changed to =RCHGetYahooQuotes(Ticker,"l1"), per Randy’s Suggestion
Issues addressed this iteration (v3):
Kermit:
1. Cells O2 â€ÂÂ" S2 return “Error†=RCHGetTableCell("http://www.gurufocus.com/financials/ http://www.gurufocus.com/financials/"&$A$1,O$3,"except for per share",">"&$B2) in my copy A1 thru b3 is a merged cell defined as Ticker
· I went back and did a global change of $A$1 to Ticker. That was an oversight on my part, for which I apologize. I’m not used to producing sheets for others to use, so I tend to skimp on the cleanup and user-friendliness.
· Which ticker are you using? The sheet had some problems with companies that have a short history, which I hoped I had fixed, but maybe not. Let me know the ticker and I’ll see what I error I get and try to come up with a fix.
2. Rows 87 â€ÂÂ" 96 are totally blank except a return “Error†=RCHGetElementNumber(Ticker,1)
· That block should return the “company description†from MSN. It does for some tickers, not for others. You can delete it if you want.
3. Row “N†has no heading, but I noticed starting in Cell N130 thru N136 they are Averages I would enter AVG in Cell N129
· Done. Also put the label in Row 5. The column is mostly there to separate annual from quarterly numbers, but for valuation I thought I’d throw in some averages
4. I was a little confused as to what rows 123 thru 128 were the growth rate of. Seems to me those rows should be right after row 24
· On location, I prefer to get the three financial statements in first. Feel free to move the rows around as you prefer. If a lot of people would prefer that format, let me know and I’ll do it.
· As to what they are the growth rates of, I thought the rows were labeled clearly. They are Y/Y growth rates for Revenues, EBITDA, etc. Let me know if the labeling is confusing.
5. I noticed a later email that you had made a change. I downloaded at 9:55am U.S. EST so I may not have latest level. So might I suggest that you include a version level maybe in cell C4.
· The only change in that version was to the price in A4, as noted above.
Randy:
Cell C135 has a #REF error...
· Fixed â€ÂÂ" my bad.
For me, rows 138 and 139 show up as "######" because the format is too wide for the columns. You can probably drop everything to the right of the decimal point.
· Done. I tend to look at smaller cap stocks, so I left a single decimal place for the income, balance sheet and CF statements.
Other Changes:
I went ahead and moved some of the data from below the 3 major statements around â€ÂÂ" moved margins and growth to just below description block, as I think those are higher priority. I also moved some of the balance sheet measures to the balance sheet block from “Misc†and Capital Efficiency. Also renamed “Capital Efficiency†to “Returnsâ€ÂÂ
As noted at the top, I also modified certain rows where the data is not always there, to replace "Error" messages with zeroes - "Treasury Stock" is an example.
RCHGetTableCell(
I don't understand what the > sign does
I tried to replace "except for per share" with "Income Statement" to advance the pointer but this did not seem to work. Any ideas
thanks
---In smf_addin@yahoogrou
Go to Files > Uploads by Forum Members
http://groups.
it's the third file down, named "10 year model - GuruFocus.xlsx http://f1.grp.
--- In smf_addin@yahoogrou
Could you point me to the location of the file to download that has the Guru 10 year statements
--- In smf_addin@yahoogrou
Thanks for the reminder - was too lazy to get to it before :-)
New version (5) posted.
Removed all those hyperlinks but added a master hyperlink to the source sheet on GuruFocus at the top.
Cleaned up the formatting a bit so that it should print better.
--- In smf_addin@yahoogrou
Hi Dan, All the Field descriptions, are Hyperlinked to MMM, If you want to remove the hyper links.
1. Enter 1 into a blank cell,
2. Copy the cell Cntrl-C
3. Highlight all the field Descriptions.
4. Select Edit Special
5. Select the Multiply option and then past.
Thanks for the Sheet
--- In smf_addin@yahoogrou
Hi Dan,
I finally got the bugs worked out with my “location†problems. Thanks for this file. I looks like you’ve learned a lot about using the add-in instructions.
A great spreadsheet.
Thanks again,
Jim
From: smf_addin@yahoogrou
Sent: Sunday, September 01, 2013 7:34 PM
To: smf_addin@yahoogrou
Subject: [smf_addin] RE: Fixes to 10 year model - GuruFocus
Version 4 is up.
I changed the dates at the top so that you have a numeric date there now instead of a text string.
Data that is pulled from GuruFocus is taken over with an ISNUMBER check to clean up the sheet.
--- In smf_addin@yahoogrou
This string is to isolate fixes needed on the 10 year GuruFocus-based model. I will flag that I am nowhere near in the league of Randy & co, so if VB skills are needed, I'm out. This is just my best effort at producing something to replace the 10 year ADVfn model by Mr. McGinnis, which was so helpful to me.
Formatting changes I can do, but I'd rather prioritize bugs and errors.
I'd also welcome anyone else having a go at building on this. Just please attach a version number and flag the changes on this thread.
One last note: though Gurufocus does standardize the data, not all fields occur for each stock. If the sheet attempts to pull data that isn't there, you will get "Error"
I have just uploaded Version 3 of the sheet
Changes made in previous iteration:
· Stock Price in A4 â€ÂÂ" changed to =RCHGetYahooQuotes(
Issues addressed this iteration (v3):
Kermit:
1. Cells O2 â€ÂÂ" S2 return “Error†=RCHGetTableCell(
· I went back and did a global change of $A$1 to Ticker. That was an oversight on my part, for which I apologize. I’m not used to producing sheets for others to use, so I tend to skimp on the cleanup and user-friendliness.
· Which ticker are you using? The sheet had some problems with companies that have a short history, which I hoped I had fixed, but maybe not. Let me know the ticker and I’ll see what I error I get and try to come up with a fix.
2. Rows 87 â€ÂÂ" 96 are totally blank except a return “Error†=RCHGetElementNumbe
· That block should return the “company description†from MSN. It does for some tickers, not for others. You can delete it if you want.
3. Row “N†has no heading, but I noticed starting in Cell N130 thru N136 they are Averages I would enter AVG in Cell N129
· Done. Also put the label in Row 5. The column is mostly there to separate annual from quarterly numbers, but for valuation I thought I’d throw in some averages
4. I was a little confused as to what rows 123 thru 128 were the growth rate of. Seems to me those rows should be right after row 24
· On location, I prefer to get the three financial statements in first. Feel free to move the rows around as you prefer. If a lot of people would prefer that format, let me know and I’ll do it.
· As to what they are the growth rates of, I thought the rows were labeled clearly. They are Y/Y growth rates for Revenues, EBITDA, etc. Let me know if the labeling is confusing.
5. I noticed a later email that you had made a change. I downloaded at 9:55am U.S. EST so I may not have latest level. So might I suggest that you include a version level maybe in cell C4.
· The only change in that version was to the price in A4, as noted above.
Randy:
Cell C135 has a #REF error...
· Fixed â€ÂÂ" my bad.
For me, rows 138 and 139 show up as "######
· Done. I tend to look at smaller cap stocks, so I left a single decimal place for the income, balance sheet and CF statements.
Other Changes:
I went ahead and moved some of the data from below the 3 major statements around â€ÂÂ" moved margins and growth to just below description block, as I think those are higher priority. I also moved some of the balance sheet measures to the balance sheet block from “Misc†and Capital Efficiency. Also renamed “Capital Efficiency†to “Returnsâ€ÂÂ
As noted at the top, I also modified certain rows where the data is not always there, to replace "Error" messages with zeroes - "Treasury Stock" is an example.
Tue Nov 5, 2013 5:58 pm (PST) . Posted by:
"Jim Ranum" amt2100
Hi all,
Does anyone know how to write the formula to have the cell in Excel give a
zero or an empty entry in the cell instead of an #VALUE result if there is
no data?
I am pulling data from the gurufocus website and taking the results and
using them in formulas. I would like to not have the entire formula crash
just because one part of the data is missing, which is what happens if any
part of the formula has #VALUE in it.
This happens on specific tickers that are less than 10 years old and the
normal number of columns are not present on the Income/Balance sheet.
Thanks for any ideas.
Jim
Does anyone know how to write the formula to have the cell in Excel give a
zero or an empty entry in the cell instead of an #VALUE result if there is
no data?
I am pulling data from the gurufocus website and taking the results and
using them in formulas. I would like to not have the entire formula crash
just because one part of the data is missing, which is what happens if any
part of the formula has #VALUE in it.
This happens on specific tickers that are less than 10 years old and the
normal number of columns are not present on the Income/Balance sheet.
Thanks for any ideas.
Jim
Tue Nov 5, 2013 6:09 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
From 2007 on, to assign a 0 if the "regular formula" has an error:
=IFERROR(....regular formula....,0)
=IFERROR(....regular formula....,"--")
Otherwise:
=IF(ISERR(....regular formula....),0,....regular formula....)
=IF(ISERR(....regular formula....),"--",....regular formula....)
Not as convenient since the "regular formula" has to be entered twice.
On Tue, Nov 5, 2013 at 6:58 PM, Jim Ranum <amt2100@gmail.com> wrote:
>
>
> Does anyone know how to write the formula to have the cell in Excel give a
> zero or an empty entry in the cell instead of an #VALUE result if there is
> no data?
>
>
>
> I am pulling data from the gurufocus website and taking the results and
> using them in formulas. I would like to not have the entire formula crash
> just because one part of the data is missing, which is what happens if any
> part of the formula has #VALUE in it.
>
>
>
> This happens on specific tickers that are less than 10 years old and the
> normal number of columns are not present on the Income/Balance sheet.
>
>
>
=IFERROR(...
=IFERROR(...
Otherwise:
=IF(ISERR(..
=IF(ISERR(..
Not as convenient since the "regular formula" has to be entered twice.
On Tue, Nov 5, 2013 at 6:58 PM, Jim Ranum <amt2100@gmail.
>
>
> Does anyone know how to write the formula to have the cell in Excel give a
> zero or an empty entry in the cell instead of an #VALUE result if there is
> no data?
>
>
>
> I am pulling data from the gurufocus website and taking the results and
> using them in formulas. I would like to not have the entire formula crash
> just because one part of the data is missing, which is what happens if any
> part of the formula has #VALUE in it.
>
>
>
> This happens on specific tickers that are less than 10 years old and the
> normal number of columns are not present on the Income/Balance sheet.
>
>
>
Tue Nov 5, 2013 6:39 pm (PST) . Posted by:
"Jim Ranum" amt2100
Thank you very much Randy.
That was amazingly fast response.
It turns out this problem is a little more complex than I originally thought. I figured out how to modify the formulas so that I could make a simple entry and change where the cells look for the latest year column, but when I run out of years/columns, instead of giving me a null result, I will always get at least one result that is the title of the column. Which isn't exactly an error, but still gives a #VALUE result in the Excel formula involving that cell.
For example ticker: ALN only has 5 years of data, so my 6th column will give a result of "Net Income", because that is the "zero" result. I can't come up with a creative way to avoid that.
=RCHGetTableCell("http://www.gurufocus.com/financials/"&F43,(5-F44),">Fiscal Period",">Net Income<")
Where in this case "5-F44" => 5 is the number for the data column and F44 is the number of years of data missing from the normal 10 years. It works great until I get to the "5-5" year, and then I get "Net Income" for the result, which screws up my formulas.
Any ideas on this one?
Thanks Randy. You provide amazing support.
Jim
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Tuesday, November 05, 2013 9:10 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Solution for a #VALUE return in a formula
From 2007 on, to assign a 0 if the "regular formula" has an error:
=IFERROR(....regular formula....,0)
=IFERROR(....regular formula....,"--")
Otherwise:
=IF(ISERR(....regular formula....),0,....regular formula....)
=IF(ISERR(....regular formula....),"--",....regular formula....)
Not as convenient since the "regular formula" has to be entered twice.
On Tue, Nov 5, 2013 at 6:58 PM, Jim Ranum <amt2100@gmail.com> wrote:
Does anyone know how to write the formula to have the cell in Excel give a zero or an empty entry in the cell instead of an #VALUE result if there is no data?
I am pulling data from the gurufocus website and taking the results and using them in formulas. I would like to not have the entire formula crash just because one part of the data is missing, which is what happens if any part of the formula has #VALUE in it.
This happens on specific tickers that are less than 10 years old and the normal number of columns are not present on the Income/Balance sheet.
That was amazingly fast response.
It turns out this problem is a little more complex than I originally thought. I figured out how to modify the formulas so that I could make a simple entry and change where the cells look for the latest year column, but when I run out of years/columns, instead of giving me a null result, I will always get at least one result that is the title of the column. Which isn't exactly an error, but still gives a #VALUE result in the Excel formula involving that cell.
For example ticker: ALN only has 5 years of data, so my 6th column will give a result of "Net Income", because that is the "zero" result. I can't come up with a creative way to avoid that.
=RCHGetTableCell(
Where in this case "5-F44" => 5 is the number for the data column and F44 is the number of years of data missing from the normal 10 years. It works great until I get to the "5-5" year, and then I get "Net Income" for the result, which screws up my formulas.
Any ideas on this one?
Thanks Randy. You provide amazing support.
Jim
From: smf_addin@yahoogrou
Sent: Tuesday, November 05, 2013 9:10 PM
To: smf_addin@yahoogrou
Subject: Re: [smf_addin] Solution for a #VALUE return in a formula
From 2007 on, to assign a 0 if the "regular formula" has an error:
=IFERROR(...
=IFERROR(...
Otherwise:
=IF(ISERR(..
=IF(ISERR(..
Not as convenient since the "regular formula" has to be entered twice.
On Tue, Nov 5, 2013 at 6:58 PM, Jim Ranum <amt2100@gmail.
Does anyone know how to write the formula to have the cell in Excel give a zero or an empty entry in the cell instead of an #VALUE result if there is no data?
I am pulling data from the gurufocus website and taking the results and using them in formulas. I would like to not have the entire formula crash just because one part of the data is missing, which is what happens if any part of the formula has #VALUE in it.
This happens on specific tickers that are less than 10 years old and the normal number of columns are not present on the Income/Balance sheet.
Tue Nov 5, 2013 7:30 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
You can use negative numbers for "pCells" in the RCHGetTableCell()
function. That tells the function to count backwards from the end of table
row. So, to use examples with less than 10 years, 10 years, and less than 5
quarters:
pCells ALN ALN MMM MMM VJET VJET -1 -- -- -- -- -- -- -2 -- -- -- -- --
-- -3 -- -- -- -- -- -- -4 Jun13 2.5 Sep13 1230 Jun13 -0.52 -5 Mar13 2.52
Jun13 1197 Dec12 -0.15 -6 Dec12 7.49 Mar13 1129 Jun12 0.44 -7 Sep12 5.61
Dec12 991 TTM -0.66 -8 Jun12 3.33 Sep12 1161 Dec12 0.29 -9 TTM 18.12 TTM
4547 Dec11 0.06 -10 Dec12 20.42 Dec12 4444 Dec10 -0.28 -11 Dec11 19.89
Dec11 4283 Fiscal Period -- -12 Dec10 17.84 Dec10 4085 Error -- -13 Dec09
14.41 Dec09 3193 Error -- -14 Dec08 14.7 Dec08 3460 Error -- -15 Dec07
9.75 Dec07 4096 Error -- -16 Dec06 5.93 Dec06 3851 Error -- -17 Dec05 4.22
Dec05 3111 Error -- -18 Fiscal Period -- Dec04 2841 Error -- -19 Error --
Dec03 2403 Error -- -20 Error -- Fiscal Period -- Error -- -21 Error --
Error -- Error --
Note that the most recent fiscal quarter always starts at a -4 for pCells.
And if you look up where the "TTM" lies within the "Fiscal Period"
headings, you can start your fiscal years based on its location. So, just
retrieve the array of fiscal period headers first, look for the "TTM"
header, and you'll know where fiscal quarters end and fiscal years start.
The basic formulas I'm using:
=RCHGetTableCell("http://www.gurufocus.com/financials/"&D$4,$C5,">Fiscal
Period")
=IFERROR(RCHGetTableCell("http://www.gurufocus.com/financials/"&D$4,$C5,">Fiscal
Period",">Net Income<")/1,"--")
The last three table cells of each row are used for some type of
formatting, so no data is in them.
On Tue, Nov 5, 2013 at 7:39 PM, Jim Ranum <amt2100@gmail.com> wrote:
>
>
> Thank you very much Randy.
>
> That was amazingly fast response.
>
>
>
> It turns out this problem is a little more complex than I originally
> thought. I figured out how to modify the formulas so that I could make a
> simple entry and change where the cells look for the latest year column,
> but when I run out of years/columns, instead of giving me a null result, I
> will always get at least one result that is the title of the column. Which
> isn't exactly an error, but still gives a #VALUE result in the Excel
> formula involving that cell.
>
>
>
> For example ticker: ALN only has 5 years of data, so my 6th column will
> give a result of "Net Income", because that is the "zero" result. I can't
> come up with a creative way to avoid that.
>
> =RCHGetTableCell("http://www.gurufocus.com/financials/"&F43,(5-F44),">Fiscal
> Period",">Net Income<")
>
>
>
> Where in this case "5-F44" => 5 is the number for the data column and F44
> is the number of years of data missing from the normal 10 years. It works
> great until I get to the "5-5" year, and then I get "Net Income" for the
> result, which screws up my formulas.
>
>
>
> Any ideas on this one?
>
>
>
> Thanks Randy. You provide amazing support.
>
> Jim
>
>
>
>
>
>
>
> *From:* smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] *On
> Behalf Of *Randy Harmelink
> *Sent:* Tuesday, November 05, 2013 9:10 PM
> *To:* smf_addin@yahoogroups.com
> *Subject:* Re: [smf_addin] Solution for a #VALUE return in a formula
>
>
>
>
>
> From 2007 on, to assign a 0 if the "regular formula" has an error:
>
> =IFERROR(....regular formula....,0)
> =IFERROR(....regular formula....,"--")
>
> Otherwise:
>
> =IF(ISERR(....regular formula....),0,....regular formula....)
> =IF(ISERR(....regular formula....),"--",....regular formula....)
>
> Not as convenient since the "regular formula" has to be entered twice.
>
>
>
> On Tue, Nov 5, 2013 at 6:58 PM, Jim Ranum <amt2100@gmail.com> wrote:
>
>
>
> Does anyone know how to write the formula to have the cell in Excel give a
> zero or an empty entry in the cell instead of an #VALUE result if there is
> no data?
>
>
>
> I am pulling data from the gurufocus website and taking the results and
> using them in formulas. I would like to not have the entire formula crash
> just because one part of the data is missing, which is what happens if any
> part of the formula has #VALUE in it.
>
>
>
> This happens on specific tickers that are less than 10 years old and the
> normal number of columns are not present on the Income/Balance sheet.
>
>
>
>
>
>
>
>
>
function. That tells the function to count backwards from the end of table
row. So, to use examples with less than 10 years, 10 years, and less than 5
quarters:
pCells ALN ALN MMM MMM VJET VJET -1 -- -- -- -- -- -- -2 -- -- -- -- --
-- -3 -- -- -- -- -- -- -4 Jun13 2.5 Sep13 1230 Jun13 -0.52 -5 Mar13 2.52
Jun13 1197 Dec12 -0.15 -6 Dec12 7.49 Mar13 1129 Jun12 0.44 -7 Sep12 5.61
Dec12 991 TTM -0.66 -8 Jun12 3.33 Sep12 1161 Dec12 0.29 -9 TTM 18.12 TTM
4547 Dec11 0.06 -10 Dec12 20.42 Dec12 4444 Dec10 -0.28 -11 Dec11 19.89
Dec11 4283 Fiscal Period -- -12 Dec10 17.84 Dec10 4085 Error -- -13 Dec09
14.41 Dec09 3193 Error -- -14 Dec08 14.7 Dec08 3460 Error -- -15 Dec07
9.75 Dec07 4096 Error -- -16 Dec06 5.93 Dec06 3851 Error -- -17 Dec05 4.22
Dec05 3111 Error -- -18 Fiscal Period -- Dec04 2841 Error -- -19 Error --
Dec03 2403 Error -- -20 Error -- Fiscal Period -- Error -- -21 Error --
Error -- Error --
Note that the most recent fiscal quarter always starts at a -4 for pCells.
And if you look up where the "TTM" lies within the "Fiscal Period"
headings, you can start your fiscal years based on its location. So, just
retrieve the array of fiscal period headers first, look for the "TTM"
header, and you'll know where fiscal quarters end and fiscal years start.
The basic formulas I'm using:
=RCHGetTableCell(
Period"
=IFERROR(RCHGetTabl
Period"
The last three table cells of each row are used for some type of
formatting, so no data is in them.
On Tue, Nov 5, 2013 at 7:39 PM, Jim Ranum <amt2100@gmail.
>
>
> Thank you very much Randy.
>
> That was amazingly fast response.
>
>
>
> It turns out this problem is a little more complex than I originally
> thought. I figured out how to modify the formulas so that I could make a
> simple entry and change where the cells look for the latest year column,
> but when I run out of years/columns, instead of giving me a null result, I
> will always get at least one result that is the title of the column. Which
> isn't exactly an error, but still gives a #VALUE result in the Excel
> formula involving that cell.
>
>
>
> For example ticker: ALN only has 5 years of data, so my 6th column will
> give a result of "Net Income", because that is the "zero" result. I can't
> come up with a creative way to avoid that.
>
> =RCHGetTableCell(
> Period"
>
>
>
> Where in this case "5-F44" => 5 is the number for the data column and F44
> is the number of years of data missing from the normal 10 years. It works
> great until I get to the "5-5" year, and then I get "Net Income" for the
> result, which screws up my formulas.
>
>
>
> Any ideas on this one?
>
>
>
> Thanks Randy. You provide amazing support.
>
> Jim
>
>
>
>
>
>
>
> *From:* smf_addin@yahoogrou
> Behalf Of *Randy Harmelink
> *Sent:* Tuesday, November 05, 2013 9:10 PM
> *To:* smf_addin@yahoogrou
> *Subject:* Re: [smf_addin] Solution for a #VALUE return in a formula
>
>
>
>
>
> From 2007 on, to assign a 0 if the "regular formula" has an error:
>
> =IFERROR(...
> =IFERROR(...
>
> Otherwise:
>
> =IF(ISERR(..
> =IF(ISERR(..
>
> Not as convenient since the "regular formula" has to be entered twice.
>
>
>
> On Tue, Nov 5, 2013 at 6:58 PM, Jim Ranum <amt2100@gmail.
>
>
>
> Does anyone know how to write the formula to have the cell in Excel give a
> zero or an empty entry in the cell instead of an #VALUE result if there is
> no data?
>
>
>
> I am pulling data from the gurufocus website and taking the results and
> using them in formulas. I would like to not have the entire formula crash
> just because one part of the data is missing, which is what happens if any
> part of the formula has #VALUE in it.
>
>
>
> This happens on specific tickers that are less than 10 years old and the
> normal number of columns are not present on the Income/Balance sheet.
>
>
>
>
>
>
>
>
>
Tue Nov 5, 2013 7:18 pm (PST) . Posted by:
"gfoster07k@sbcglobal.net" gfoster07k@sbcglobal.net
Sorry but I still am unable to make this work. I want to add in RCHGetYaHooQuotes. I downloaded to my Download Directory and extracted to Program Files-SMF Add in. That directory now contains:
RCH_Stock_Market_Functions 12/29/2012 Microsoft Add in 462kb
RCHGetElementNumber
smf-elements - 0
smf-elements - 1
through
smf-elements - 9
If I start Excel with a new spreadsheet, got to File - Options - Addin the Add in Manager does not include any RCH add ins to select. Where an I going wrong?
Thanks
RCH_Stock_Market_
RCHGetElementNumber
smf-elements - 0
smf-elements - 1
through
smf-elements - 9
If I start Excel with a new spreadsheet, got to File - Options - Addin the Add in Manager does not include any RCH add ins to select. Where an I going wrong?
Thanks
Tidak ada komentar:
Posting Komentar