15 Messages
Digest #3462
Messages
Mon Jul 20, 2015 2:30 am (PDT) . Posted by:
mail_to_subu
thanks Randy
will try again !!
-----------
regards
Subu
will try again !!
-----------
regards
Subu
Mon Jul 20, 2015 11:56 am (PDT) . Posted by:
trendsekr
Better yet, are there links to each data sources codes?
Thanks
Thanks
Mon Jul 20, 2015 1:11 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
If you are talking about the RCHGetYahooQuotes() function, there is not a
Yahoo code for earnings date.
I have no idea what you mean by "links to each data sources codes"?
On Mon, Jul 20, 2015 at 11:56 AM, trendsekr@... wrote:
>
> Better yet, are there links to each data sources codes?
>
>
Yahoo code for earnings date.
I have no idea what you mean by "links to each data sources codes"?
On Mon, Jul 20, 2015 at 11:56 AM, trendsekr@..
>
> Better yet, are there links to each data sources codes?
>
>
Mon Jul 20, 2015 12:38 pm (PDT) . Posted by:
"Jim Ranum" amt2100
Hi Randy,
I'm sorry I need to revisit an issue that I think we discussed about a year
ago, but I can't find the post now.
I constructed in my formulas a reference to a cell that would allow me to
adjust for the fact that many stocks don't have the standard 15 years of
data on the Financials section of Gurufocus.
Example:
=RCHGetTableCell("http://www.gurufocus.com/financials/"&$F$42,($F66-$F$43),"
>Fiscal Period",">Gross Profit")
Where F42 = the ticker
F66 is 16 and F43 is zero for a normal stock that has 15 years of data
columns. If it doesn't, I just adjust F43 to the proper number and all my
other cells fall into line.
But at one time you mentioned a method to center off of the "TTM" column and
work left-wards. I didn't have time to readjust all the cells in my
spreadsheet at the time, but I am now running into difficulties getting my
data to pull out correctly. For some reason, doing things the way I was is
skipping columns, like it doesn't see them. (I have multiple cells that pull
consecutive years of data, say 3-5 years)
These difficulties cropped up after Guru switched from 10 years to 15 years
of data columns.
Can you please refresh my memory on how to grab the most recent TTM column's
data and the 2014, 2013, etc.? I have a few data points that I analyze 3
and 5 year averages and look at trend.
Thanks very much.
Best regards,
Jim
I'm sorry I need to revisit an issue that I think we discussed about a year
ago, but I can't find the post now.
I constructed in my formulas a reference to a cell that would allow me to
adjust for the fact that many stocks don't have the standard 15 years of
data on the Financials section of Gurufocus.
Example:
=RCHGetTableCell("http://www.gurufocus.com/financials/"&$F$42,
>Fiscal Period"
Where F42 = the ticker
F66 is 16 and F43 is zero for a normal stock that has 15 years of data
columns. If it doesn't, I just adjust F43 to the proper number and all my
other cells fall into line.
But at one time you mentioned a method to center off of the "TTM" column and
work left-wards. I didn't have time to readjust all the cells in my
spreadsheet at the time, but I am now running into difficulties getting my
data to pull out correctly. For some reason, doing things the way I was is
skipping columns, like it doesn't see them. (I have multiple cells that pull
consecutive years of data, say 3-5 years)
These difficulties cropped up after Guru switched from 10 years to 15 years
of data columns.
Can you please refresh my memory on how to grab the most recent TTM column's
data and the 2014, 2013, etc.? I have a few data points that I analyze 3
and 5 year averages and look at trend.
Thanks very much.
Best regards,
Jim
Mon Jul 20, 2015 12:45 pm (PDT) . Posted by:
"Jim Ranum" amt2100
Here's another example of weird results.
DLNG
=RCHGetTableCell("http://www.gurufocus.com/financials/"&$F$42,($F66-$F$43),"
>Fiscal Period",">Revenue per Share")
Again, F66= 16, and in this case with only 5 years of data columns, F43 = 11
I'm getting $23.61 for a result and there is no $23.61 in ANY column.
Best regards,
Jim
From: Jim Ranum [mailto:amt2100@gmail.com]
Sent: Monday, July 20, 2015 3:31 PM
To: smf_addin@yahoogroups.com
Subject: Gurufocus - again - data from TTM column on Financials page
Hi Randy,
I'm sorry I need to revisit an issue that I think we discussed about a year
ago, but I can't find the post now.
I constructed in my formulas a reference to a cell that would allow me to
adjust for the fact that many stocks don't have the standard 15 years of
data on the Financials section of Gurufocus.
Example:
=RCHGetTableCell("http://www.gurufocus.com/financials/"&$F$42,($F66-$F$43),"
>Fiscal Period",">Gross Profit")
Where F42 = the ticker
F66 is 16 and F43 is zero for a normal stock that has 15 years of data
columns. If it doesn't, I just adjust F43 to the proper number and all my
other cells fall into line.
But at one time you mentioned a method to center off of the "TTM" column and
work left-wards. I didn't have time to readjust all the cells in my
spreadsheet at the time, but I am now running into difficulties getting my
data to pull out correctly. For some reason, doing things the way I was is
skipping columns, like it doesn't see them. (I have multiple cells that pull
consecutive years of data, say 3-5 years)
These difficulties cropped up after Guru switched from 10 years to 15 years
of data columns.
Can you please refresh my memory on how to grab the most recent TTM column's
data and the 2014, 2013, etc.? I have a few data points that I analyze 3
and 5 year averages and look at trend.
Thanks very much.
Best regards,
Jim
DLNG
=RCHGetTableCell("http://www.gurufocus.com/financials/"&$F$42,($F66-$F$43),"
>Fiscal Period",">Revenue per Share")
Again, F66= 16, and in this case with only 5 years of data columns, F43 = 11
I'm getting $23.61 for a result and there is no $23.61 in ANY column.
Best regards,
Jim
From: Jim Ranum [mailto:amt2100@gmail.com]
Sent: Monday, July 20, 2015 3:31 PM
To: smf_addin@yahoogroups.com
Subject: Gurufocus - again - data from TTM column on Financials page
Hi Randy,
I'm sorry I need to revisit an issue that I think we discussed about a year
ago, but I can't find the post now.
I constructed in my formulas a reference to a cell that would allow me to
adjust for the fact that many stocks don't have the standard 15 years of
data on the Financials section of Gurufocus.
Example:
=RCHGetTableCell("http://www.gurufocus.com/financials/"&$F$42,
>Fiscal Period"
Where F42 = the ticker
F66 is 16 and F43 is zero for a normal stock that has 15 years of data
columns. If it doesn't, I just adjust F43 to the proper number and all my
other cells fall into line.
But at one time you mentioned a method to center off of the "TTM" column and
work left-wards. I didn't have time to readjust all the cells in my
spreadsheet at the time, but I am now running into difficulties getting my
data to pull out correctly. For some reason, doing things the way I was is
skipping columns, like it doesn't see them. (I have multiple cells that pull
consecutive years of data, say 3-5 years)
These difficulties cropped up after Guru switched from 10 years to 15 years
of data columns.
Can you please refresh my memory on how to grab the most recent TTM column's
data and the 2014, 2013, etc.? I have a few data points that I analyze 3
and 5 year averages and look at trend.
Thanks very much.
Best regards,
Jim
Mon Jul 20, 2015 1:05 pm (PDT) . Posted by:
"Jim Ranum" amt2100
Sorry for multiple emails.
I finally got the correct number, but I don't understand this at all. I had
to use "5.5" in F43. How is it finding columns using "half's"?
Incrementally shifting the columns one digit at a time it was "skipping"
columns. For a goof, I put in 5 ½ and it worked.
So some tickers take whole digits and some need half digits to count
columns?
How does that work?
The other weird thing is, why don't I need to use a "5" in the formula
(which would be 16-11 in my situation)?
I'm putting in 5.5 in F43, which means the formula is seeing (16-5.5), so
10.5 columns over. But that would be way over in the Quarterly data columns.
The TTM column is literally the 5th column.
Very confused by this.
Best regards,
Jim
From: Jim Ranum [mailto:amt2100@gmail.com]
Sent: Monday, July 20, 2015 3:46 PM
To: smf_addin@yahoogroups.com
Subject: RE: Gurufocus - again - data from TTM column on Financials page
Here's another example of weird results.
DLNG
=RCHGetTableCell("http://www.gurufocus.com/financials/"&$F$42,($F66-$F$43),"
>Fiscal Period",">Revenue per Share")
Again, F66= 16, and in this case with only 5 years of data columns, F43 = 11
I'm getting $23.61 for a result and there is no $23.61 in ANY column.
Best regards,
Jim
From: Jim Ranum [mailto:amt2100@gmail.com]
Sent: Monday, July 20, 2015 3:31 PM
To: smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com>
Subject: Gurufocus - again - data from TTM column on Financials page
Hi Randy,
I'm sorry I need to revisit an issue that I think we discussed about a year
ago, but I can't find the post now.
I constructed in my formulas a reference to a cell that would allow me to
adjust for the fact that many stocks don't have the standard 15 years of
data on the Financials section of Gurufocus.
Example:
=RCHGetTableCell("http://www.gurufocus.com/financials/"&$F$42,($F66-$F$43),"
>Fiscal Period",">Gross Profit")
Where F42 = the ticker
F66 is 16 and F43 is zero for a normal stock that has 15 years of data
columns. If it doesn't, I just adjust F43 to the proper number and all my
other cells fall into line.
But at one time you mentioned a method to center off of the "TTM" column and
work left-wards. I didn't have time to readjust all the cells in my
spreadsheet at the time, but I am now running into difficulties getting my
data to pull out correctly. For some reason, doing things the way I was is
skipping columns, like it doesn't see them. (I have multiple cells that pull
consecutive years of data, say 3—5 years)
These difficulties cropped up after Guru switched from 10 years to 15 years
of data columns.
Can you please refresh my memory on how to grab the most recent TTM column's
data and the 2014, 2013, etc.? I have a few data points that I analyze 3
and 5 year averages and look at trend.
Thanks very much.
Best regards,
Jim
I finally got the correct number, but I don't understand this at all. I had
to use "5.5" in F43. How is it finding columns using "half's"?
Incrementally shifting the columns one digit at a time it was "skipping"
columns. For a goof, I put in 5 ½ and it worked.
So some tickers take whole digits and some need half digits to count
columns?
How does that work?
The other weird thing is, why don't I need to use a "5" in the formula
(which would be 16-11 in my situation)?
I'm putting in 5.5 in F43, which means the formula is seeing (16-5.5), so
10.5 columns over. But that would be way over in the Quarterly data columns.
The TTM column is literally the 5th column.
Very confused by this.
Best regards,
Jim
From: Jim Ranum [mailto:amt2100@gmail.com]
Sent: Monday, July 20, 2015 3:46 PM
To: smf_addin@yahoogroups.com
Subject: RE: Gurufocus - again - data from TTM column on Financials page
Here's another example of weird results.
DLNG
=RCHGetTableCell("http://www.gurufocus.com/financials/"&$F$42,($F66-$F$43),"
>Fiscal Period",">Revenue per Share")
Again, F66= 16, and in this case with only 5 years of data columns, F43 = 11
I'm getting $23.61 for a result and there is no $23.61 in ANY column.
Best regards,
Jim
From: Jim Ranum [mailto:amt2100@gmail.com]
Sent: Monday, July 20, 2015 3:31 PM
To: smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com>
Subject: Gurufocus - again - data from TTM column on Financials page
Hi Randy,
I'm sorry I need to revisit an issue that I think we discussed about a year
ago, but I can't find the post now.
I constructed in my formulas a reference to a cell that would allow me to
adjust for the fact that many stocks don't have the standard 15 years of
data on the Financials section of Gurufocus.
Example:
=RCHGetTableCell("http://www.gurufocus.com/financials/"&$F$42,
>Fiscal Period"
Where F42 = the ticker
F66 is 16 and F43 is zero for a normal stock that has 15 years of data
columns. If it doesn't, I just adjust F43 to the proper number and all my
other cells fall into line.
But at one time you mentioned a method to center off of the "TTM" column and
work left-wards. I didn't have time to readjust all the cells in my
spreadsheet at the time, but I am now running into difficulties getting my
data to pull out correctly. For some reason, doing things the way I was is
skipping columns, like it doesn't see them. (I have multiple cells that pull
consecutive years of data, say 3—5 years)
These difficulties cropped up after Guru switched from 10 years to 15 years
of data columns.
Can you please refresh my memory on how to grab the most recent TTM column's
data and the 2014, 2013, etc.? I have a few data points that I analyze 3
and 5 year averages and look at trend.
Thanks very much.
Best regards,
Jim
Mon Jul 20, 2015 1:23 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
On Mon, Jul 20, 2015 at 12:45 PM, 'Jim Ranum' amt2100@...wrote:
>
> Here̢۪s another example of weird results.
>
> DLNG
>
> =RCHGetTableCell("http://www.gurufocus.com/financials/"&$F$42,($F66-$F$43),">Fiscal
> Period",">Revenue per Share")
>
> Again, F66= 16, and in this case with only 5 years of data columns, F43 =
> 11
>
> I̢۪m getting $23.61 for a result and there is no $23.61 in ANY column.
>
>
When I do your values in a formula:
=RCHGetTableCell("http://www.gurufocus.com/financials/DLNG",5,">Fiscal
Period",">Revenue per Share")
...I get the correct value of $5.03 for TTM.
>
> Here̢۪s another example of weird results.
>
> DLNG
>
> =RCHGetTableCell("http://www.gurufocus.com/financials/"&$F$42,($F66-$F$43),">Fiscal
> Period",">Revenue per Share")
>
> Again, F66= 16, and in this case with only 5 years of data columns, F43 =
> 11
>
> I̢۪m getting $23.61 for a result and there is no $23.61 in ANY column.
>
>
When I do your values in a formula:
=RCHGetTableCell("http://www.gurufocus.com/financials/DLNG",5,"
Period"
...I get the correct value of $5.03 for TTM.
Mon Jul 20, 2015 1:25 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
On Mon, Jul 20, 2015 at 12:59 PM, 'Jim Ranum' amt2100@...wrote:
>
> I finally got the correct number, but I don̢۪t understand this at all. I
> had to use “5.5†in F43. How is it finding columns using “half’s�
>
> Incrementally shifting the columns one digit at a time it was “skippingâ€
> columns. For a goof, I put in 5 ½ and it worked.
>
> So some tickers take whole digits and some need half digits to count
> columns?
>
> How does that work?
>
>
>
> The other weird thing is, why don’t I need to use a “5†in the formula
> (which would be 16-11 in my situation)?
>
> I̢۪m putting in 5.5 in F43, which means the formula is seeing (16-5.5), so
> 10.5 columns over. But that would be way over in the Quarterly data
> columns. The TTM column is literally the 5th column.
>
> Very confused by this.
>
>
>
If I do:
=RCHGetTableCell("http://www.gurufocus.com/financials/DLNG",5.5,">Fiscal
Period",">Revenue per Share")
...I get the first quarterly amount -- the equivalent of using a 6:
=RCHGetTableCell("http://www.gurufocus.com/financials/DLNG",6,">Fiscal
Period",">Revenue per Share")
As far as I can tell, EXCEL/VBA just rounds off the number to the nearest
integer when passing it to the function.
>
> I finally got the correct number, but I don̢۪t understand this at all. I
> had to use “5.5†in F43. How is it finding columns using “half’s�
>
> Incrementally shifting the columns one digit at a time it was “skippingâ€
> columns. For a goof, I put in 5 ½ and it worked.
>
> So some tickers take whole digits and some need half digits to count
> columns?
>
> How does that work?
>
>
>
> The other weird thing is, why don’t I need to use a “5†in the formula
> (which would be 16-11 in my situation)?
>
> I̢۪m putting in 5.5 in F43, which means the formula is seeing (16-5.5), so
> 10.5 columns over. But that would be way over in the Quarterly data
> columns. The TTM column is literally the 5th column.
>
> Very confused by this.
>
>
>
If I do:
=RCHGetTableCell("http://www.gurufocus.com/financials/DLNG",5.5,">Fiscal
Period",">Revenue per Share")
...I get the first quarterly amount -- the equivalent of using a 6:
=RCHGetTableCell("http://www.gurufocus.com/financials/DLNG",6,"
Period"
As far as I can tell, EXCEL/VBA just rounds off the number to the nearest
integer when passing it to the function.
Mon Jul 20, 2015 1:40 pm (PDT) . Posted by:
hamishthedenizen
Jim, it may be more cumbersome than you'd like but here is a workaround that I used for my spreadsheets.
Row 1, list the numbers 1-16 into cells b1 - Q1
Reference those numbers to import the dates into row 2 using
RCHGetTableCell("http://www.gurufocus.com/financials/"&Ticker,b1,"except for per share",">Fiscal Period")
Then use =MATCH("TTM*",b2:q2,0) to return the column position of TTM data, and you can key backwards off that number.
Row 1, list the numbers 1-16 into cells b1 - Q1
Reference those numbers to import the dates into row 2 using
RCHGetTableCell("http://www.gurufocus.com/financials/"&Ticker,
Then use =MATCH("
Mon Jul 20, 2015 1:59 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Have you tried out the new function and the new template:
smfGetGuruFocusItem-Full-Template.xls
I tried it for DLNG and it looks like everything is coming through fine (I
did just have to fix the "Fiscal Periods" since it wasn't doing the "N/A"
correctly):
Fiscal Periods N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A Dec11 Dec12
Dec13 Dec14 TTM Mar13 Jun13 Sep13 Dec13 Mar14 Jun14 Sep14 Dec14 Mar15 Revenue
per Share N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A 0 3.56 11.08 5.96 5.03
0.97 0.98 0.99 2.8 1.4 0.46 0.81 2.02 1.74
There, the TTM formula is just:
=smfGetGuruFocusItem(Ticker,1,"TTM")
...where "1" means the "Revenue Per Share" data item.
What I did for my function is determine the columns that contain the first
and last quarterly data (i.e. iFQ and iLQ):
=smfConvertData(smfStrExtr(smfGetTagContent("
http://www.gurufocus.com/financials/DLNG", "td", 1, ">Per Share Data",
"/term/per share rev/", "ttm"), "normal", "'"))
=smfConvertData(smfStrExtr(smfGetTagContent("
http://www.gurufocus.com/financials/DLNG", "td", -1, ">Per Share Data",
"/term/per share rev/", "<tr"), "normal", "'"))
In the case of DLNG, 5 and 13.
Then, I know the TTM is always (iFQ-1), annuals are 1 to (iFQ-2), and
quarterlies are always iFQ to iLQ. And then I return "N/A" if offsetting
from those pointers would cause it to go out of bounds. That is:
Case pOffset > (iFQ - 2) Or pOffset < 0
smfGetGuruFocusItem = "N/A"
...or:
Case pOffset > (iLQ - iFQ) Or pOffset < 0
smfGetGuruFocusItem = "N/A"
....so my annuals are in the "(iFQ - pOffset - 1)" position and quarterlies
are in the "(iLQ - pOffset)" position.
On Mon, Jul 20, 2015 at 12:30 PM, 'Jim Ranum' amt2100@...wrote:
>
>
> Hi Randy,
>
>
>
> I̢۪m sorry I need to revisit an issue that I think we discussed about a
> year ago, but I can̢۪t find the post now.
>
> I constructed in my formulas a reference to a cell that would allow me to
> adjust for the fact that many stocks don̢۪t have the standard 15 years of
> data on the Financials section of Gurufocus.
>
> Example:
>
> =RCHGetTableCell("http://www.gurufocus.com/financials/"&$F$42,($F66-$F$43),">Fiscal
> Period",">Gross Profit")
>
>
>
> Where F42 = the ticker
>
> F66 is 16 and F43 is zero for a normal stock that has 15 years of data
> columns. If it doesn̢۪t, I just adjust F43 to the proper number and all my
> other cells fall into line.
>
> But at one time you mentioned a method to center off of the “TTM†column
> and work left-wards. I didn̢۪t have time to readjust all the cells in my
> spreadsheet at the time, but I am now running into difficulties getting my
> data to pull out correctly. For some reason, doing things the way I was is
> skipping columns, like it doesn̢۪t see them. (I have multiple cells that
> pull consecutive years of data, say 3â€"5 years)
>
> These difficulties cropped up after Guru switched from 10 years to 15
> years of data columns.
>
>
>
> Can you please refresh my memory on how to grab the most recent TTM
> column̢۪s data and the 2014, 2013, etc.? I have a few data points that I
> analyze 3 and 5 year averages and look at trend.
>
> Thanks very much.
>
smfGetGuruFocusItem-Full-Template.xls
I tried it for DLNG and it looks like everything is coming through fine (I
did just have to fix the "Fiscal Periods" since it wasn't doing the "N/A"
correctly):
Fiscal Periods N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A Dec11 Dec12
Dec13 Dec14 TTM Mar13 Jun13 Sep13 Dec13 Mar14 Jun14 Sep14 Dec14 Mar15 Revenue
per Share N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A 0 3.56 11.08 5.96 5.03
0.97 0.98 0.99 2.8 1.4 0.46 0.81 2.02 1.74
There, the TTM formula is just:
=smfGetGuruFocusItem(Ticker,1,"TTM")
...where "1" means the "Revenue Per Share" data item.
What I did for my function is determine the columns that contain the first
and last quarterly data (i.e. iFQ and iLQ):
=smfConvertData(smfStrExtr(smfGetTagContent("
http://www.gurufocus.com/financials/DLNG", "td", 1, ">Per Share Data",
"/term/per share rev/", "ttm"), "normal", "'"))
=smfConvertData(smfStrExtr(smfGetTagContent("
http://www.gurufocus.com/financials/DLNG", "td", -1, ">Per Share Data",
"/term/per share rev/", "<tr"), "normal", "'"))
In the case of DLNG, 5 and 13.
Then, I know the TTM is always (iFQ-1), annuals are 1 to (iFQ-2), and
quarterlies are always iFQ to iLQ. And then I return "N/A" if offsetting
from those pointers would cause it to go out of bounds. That is:
Case pOffset > (iFQ - 2) Or pOffset < 0
smfGetGuruFocusItem = "N/A"
...or:
Case pOffset > (iLQ - iFQ) Or pOffset < 0
smfGetGuruFocusItem = "N/A"
....so my annuals are in the "(iFQ - pOffset - 1)" position and quarterlies
are in the "(iLQ - pOffset)" position.
On Mon, Jul 20, 2015 at 12:30 PM, 'Jim Ranum' amt2100@...wrote:
>
>
> Hi Randy,
>
>
>
> I̢۪m sorry I need to revisit an issue that I think we discussed about a
> year ago, but I can̢۪t find the post now.
>
> I constructed in my formulas a reference to a cell that would allow me to
> adjust for the fact that many stocks don̢۪t have the standard 15 years of
> data on the Financials section of Gurufocus.
>
> Example:
>
> =RCHGetTableCell("http://www.gurufocus.com/financials/"&$F$42,
> Period"
>
>
>
> Where F42 = the ticker
>
> F66 is 16 and F43 is zero for a normal stock that has 15 years of data
> columns. If it doesn̢۪t, I just adjust F43 to the proper number and all my
> other cells fall into line.
>
> But at one time you mentioned a method to center off of the “TTM†column
> and work left-wards. I didn̢۪t have time to readjust all the cells in my
> spreadsheet at the time, but I am now running into difficulties getting my
> data to pull out correctly. For some reason, doing things the way I was is
> skipping columns, like it doesn̢۪t see them. (I have multiple cells that
> pull consecutive years of data, say 3â€"5 years)
>
> These difficulties cropped up after Guru switched from 10 years to 15
> years of data columns.
>
>
>
> Can you please refresh my memory on how to grab the most recent TTM
> column̢۪s data and the 2014, 2013, etc.? I have a few data points that I
> analyze 3 and 5 year averages and look at trend.
>
> Thanks very much.
>
Mon Jul 20, 2015 2:54 pm (PDT) . Posted by:
"Jim Ranum" amt2100
Hi Randy,
OK, I understand what you responded below, up to the point that it is unclear if you have automated this to being able to just enter the ticker or if you have to verify what columns come back and then input a column offset number. (a 2-step process)
I had a variation of this as a 2-step, enter ticker, see what column comes back as the date in first column after “Fiscal Periods†and then input my offset number.
Unless I̢۪m misunderstanding, that is still what you are doing. I just don̢۪t have the entire line showing, just the result of first column.
If I misunderstood, and there is a way to automate the entry to find the TTM column from your lines below and have it go into the other formulas, could you please give the complete formula that would go in the cell?
If you are still doing a 2-step entry, like I am, I am still confused as to what my spreadsheet is doing. If I put in a 5, I get the column before the TTM and if I put in a 6, I get the column after the TTM. But if I put in a 5.5, I get the TTM column.
Best regards,
Jim
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Monday, July 20, 2015 4:59 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Gurufocus - again - data from TTM column on Financials page
Have you tried out the new function and the new template:
smfGetGuruFocusItem-Full-Template.xls
I tried it for DLNG and it looks like everything is coming through fine (I did just have to fix the "Fiscal Periods" since it wasn't doing the "N/A" correctly):
Fiscal Periods
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
Dec11
Dec12
Dec13
Dec14
TTM
Mar13
Jun13
Sep13
Dec13
Mar14
Jun14
Sep14
Dec14
Mar15
Revenue per Share
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
0
3.56
11.08
5.96
5.03
0.97
0.98
0.99
2.8
1.4
0.46
0.81
2.02
1.74
There, the TTM formula is just:
=smfGetGuruFocusItem(Ticker,1,"TTM")
...where "1" means the "Revenue Per Share" data item.
What I did for my function is determine the columns that contain the first and last quarterly data (i.e. iFQ and iLQ):
=smfConvertData(smfStrExtr(smfGetTagContent("http://www.gurufocus.com/financials/DLNG", "td", 1, ">Per Share Data", "/term/per share rev/", "ttm"), "normal", "'"))
=smfConvertData(smfStrExtr(smfGetTagContent("http://www.gurufocus.com/financials/DLNG", "td", -1, ">Per Share Data", "/term/per share rev/", "<tr"), "normal", "'"))
In the case of DLNG, 5 and 13.
Then, I know the TTM is always (iFQ-1), annuals are 1 to (iFQ-2), and quarterlies are always iFQ to iLQ. And then I return "N/A" if offsetting from those pointers would cause it to go out of bounds. That is:
Case pOffset > (iFQ - 2) Or pOffset < 0
smfGetGuruFocusItem = "N/A"
...or:
Case pOffset > (iLQ - iFQ) Or pOffset < 0
smfGetGuruFocusItem = "N/A"
....so my annuals are in the "(iFQ - pOffset - 1)" position and quarterlies are in the "(iLQ - pOffset)" position.
On Mon, Jul 20, 2015 at 12:30 PM, 'Jim Ranum' amt2100@...wrote <mailto:amt2100@...wrote> :
Hi Randy,
I̢۪m sorry I need to revisit an issue that I think we discussed about a year ago, but I can̢۪t find the post now.
I constructed in my formulas a reference to a cell that would allow me to adjust for the fact that many stocks don̢۪t have the standard 15 years of data on the Financials section of Gurufocus.
Example:
=RCHGetTableCell("http://www.gurufocus.com/financials/"&$F$42,($F66-$F$43),">Fiscal Period",">Gross Profit")
Where F42 = the ticker
F66 is 16 and F43 is zero for a normal stock that has 15 years of data columns. If it doesn̢۪t, I just adjust F43 to the proper number and all my other cells fall into line.
But at one time you mentioned a method to center off of the “TTM†column and work left-wards. I didn’t have time to readjust all the cells in my spreadsheet at the time, but I am now running into difficulties getting my data to pull out correctly. For some reason, doing things the way I was is skipping columns, like it doesn’t see them. (I have multiple cells that pull consecutive years of data, say 3â€"5 years)
These difficulties cropped up after Guru switched from 10 years to 15 years of data columns.
Can you please refresh my memory on how to grab the most recent TTM column̢۪s data and the 2014, 2013, etc.? I have a few data points that I analyze 3 and 5 year averages and look at trend.
Thanks very much.
OK, I understand what you responded below, up to the point that it is unclear if you have automated this to being able to just enter the ticker or if you have to verify what columns come back and then input a column offset number. (a 2-step process)
I had a variation of this as a 2-step, enter ticker, see what column comes back as the date in first column after “Fiscal Periods†and then input my offset number.
Unless I̢۪m misunderstanding, that is still what you are doing. I just don̢۪t have the entire line showing, just the result of first column.
If I misunderstood, and there is a way to automate the entry to find the TTM column from your lines below and have it go into the other formulas, could you please give the complete formula that would go in the cell?
If you are still doing a 2-step entry, like I am, I am still confused as to what my spreadsheet is doing. If I put in a 5, I get the column before the TTM and if I put in a 6, I get the column after the TTM. But if I put in a 5.5, I get the TTM column.
Best regards,
Jim
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Monday, July 20, 2015 4:59 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Gurufocus - again - data from TTM column on Financials page
Have you tried out the new function and the new template:
smfGetGuruFocusItem-Full-Template.xls
I tried it for DLNG and it looks like everything is coming through fine (I did just have to fix the "Fiscal Periods" since it wasn't doing the "N/A" correctly):
Fiscal Periods
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
Dec11
Dec12
Dec13
Dec14
TTM
Mar13
Jun13
Sep13
Dec13
Mar14
Jun14
Sep14
Dec14
Mar15
Revenue per Share
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
0
3.56
11.08
5.96
5.03
0.97
0.98
0.99
2.8
1.4
0.46
0.81
2.02
1.74
There, the TTM formula is just:
=smfGetGuruFocusItem(Ticker,1,"TTM")
...where "1" means the "Revenue Per Share" data item.
What I did for my function is determine the columns that contain the first and last quarterly data (i.e. iFQ and iLQ):
=smfConvertData(smfStrExtr(smfGetTagContent("http://www.gurufocus.com/financials/DLNG", "td", 1, ">Per Share Data", "/term/per share rev/", "ttm"), "normal", "'"))
=smfConvertData(smfStrExtr(smfGetTagContent("http://www.gurufocus.com/financials/DLNG", "td", -1, ">Per Share Data", "/term/per share rev/", "<tr"), "normal", "'"))
In the case of DLNG, 5 and 13.
Then, I know the TTM is always (iFQ-1), annuals are 1 to (iFQ-2), and quarterlies are always iFQ to iLQ. And then I return "N/A" if offsetting from those pointers would cause it to go out of bounds. That is:
Case pOffset > (iFQ - 2) Or pOffset < 0
smfGetGuruFocusItem = "N/A"
...or:
Case pOffset > (iLQ - iFQ) Or pOffset < 0
smfGetGuruFocusItem = "N/A"
....so my annuals are in the "(iFQ - pOffset - 1)" position and quarterlies are in the "(iLQ - pOffset)" position.
On Mon, Jul 20, 2015 at 12:30 PM, 'Jim Ranum' amt2100@...wrote <mailto:amt2100@...wrote> :
Hi Randy,
I̢۪m sorry I need to revisit an issue that I think we discussed about a year ago, but I can̢۪t find the post now.
I constructed in my formulas a reference to a cell that would allow me to adjust for the fact that many stocks don̢۪t have the standard 15 years of data on the Financials section of Gurufocus.
Example:
=RCHGetTableCell("http://www.gurufocus.com/financials/"&$F$42,
Where F42 = the ticker
F66 is 16 and F43 is zero for a normal stock that has 15 years of data columns. If it doesn̢۪t, I just adjust F43 to the proper number and all my other cells fall into line.
But at one time you mentioned a method to center off of the “TTM†column and work left-wards. I didn’t have time to readjust all the cells in my spreadsheet at the time, but I am now running into difficulties getting my data to pull out correctly. For some reason, doing things the way I was is skipping columns, like it doesn’t see them. (I have multiple cells that pull consecutive years of data, say 3â€"5 years)
These difficulties cropped up after Guru switched from 10 years to 15 years of data columns.
Can you please refresh my memory on how to grab the most recent TTM column̢۪s data and the 2014, 2013, etc.? I have a few data points that I analyze 3 and 5 year averages and look at trend.
Thanks very much.
Mon Jul 20, 2015 2:58 pm (PDT) . Posted by:
"Jim Ranum" amt2100
Thanks Dan. It still looks like you̢۪re doing a 2-step input process, and that̢۪s similar to what I̢۪m doing. I̢۪m just getting bizarre results, when it was working fine for over a year.
Best regards,
Jim
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Monday, July 20, 2015 4:41 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: Gurufocus - again - data from TTM column on Financials page
Jim, it may be more cumbersome than you'd like but here is a workaround that I used for my spreadsheets.
Row 1, list the numbers 1-16 into cells b1 - Q1
Reference those numbers to import the dates into row 2 using
RCHGetTableCell("http://www.gurufocus.com/financials/"&Ticker,b1,"except for per share",">Fiscal Period")
Then use =MATCH("TTM*",b2:q2,0) to return the column position of TTM data, and you can key backwards off that number.
Best regards,
Jim
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Monday, July 20, 2015 4:41 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: Gurufocus - again - data from TTM column on Financials page
Jim, it may be more cumbersome than you'd like but here is a workaround that I used for my spreadsheets.
Row 1, list the numbers 1-16 into cells b1 - Q1
Reference those numbers to import the dates into row 2 using
RCHGetTableCell("http://www.gurufocus.com/financials/"&Ticker,
Then use =MATCH("
Mon Jul 20, 2015 3:08 pm (PDT) . Posted by:
"Jim Ranum" amt2100
OK, good news I found why I was getting columns skipping. The formatting of the cells I used to calculate the offset was apparently not consistent.
That was a weird one.
Still interested if anyone can figure out a one-step method to just enter ticker and it pulls from the TTM column.
Sure would be nice if Guru would just have the TTM in column 1.
Best regards,
Jim
From: Jim Ranum [mailto:amt2100@gmail.com]
Sent: Monday, July 20, 2015 5:54 PM
To: 'smf_addin@yahoogroups.com'
Subject: RE: [smf_addin] Gurufocus - again - data from TTM column on Financials page
Hi Randy,
OK, I understand what you responded below, up to the point that it is unclear if you have automated this to being able to just enter the ticker or if you have to verify what columns come back and then input a column offset number. (a 2-step process)
I had a variation of this as a 2-step, enter ticker, see what column comes back as the date in first column after “Fiscal Periods†and then input my offset number.
Unless I̢۪m misunderstanding, that is still what you are doing. I just don̢۪t have the entire line showing, just the result of first column.
If I misunderstood, and there is a way to automate the entry to find the TTM column from your lines below and have it go into the other formulas, could you please give the complete formula that would go in the cell?
If you are still doing a 2-step entry, like I am, I am still confused as to what my spreadsheet is doing. If I put in a 5, I get the column before the TTM and if I put in a 6, I get the column after the TTM. But if I put in a 5.5, I get the TTM column.
Best regards,
Jim
From: smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com> [mailto:smf_addin@yahoogroups.com]
Sent: Monday, July 20, 2015 4:59 PM
To: smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com>
Subject: Re: [smf_addin] Gurufocus - again - data from TTM column on Financials page
Have you tried out the new function and the new template:
smfGetGuruFocusItem-Full-Template.xls
I tried it for DLNG and it looks like everything is coming through fine (I did just have to fix the "Fiscal Periods" since it wasn't doing the "N/A" correctly):
Fiscal Periods
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
Dec11
Dec12
Dec13
Dec14
TTM
Mar13
Jun13
Sep13
Dec13
Mar14
Jun14
Sep14
Dec14
Mar15
Revenue per Share
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
0
3.56
11.08
5.96
5.03
0.97
0.98
0.99
2.8
1.4
0.46
0.81
2.02
1.74
There, the TTM formula is just:
=smfGetGuruFocusItem(Ticker,1,"TTM")
...where "1" means the "Revenue Per Share" data item.
What I did for my function is determine the columns that contain the first and last quarterly data (i.e. iFQ and iLQ):
=smfConvertData(smfStrExtr(smfGetTagContent("http://www.gurufocus.com/financials/DLNG", "td", 1, ">Per Share Data", "/term/per share rev/", "ttm"), "normal", "'"))
=smfConvertData(smfStrExtr(smfGetTagContent("http://www.gurufocus.com/financials/DLNG", "td", -1, ">Per Share Data", "/term/per share rev/", "<tr"), "normal", "'"))
In the case of DLNG, 5 and 13.
Then, I know the TTM is always (iFQ-1), annuals are 1 to (iFQ-2), and quarterlies are always iFQ to iLQ. And then I return "N/A" if offsetting from those pointers would cause it to go out of bounds. That is:
Case pOffset > (iFQ - 2) Or pOffset < 0
smfGetGuruFocusItem = "N/A"
...or:
Case pOffset > (iLQ - iFQ) Or pOffset < 0
smfGetGuruFocusItem = "N/A"
....so my annuals are in the "(iFQ - pOffset - 1)" position and quarterlies are in the "(iLQ - pOffset)" position.
On Mon, Jul 20, 2015 at 12:30 PM, 'Jim Ranum' amt2100@...wrote <mailto:amt2100@...wrote> :
Hi Randy,
I̢۪m sorry I need to revisit an issue that I think we discussed about a year ago, but I can̢۪t find the post now.
I constructed in my formulas a reference to a cell that would allow me to adjust for the fact that many stocks don̢۪t have the standard 15 years of data on the Financials section of Gurufocus.
Example:
=RCHGetTableCell("http://www.gurufocus.com/financials/"&$F$42,($F66-$F$43),">Fiscal Period",">Gross Profit")
Where F42 = the ticker
F66 is 16 and F43 is zero for a normal stock that has 15 years of data columns. If it doesn̢۪t, I just adjust F43 to the proper number and all my other cells fall into line.
But at one time you mentioned a method to center off of the “TTM†column and work left-wards. I didn’t have time to readjust all the cells in my spreadsheet at the time, but I am now running into difficulties getting my data to pull out correctly. For some reason, doing things the way I was is skipping columns, like it doesn’t see them. (I have multiple cells that pull consecutive years of data, say 3â€"5 years)
These difficulties cropped up after Guru switched from 10 years to 15 years of data columns.
Can you please refresh my memory on how to grab the most recent TTM column̢۪s data and the 2014, 2013, etc.? I have a few data points that I analyze 3 and 5 year averages and look at trend.
Thanks very much.
That was a weird one.
Still interested if anyone can figure out a one-step method to just enter ticker and it pulls from the TTM column.
Sure would be nice if Guru would just have the TTM in column 1.
Best regards,
Jim
From: Jim Ranum [mailto:amt2100@gmail.com]
Sent: Monday, July 20, 2015 5:54 PM
To: 'smf_addin@yahoogroups.com'
Subject: RE: [smf_addin] Gurufocus - again - data from TTM column on Financials page
Hi Randy,
OK, I understand what you responded below, up to the point that it is unclear if you have automated this to being able to just enter the ticker or if you have to verify what columns come back and then input a column offset number. (a 2-step process)
I had a variation of this as a 2-step, enter ticker, see what column comes back as the date in first column after “Fiscal Periods†and then input my offset number.
Unless I̢۪m misunderstanding, that is still what you are doing. I just don̢۪t have the entire line showing, just the result of first column.
If I misunderstood, and there is a way to automate the entry to find the TTM column from your lines below and have it go into the other formulas, could you please give the complete formula that would go in the cell?
If you are still doing a 2-step entry, like I am, I am still confused as to what my spreadsheet is doing. If I put in a 5, I get the column before the TTM and if I put in a 6, I get the column after the TTM. But if I put in a 5.5, I get the TTM column.
Best regards,
Jim
From: smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com> [mailto:smf_addin@yahoogroups.com]
Sent: Monday, July 20, 2015 4:59 PM
To: smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com>
Subject: Re: [smf_addin] Gurufocus - again - data from TTM column on Financials page
Have you tried out the new function and the new template:
smfGetGuruFocusItem-Full-Template.xls
I tried it for DLNG and it looks like everything is coming through fine (I did just have to fix the "Fiscal Periods" since it wasn't doing the "N/A" correctly):
Fiscal Periods
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
Dec11
Dec12
Dec13
Dec14
TTM
Mar13
Jun13
Sep13
Dec13
Mar14
Jun14
Sep14
Dec14
Mar15
Revenue per Share
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
0
3.56
11.08
5.96
5.03
0.97
0.98
0.99
2.8
1.4
0.46
0.81
2.02
1.74
There, the TTM formula is just:
=smfGetGuruFocusItem(Ticker,1,"TTM")
...where "1" means the "Revenue Per Share" data item.
What I did for my function is determine the columns that contain the first and last quarterly data (i.e. iFQ and iLQ):
=smfConvertData(smfStrExtr(smfGetTagContent("http://www.gurufocus.com/financials/DLNG", "td", 1, ">Per Share Data", "/term/per share rev/", "ttm"), "normal", "'"))
=smfConvertData(smfStrExtr(smfGetTagContent("http://www.gurufocus.com/financials/DLNG", "td", -1, ">Per Share Data", "/term/per share rev/", "<tr"), "normal", "'"))
In the case of DLNG, 5 and 13.
Then, I know the TTM is always (iFQ-1), annuals are 1 to (iFQ-2), and quarterlies are always iFQ to iLQ. And then I return "N/A" if offsetting from those pointers would cause it to go out of bounds. That is:
Case pOffset > (iFQ - 2) Or pOffset < 0
smfGetGuruFocusItem = "N/A"
...or:
Case pOffset > (iLQ - iFQ) Or pOffset < 0
smfGetGuruFocusItem = "N/A"
....so my annuals are in the "(iFQ - pOffset - 1)" position and quarterlies are in the "(iLQ - pOffset)" position.
On Mon, Jul 20, 2015 at 12:30 PM, 'Jim Ranum' amt2100@...wrote <mailto:amt2100@...wrote> :
Hi Randy,
I̢۪m sorry I need to revisit an issue that I think we discussed about a year ago, but I can̢۪t find the post now.
I constructed in my formulas a reference to a cell that would allow me to adjust for the fact that many stocks don̢۪t have the standard 15 years of data on the Financials section of Gurufocus.
Example:
=RCHGetTableCell("http://www.gurufocus.com/financials/"&$F$42,
Where F42 = the ticker
F66 is 16 and F43 is zero for a normal stock that has 15 years of data columns. If it doesn̢۪t, I just adjust F43 to the proper number and all my other cells fall into line.
But at one time you mentioned a method to center off of the “TTM†column and work left-wards. I didn’t have time to readjust all the cells in my spreadsheet at the time, but I am now running into difficulties getting my data to pull out correctly. For some reason, doing things the way I was is skipping columns, like it doesn’t see them. (I have multiple cells that pull consecutive years of data, say 3â€"5 years)
These difficulties cropped up after Guru switched from 10 years to 15 years of data columns.
Can you please refresh my memory on how to grab the most recent TTM column̢۪s data and the 2014, 2013, etc.? I have a few data points that I analyze 3 and 5 year averages and look at trend.
Thanks very much.
Mon Jul 20, 2015 4:18 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The new function/formula automates it completely. For example, that
"Revenue per Share" row has these formulas:
=smfGetGuruFocusItem(Ticker,1,"L")
=smfGetGuruFocusItem(Ticker,1,"A",14)
=smfGetGuruFocusItem(Ticker,1,"A",13)
=smfGetGuruFocusItem(Ticker,1,"A",12)
=smfGetGuruFocusItem(Ticker,1,"A",11)
=smfGetGuruFocusItem(Ticker,1,"A",10)
=smfGetGuruFocusItem(Ticker,1,"A",9)
=smfGetGuruFocusItem(Ticker,1,"A",8)
=smfGetGuruFocusItem(Ticker,1,"A",7)
=smfGetGuruFocusItem(Ticker,1,"A",6)
=smfGetGuruFocusItem(Ticker,1,"A",5)
=smfGetGuruFocusItem(Ticker,1,"A",4)
=smfGetGuruFocusItem(Ticker,1,"A",3)
=smfGetGuruFocusItem(Ticker,1,"A",2)
=smfGetGuruFocusItem(Ticker,1,"A",1)
=smfGetGuruFocusItem(Ticker,1,"A",0)
=smfGetGuruFocusItem(Ticker,1,"TTM")
=smfGetGuruFocusItem(Ticker,1,"Q",8)
=smfGetGuruFocusItem(Ticker,1,"Q",7)
=smfGetGuruFocusItem(Ticker,1,"Q",6)
=smfGetGuruFocusItem(Ticker,1,"Q",5)
=smfGetGuruFocusItem(Ticker,1,"Q",4)
=smfGetGuruFocusItem(Ticker,1,"Q",3)
=smfGetGuruFocusItem(Ticker,1,"Q",2)
=smfGetGuruFocusItem(Ticker,1,"Q",1)
=smfGetGuruFocusItem(Ticker,1,"Q",0)
The "L" is for the label, "A"nnual periods from 14 thru 0 (0=current FY),
"TTM" for TTM value, "Q"uarterly periods from 8 to 0 (0=current FQ).
The second parameter varies from 0 to 185, for the various line items I
found on GuruFocus (including a few that are specific to financial
companies). I should be able to expand that if I missed any, or if they add
any. The function shouldn't care how many years or quarters they carry, but
it does requires the same layout of a line -- annuals, TTM, quarters.
Each formula stands on its own, so you can cut and paste whichever
columns/rows/sections you'd like. Here's one that has 15 years of data:
*MMM*
Fiscal Periods Dec00 Dec01 Dec02 Dec03
Dec04 Dec05 Dec06 Dec07 Dec08 Dec09 Dec10 Dec11 Dec12 Dec13 Dec14 TTM Mar13
Jun13 Sep13 Dec13 Mar14 Jun14 Sep14 Dec14 Mar15 Revenue per Share 20.91
20.07 20.65 22.92 25.12 27.09 30.12 33.42 35.73 32.72 36.75 41.18 42.52
44.51 48.07 48.14 10.87 11.09 11.44 11.11 11.61 12.24 12.37 11.86 11.67 EBITDA
per Share 5.14 4.25 5.11 5.92 7.06 7.55 8.97 10.11 9.16 8.5 9.75 10.37 11.1
11.65 12.95 13.06 2.84 2.93 3.02 2.86 3.08 3.34 3.43 3.1 3.19 EBIT per
Share 3.82 2.84 3.85 4.67 5.75 6.21 7.48 8.46 7.38 6.81 8.16 8.59 9.22 9.61
10.78 10.89 2.34 2.43 2.51 2.32 2.54 2.79 2.89 2.55 2.66
Like I said, it returns "N/A" if the data is less than the 15 years or 9
quarters of carried data. And should return "Premium" for fields that
aren't available because you're not logged in as a subscriber.
On Mon, Jul 20, 2015 at 2:54 PM, 'Jim Ranum' amt2100@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
>
> OK, I understand what you responded below, up to the point that it is
> unclear if you have automated this to being able to just enter the ticker
> or if you have to verify what columns come back and then input a column
> offset number. (a 2-step process)
>
> I had a variation of this as a 2-step, enter ticker, see what column comes
> back as the date in first column after “Fiscal Periods†and then input my
> offset number.
>
> Unless I̢۪m misunderstanding, that is still what you are doing. I just
> don̢۪t have the entire line showing, just the result of first column.
>
> If I misunderstood, and there is a way to automate the entry to find the
> TTM column from your lines below and have it go into the other formulas,
> could you please give the complete formula that would go in the cell?
>
>
>
> If you are still doing a 2-step entry, like I am, I am still confused as
> to what my spreadsheet is doing. If I put in a 5, I get the column before
> the TTM and if I put in a 6, I get the column after the TTM. But if I put
> in a 5.5, I get the TTM column.
>
>
>
"Revenue per Share" row has these formulas:
=smfGetGuruFocusItem(Ticker,1,"L")
=smfGetGuruFocusItem(Ticker,1,"A",14)
=smfGetGuruFocusItem(Ticker,1,"A",13)
=smfGetGuruFocusItem(Ticker,1,"A",12)
=smfGetGuruFocusItem(Ticker,1,"A",11)
=smfGetGuruFocusItem(Ticker,1,"A",10)
=smfGetGuruFocusItem(Ticker,1,"A",9)
=smfGetGuruFocusItem(Ticker,1,"A",8)
=smfGetGuruFocusItem(Ticker,1,"A",7)
=smfGetGuruFocusItem(Ticker,1,"A",6)
=smfGetGuruFocusItem(Ticker,1,"A",5)
=smfGetGuruFocusItem(Ticker,1,"A",4)
=smfGetGuruFocusItem(Ticker,1,"A",3)
=smfGetGuruFocusItem(Ticker,1,"A",2)
=smfGetGuruFocusItem(Ticker,1,"A",1)
=smfGetGuruFocusItem(Ticker,1,"A",0)
=smfGetGuruFocusItem(Ticker,1,"TTM")
=smfGetGuruFocusItem(Ticker,1,"Q",8)
=smfGetGuruFocusItem(Ticker,1,"Q",7)
=smfGetGuruFocusItem(Ticker,1,"Q",6)
=smfGetGuruFocusItem(Ticker,1,"Q",5)
=smfGetGuruFocusItem(Ticker,1,"Q",4)
=smfGetGuruFocusItem(Ticker,1,"Q",3)
=smfGetGuruFocusItem(Ticker,1,"Q",2)
=smfGetGuruFocusItem(Ticker,1,"Q",1)
=smfGetGuruFocusItem(Ticker,1,"Q",0)
The "L" is for the label, "A"nnual periods from 14 thru 0 (0=current FY),
"TTM" for TTM value, "Q"uarterly periods from 8 to 0 (0=current FQ).
The second parameter varies from 0 to 185, for the various line items I
found on GuruFocus (including a few that are specific to financial
companies). I should be able to expand that if I missed any, or if they add
any. The function shouldn't care how many years or quarters they carry, but
it does requires the same layout of a line -- annuals, TTM, quarters.
Each formula stands on its own, so you can cut and paste whichever
columns/rows/sections you'd like. Here's one that has 15 years of data:
*MMM*
Fiscal Periods Dec00 Dec01 Dec02 Dec03
Dec04 Dec05 Dec06 Dec07 Dec08 Dec09 Dec10 Dec11 Dec12 Dec13 Dec14 TTM Mar13
Jun13 Sep13 Dec13 Mar14 Jun14 Sep14 Dec14 Mar15 Revenue per Share 20.91
20.07 20.65 22.92 25.12 27.09 30.12 33.42 35.73 32.72 36.75 41.18 42.52
44.51 48.07 48.14 10.87 11.09 11.44 11.11 11.61 12.24 12.37 11.86 11.67 EBITDA
per Share 5.14 4.25 5.11 5.92 7.06 7.55 8.97 10.11 9.16 8.5 9.75 10.37 11.1
11.65 12.95 13.06 2.84 2.93 3.02 2.86 3.08 3.34 3.43 3.1 3.19 EBIT per
Share 3.82 2.84 3.85 4.67 5.75 6.21 7.48 8.46 7.38 6.81 8.16 8.59 9.22 9.61
10.78 10.89 2.34 2.43 2.51 2.32 2.54 2.79 2.89 2.55 2.66
Like I said, it returns "N/A" if the data is less than the 15 years or 9
quarters of carried data. And should return "Premium" for fields that
aren't available because you're not logged in as a subscriber.
On Mon, Jul 20, 2015 at 2:54 PM, 'Jim Ranum' amt2100@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
>
> OK, I understand what you responded below, up to the point that it is
> unclear if you have automated this to being able to just enter the ticker
> or if you have to verify what columns come back and then input a column
> offset number. (a 2-step process)
>
> I had a variation of this as a 2-step, enter ticker, see what column comes
> back as the date in first column after “Fiscal Periods†and then input my
> offset number.
>
> Unless I̢۪m misunderstanding, that is still what you are doing. I just
> don̢۪t have the entire line showing, just the result of first column.
>
> If I misunderstood, and there is a way to automate the entry to find the
> TTM column from your lines below and have it go into the other formulas,
> could you please give the complete formula that would go in the cell?
>
>
>
> If you are still doing a 2-step entry, like I am, I am still confused as
> to what my spreadsheet is doing. If I put in a 5, I get the column before
> the TTM and if I put in a 6, I get the column after the TTM. But if I put
> in a 5.5, I get the TTM column.
>
>
>
Mon Jul 20, 2015 4:24 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The "TTM" column is the easiest one to get. For example:
=smfConvertData(smfGetTagContent("http://www.gurufocus.com/financials/MMM",
"div", -1, ">Per Share Data", "/term/per share rev/", "yesttm"))
I identify each of my line items with the "/term/..." link then have on the
label of the line.
On Mon, Jul 20, 2015 at 3:08 PM, 'Jim Ranum' amt2100@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Still interested if anyone can figure out a one-step method to just enter
> ticker and it pulls from the TTM column.
>
> Sure would be nice if Guru would just have the TTM in column 1.
>
>
>
=smfConvertData(smfGetTagContent("http://www.gurufocus.com/financials/MMM",
"div", -1, ">Per Share Data", "/term/per share rev/", "yesttm"))
I identify each of my line items with the "/term/..." link then have on the
label of the line.
On Mon, Jul 20, 2015 at 3:08 PM, 'Jim Ranum' amt2100@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Still interested if anyone can figure out a one-step method to just enter
> ticker and it pulls from the TTM column.
>
> Sure would be nice if Guru would just have the TTM in column 1.
>
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar