2 New Messages
Digest #2730
Messages
Sat Aug 10, 2013 9:26 am (PDT) . Posted by:
"Jacob Jose" pepecan47
Hi Randy, I don't know how to tell you how grateful I am.
I am using MMM as a ticker. When I enter in B2 the formula =MIN(IF(B12:B142>B13:B143,ROW(B12:B142),999))
I get #VALUE!
I don't undertand what you mean by "needs to be array-entered". Are you referring to the cell B2?
I use =RCHGetYahooHistory(A2,,,,,,,"v") and it is array entered. Is this giving me the dividends with stock splits?
Regards,
Jose L. Jacob
________________________________
From: Randy Harmelink <rharmelink@gmail.com >
To: smf_addin@yahoogroups.com
Sent: Saturday, August 10, 2013 1:54:25 AM
Subject: Re: [smf_addin] Getting last dividend increase from yahoo history
Here's what I get:
B2: =MIN(IF(B12:B142>B13:B143,ROW(B12:B142),999))
C2: =INDEX(B:B,B2)/INDEX(B:B,B2+1)-1
D2: =INDEX(A:A,B2)
E2: =SUMPRODUCT(--(A12:A143>A12-350),--(LEN(A12:A143)>1))*INDEX(B:B,B2)/RCHGetYahooQuotes(A2,"l1")
B2 finds the list time an increase occurred, and needs to be array-entered.
C2 divides the dividend from that date by the previous, although I think it really should be the projected 12-month dividend over the prior 12-month dividend.
D2 find the date of the last increase.
E2 finds out how many dividend payments were paid in the last year and multiplies it against the latest paid dividend.
However, do note that special dividends will throw all of that out of whack.
Are the dividends adjusted for stock splits?
On Fri, Aug 9, 2013 at 9:25 PM, Jacob Jose <pepecan47@yahoo.ca > wrote:
>
>I have an array defined A11:B11 to A143:B143 with the formula =RCHGetYahooHistory(A2,,,,,,,"v")
>How can I get:
>
>- the last dividend increase percentage and the date?
>- The forward dividend yield? (the frequency of dividends could be monthly, quarterly, etc)
>
>For example:
>
>KEY.TO last dividend increase: 5.88% date: 2012-11-20 forward yield: 0.18*12/59.84=3.61%
>MMM 7.63% 2013-02-13 0.635*4/118.31=2.15%
>ADI 13.33% 2013-02-27 0.34*4/49=2.78%
I am using MMM as a ticker. When I enter in B2 the formula =MIN(IF(B12:
I get #VALUE!
I don't undertand what you mean by "needs to be array-entered"
I use =RCHGetYahooHistory
Regards,
Jose L. Jacob
____________
From: Randy Harmelink <rharmelink@gmail.
To: smf_addin@yahoogrou
Sent: Saturday, August 10, 2013 1:54:25 AM
Subject: Re: [smf_addin] Getting last dividend increase from yahoo history
Here's what I get:
B2: =MIN(IF(B12:
C2: =INDEX(B:B,B2)
D2: =INDEX(A:A,B2)
E2: =SUMPRODUCT(
B2 finds the list time an increase occurred, and needs to be array-entered.
C2 divides the dividend from that date by the previous, although I think it really should be the projected 12-month dividend over the prior 12-month dividend.
D2 find the date of the last increase.
E2 finds out how many dividend payments were paid in the last year and multiplies it against the latest paid dividend.
However, do note that special dividends will throw all of that out of whack.
Are the dividends adjusted for stock splits?
On Fri, Aug 9, 2013 at 9:25 PM, Jacob Jose <pepecan47@yahoo.
>
>I have an array defined A11:B11 to A143:B143 with the formula =RCHGetYahooHistory
>How can I get:
>
>- the last dividend increase percentage and the date?
>- The forward dividend yield? (the frequency of dividends could be monthly, quarterly, etc)
>
>For example:
>
>KEY.TO last dividend increase: 5.88% date: 2012-11-20 forward yield: 0.18*12/59.84=
>MMM 7.63% 2013-02-13 0.
>ADI 13.33% 2013-02-27 0.34*4/49=2.
Sat Aug 10, 2013 12:03 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Yes, the formula in B2 needs to be array-entered into the single cell. It
is an array-processing formula -- note the B12:B142 and B13:B143 ranges.
When the formula is array entered, it will cycle through those two arrays,
doing all the necessary comparisons to see if the dividend changed. That
is, B12 vs B13, then B13 vs B14, ..., and finally B142 vs B143. If the
comparison is true it sets the result of that comparison to the row number.
Otherwise, is sets the result to 999. Then the MIN() function finds the
minimum row number, which would be the most recent dividend change.
If you don't array-enter it, it will give you a #VALUE error.
Yes, the RCHGetYahooHistory() function is also array-entered. But over a
range. And for a different purpose.
If you really want to see what is happening internally, array-enter the
inner part of the formula:
=IF(B12:B142>B13:B143,ROW(B12:B142),999)
...over the C12:C142 range. That will show you the result of each
individual comparison the formula is doing, showing either 999 or the row
number, depending on whether there was a dividend increase. But I just want
a single value, so I use a MIN() function on that array. Since the MIN()
returns a single value, I only need to enter it into a single cell.
However, I still need the function to do the array processing, so I need to
array-enter it into that single cell.
Date Dividends Increase? 2013-05-22 $0.64 999 2013-02-13 $0.64 13
2012-11-20 $0.59 999 2012-08-22 $0.59 999 2012-05-16 $0.59 999 2012-02-15
$0.59 17 2011-11-22 $0.55 999 2011-08-17 $0.55 999 2011-05-18 $0.55 999
2011-02-16 $0.55 21 2010-11-17 $0.53 999 2010-08-18 $0.53 999 2010-05-19
$0.53 999 2010-02-17 $0.53 25 2009-11-18 $0.51 999 2009-08-19 $0.51 999
Array-entered formulas of this type can be extremely powerful. And
frustrating.
On Sat, Aug 10, 2013 at 9:26 AM, Jacob Jose <pepecan47@yahoo.ca > wrote:
>
> Hi Randy, I don't know how to tell you how grateful I am.
>
> I am using MMM as a ticker. When I enter in B2 the formula
> =MIN(IF(B12:B142>B13:B143,ROW(B12:B142),999))
> I get #VALUE!
> I don't undertand what you mean by "needs to be array-entered". Are you
> referring to the cell B2?
> I use =RCHGetYahooHistory(A2,,,,,,,"v") and it is array entered. Is this
> giving me the dividends with stock splits?
>
>
is an array-processing formula -- note the B12:B142 and B13:B143 ranges.
When the formula is array entered, it will cycle through those two arrays,
doing all the necessary comparisons to see if the dividend changed. That
is, B12 vs B13, then B13 vs B14, ..., and finally B142 vs B143. If the
comparison is true it sets the result of that comparison to the row number.
Otherwise, is sets the result to 999. Then the MIN() function finds the
minimum row number, which would be the most recent dividend change.
If you don't array-enter it, it will give you a #VALUE error.
Yes, the RCHGetYahooHistory(
range. And for a different purpose.
If you really want to see what is happening internally, array-enter the
inner part of the formula:
=IF(B12:B142>
...over the C12:C142 range. That will show you the result of each
individual comparison the formula is doing, showing either 999 or the row
number, depending on whether there was a dividend increase. But I just want
a single value, so I use a MIN() function on that array. Since the MIN()
returns a single value, I only need to enter it into a single cell.
However, I still need the function to do the array processing, so I need to
array-enter it into that single cell.
Date Dividends Increase? 2013-05-22 $0.64 999 2013-02-13 $0.64 13
2012-11-20 $0.59 999 2012-08-22 $0.59 999 2012-05-16 $0.59 999 2012-02-15
$0.59 17 2011-11-22 $0.55 999 2011-08-17 $0.55 999 2011-05-18 $0.55 999
2011-02-16 $0.55 21 2010-11-17 $0.53 999 2010-08-18 $0.53 999 2010-05-19
$0.53 999 2010-02-17 $0.53 25 2009-11-18 $0.51 999 2009-08-19 $0.51 999
Array-entered formulas of this type can be extremely powerful. And
frustrating.
On Sat, Aug 10, 2013 at 9:26 AM, Jacob Jose <pepecan47@yahoo.
>
> Hi Randy, I don't know how to tell you how grateful I am.
>
> I am using MMM as a ticker. When I enter in B2 the formula
> =MIN(IF(B12:
> I get #VALUE!
> I don't undertand what you mean by "needs to be array-entered"
> referring to the cell B2?
> I use =RCHGetYahooHistory
> giving me the dividends with stock splits?
>
>
Tidak ada komentar:
Posting Komentar