# 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