[smf_addin] Digest Number 3683

EXCEL Stock Market Functions Add-in Group

7 Messages

Digest #3683

1a

Finding most recent dividend increase by "Jose Jacob" pepecan47

1b

Re: Finding most recent dividend increase by "Randy Harmelink" rharmelink

1c

Re: Finding most recent dividend increase by jimmymc00

1d

Re: Finding most recent dividend increase by jimmymc00

2a

smfGetGuruFocusItem() Not Working for Quarterly Data by danseiler2000

2b

Re: smfGetGuruFocusItem() Not Working for Quarterly Data by "Randy Harmelink" rharmelink

2c

Re: smfGetGuruFocusItem() Not Working for Quarterly Data by danseiler2000

Messages

1a

Finding most recent dividend increase

Sun Apr 24, 2016 10:00 am (PDT) . Posted by:

"Jose Jacob" pepecan47

This is a question more related to Excel than SMF, but since there are gurus here I will ask anyway.

I have the following spreadsheet with the last 8 dividends. How can I find the most recent dividend increase?

Ticker    Past1div Past2div Past3div Past4div Past5div Past6div Past7div Past8div   
d           0.7000    0.6480    0.6480    0.6480    0.6480    0.6000    0.6000    0.6000   
mcd      0.8900    0.8900    0.8500    0.8500    0.8500    0.8500    0.8100    0.8100

For D, (0.7000 - 0.6480) / 0.6480 = 8.02%
for MCD, (0.8900 - 0.8500) / 0.8500 = 4.71%

Regards,
Jose L. Jacob

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

1b

Re: Finding most recent dividend increase

Sun Apr 24, 2016 2:10 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

If you dividends are in the range E6:L6, array-enter this formula to find
the earliest column with an increase:

=MIN(IF(E6:K6>F6:L6,COLUMN(E6:K6),999))

Then compute the increase with this, referring to that value:

=IF(M6=999,NA(),INDEX(6:6,M6)/INDEX(6:6,M6+1)-1)

Ticker Past1div Past2div Past3div Past4div Past5div Past6div Past7div
Past8div Column % Chg
d 0.7000 0.6480 0.6480 0.6480 0.6480 0.6000 0.6000 0.6000 5 0.080247
mcd 0.8900 0.8900 0.8500 0.8500 0.8500 0.8500 0.8100 0.8100 6 0.047059
xxx 1 1 1 1 1 1 1 1 999

On Sun, Apr 24, 2016 at 9:59 AM, Jose Jacob pepecan47
​...wrote:

> This is a question more related to Excel than SMF, but since there are
> gurus here I will ask anyway.
>
> I have the following spreadsheet with the last 8 dividends. How can I find
> the most recent dividend increase?
>
> Ticker Past1div Past2div Past3div Past4div Past5div Past6div Past7div
> Past8div
> d 0.7000 0.6480 0.6480 0.6480 0.6480 0.6000
> 0.6000 0.6000
> mcd 0.8900 0.8900 0.8500 0.8500 0.8500 0.8500
> 0.8100 0.8100
>
> For D, (0.7000 - 0.6480) / 0.6480 = 8.02%
> for MCD, (0.8900 - 0.8500) / 0.8500 = 4.71%
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

1c

Re: Finding most recent dividend increase

Sun Apr 24, 2016 2:22 pm (PDT) . Posted by:

jimmymc00

Jose. Here are a couple of ways to calculate the most recent change (positive or negative) within your string of 8 values. You may want to try both as each may be helpful for what you are doing.

1. calculates change for each column:
A B C D E F G H I

MCD

0.89

0.89

0.85

0.85

0.85

0.85

0.81

0.81




4.71%


4.94%


The formula to place in column B under the first dividend in this e.g.: =if(B1<>C1,(B1-C1)/C1,"")

It would then be copied to columns C - H. (The MCD data for this formula happened to be in row 1 of my spreadsheet.)

2. calculates latest change only in columns B - H

MCD
0.89
0.89
0.85
0.85
0.85
0.85
0.81
0.81

4.71%

The formula for the result in column A under MCD in this e.g.: =+IF(B4<>C4,(B4-C4)/C4,IF(C4<>D4,(C4-D4)/D4,IF(D4<>E4,(D4-E4)/E4,IF(E4<>F4,(E4-F4)/F4,IF(F4<>G4,(F4-G4)/G4,IF(G4<>H4,(G4-H4)/H4,IF(H4<>I4,(H4-I4)/I4,"")))))))

As you can see it is a conditional logic statement of repeated column comparison starting with 1st two columns and advancing to next two. Once if finds an increase or decrease between columns it stops populating allowing the first result to show. (The MCD data for this formula happened to be in row 4 of my spreadsheet.)

Both formulas can be changed to show only increases by removing the "<" in each of the comparisons.

Hope this helps. Jim

-----Original Message-----
From: Jose Jacob pepecan47@yahoo.ca [smf_addin] <smf_addin@yahoogroups.com>
To: smf_addin <smf_addin@yahoogroups.com>
Sent: Sun, Apr 24, 2016 1:00 pm
Subject: [smf_addin] Finding most recent dividend increase

This is a question more related to Excel than SMF, but since there are gurus here I will ask anyway.

I have the following spreadsheet with the last 8 dividends. How can I find the most recent dividend increase?

Ticker Past1div Past2div Past3div Past4div Past5div Past6div Past7div Past8div
d 0.7000 0.6480 0.6480 0.6480 0.6480 0.6000 0.6000 0.6000
mcd 0.8900 0.8900 0.8500 0.8500 0.8500 0.8500 0.8100 0.8100

For D, (0.7000 - 0.6480) / 0.6480 = 8.02%
for MCD, (0.8900 - 0.8500) / 0.8500 = 4.71%

Regards,
Jose L. Jacob

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

1d

Re: Finding most recent dividend increase

Sun Apr 24, 2016 2:25 pm (PDT) . Posted by:

jimmymc00

Randy types faster than I do and his response is over my paygrade. :) Glad to see it though. Get to learn something new. Thanks. Jim


-----Original Message-----
From: Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com>
To: smf_addin <smf_addin@yahoogroups.com>
Sent: Sun, Apr 24, 2016 5:10 pm
Subject: Re: [smf_addin] Finding most recent dividend increase

If you dividends are in the range E6:L6, array-enter this formula to find the earliest column with an increase:

=MIN(IF(E6:K6>F6:L6,COLUMN(E6:K6),999))

Then compute the increase with this, referring to that value:

=IF(M6=999,NA(),INDEX(6:6,M6)/INDEX(6:6,M6+1)-1)

Ticker

Past1div

Past2div

Past3div

Past4div

Past5div

Past6div

Past7div

Past8div

Column

% Chg



d

0.7000

0.6480

0.6480

0.6480

0.6480

0.6000

0.6000

0.6000

5

0.080247



mcd

0.8900

0.8900

0.8500

0.8500

0.8500

0.8500

0.8100

0.8100

6

0.047059



xxx

1

1

1

1

1

1

1

1

999




On Sun, Apr 24, 2016 at 9:59 AM, Jose Jacob pepecan47
​...wrote:

This is a question more related to Excel than SMF, but since there are gurus here I will ask anyway.

I have the following spreadsheet with the last 8 dividends. How can I find the most recent dividend increase?

Ticker Past1div Past2div Past3div Past4div Past5div Past6div Past7div Past8div
d 0.7000 0.6480 0.6480 0.6480 0.6480 0.6000 0.6000 0.6000
mcd 0.8900 0.8900 0.8500 0.8500 0.8500 0.8500 0.8100 0.8100

For D, (0.7000 - 0.6480) / 0.6480 = 8.02%
for MCD, (0.8900 - 0.8500) / 0.8500 = 4.71%

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

2a

smfGetGuruFocusItem() Not Working for Quarterly Data

Sun Apr 24, 2016 11:16 am (PDT) . Posted by:

danseiler2000

I had been using the smfGetGuruFocusItem(Ticker,53,"Q",0) function to retrieve quarterly data, but it has recently been returning "Error". Did the website change and cause the function to no longer work?

Thanks,
Daniel

Reply to sender . Reply to group . Reply via Web Post . All Messages (3) . Top ^

2b

Re: smfGetGuruFocusItem() Not Working for Quarterly Data

Sun Apr 24, 2016 1:56 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

You probably just need to grab the latest version of the add-in.

GuruFocus dropped the number of quarters from 9 to 5, so I had to update
the add-in code to account for that.

The change was made on 2016-04-09.

I also had to make another change on 2016-04-19, as they changed the annual
display as well.

See:

http://ogres-crypt.com/SMF/Works-In-Progress/0-Change-log.html

On Sun, Apr 24, 2016 at 11:16 AM, danseiler2000@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> I had been using the smfGetGuruFocusItem(Ticker,53,"Q",0) function to
> retrieve quarterly data, but it has recently been returning "Error". Did
> the website change and cause the function to no longer work?
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (3) . Top ^

2c

Re: smfGetGuruFocusItem() Not Working for Quarterly Data

Sun Apr 24, 2016 5:03 pm (PDT) . Posted by:

danseiler2000

Thank you for your hard work on this project.

Reply to sender . Reply to group . Reply via Web Post . All Messages (3) . Top ^

For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF

Visit Your Group

• New Members 17

• Privacy • Unsubscribe • Terms of Use