Kamis, 19 April 2012

[smf_addin] Digest Number 2181

Messages In This Digest (9 Messages)

Messages

1a.

Re: EPS element numbers,  for the last 5 years?

Posted by: "MikeM" mikemcq802@yahoo.com   mikemcq802

Wed Apr 18, 2012 5:00 am (PDT)



You can see all the element numbers available by looking in the directory you installed SMF. There is a filed called RCHGetElementNumber-Element-Definitions.xls. For example, see number 5896 for AdvFN's Annual Income Statement Dividends Paid Per Share FY1.

For AdvFN financial statement data, I find it easier to use the GetAdvFNElement function of the add-in.

Example for IBM latest fiscal year:

=smfGetAdvFNElement("IBM","A",1,">Income Statement",">Dividends Paid Per Share")

The ">" signs in the search text are because these are search phrases for html - not just what you see on the screen. It helps to make sure you get the right item. You can search this board for previous comments and issues with search phrases for AdvFN.

--- In smf_addin@yahoogroups.com, "rr76012" <rr76012@...> wrote:
>
> Hello Mike,
>
> Do I use =rchgetelementnumber function to get the dividends?
>
> What element numbers do I use for the last 5 years dividends?
>
> Mike,
> Thank you,
>
> --- In smf_addin@yahoogroups.com, "MikeM" <mikemcq802@> wrote:
> >
> > For dividends, in addition to Randy's suggestion you can use the AdvFN elements.
> >
> > AdvFN shows dividends per share in the Income Statement section. They also provide the payout ratio, however, I've had some odd results with their calculation. So, I recommend dividing this by the EPS to derive the payout ratio yourself.
> >
> >
> > --- In smf_addin@yahoogroups.com, "rr76012" <rr76012@> wrote:
> > >
> > > I am using =RCHGetElementNumber($B$2,333), element #'s 333 thru 342 to get the last 10 years eps for a stock.
> > >
> > > I keep getting an "error" message in excel. Do I need to do any addin updating?
> > >
> > > Randy, what am I doing wrong.
> > >
> > > Also, is there any element numbers for the last few 5 years of dividends or payout?
> > >
> > > Thanks,
> > >
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (9)
1b.

Annual EPS and Dividends and Payout for the last 5 years?

Posted by: "rr76012" rr76012@yahoo.com   rr76012

Wed Apr 18, 2012 7:02 am (PDT)



Mike,

Thanks, I loved the specific directions you gave; I can now follow and implement your strategy.

I only sort of know how to use the =rchgetetlementnumber function, because it is so easy and straightforward. Beyond that it gets more complicated and difficult to implement. This will be my first time to use another function.

After reading all the messages on dividends, I know many people on this forum will be able to use and implement your specific directions on finding and using annual dividends.

And thanks for the info on the payout being off and best to calculate with annual dividend divided by annual eps.

Mike again thanks for everything,I plan on using your dividend and payout strategy today.

--- In smf_addin@yahoogroups.com, "MikeM" <mikemcq802@...> wrote:
>
> You can see all the element numbers available by looking in the directory you installed SMF. There is a filed called RCHGetElementNumber-Element-Definitions.xls. For example, see number 5896 for AdvFN's Annual Income Statement Dividends Paid Per Share FY1.
>
> For AdvFN financial statement data, I find it easier to use the GetAdvFNElement function of the add-in.
>
> Example for IBM latest fiscal year:
>
> =smfGetAdvFNElement("IBM","A",1,">Income Statement",">Dividends Paid Per Share")
>
> The ">" signs in the search text are because these are search phrases for html - not just what you see on the screen. It helps to make sure you get the right item. You can search this board for previous comments and issues with search phrases for AdvFN.
>
>
> --- In smf_addin@yahoogroups.com, "rr76012" <rr76012@> wrote:
> >
> > Hello Mike,
> >
> > Do I use =rchgetelementnumber function to get the dividends?
> >
> > What element numbers do I use for the last 5 years dividends?
> >
> > Mike,
> > Thank you,
> >
> > --- In smf_addin@yahoogroups.com, "MikeM" <mikemcq802@> wrote:
> > >
> > > For dividends, in addition to Randy's suggestion you can use the AdvFN elements.
> > >
> > > AdvFN shows dividends per share in the Income Statement section. They also provide the payout ratio, however, I've had some odd results with their calculation. So, I recommend dividing this by the EPS to derive the payout ratio yourself.
> > >
> > >
> > > --- In smf_addin@yahoogroups.com, "rr76012" <rr76012@> wrote:
> > > >
> > > > I am using =RCHGetElementNumber($B$2,333), element #'s 333 thru 342 to get the last 10 years eps for a stock.
> > > >
> > > > I keep getting an "error" message in excel. Do I need to do any addin updating?
> > > >
> > > > Randy, what am I doing wrong.
> > > >
> > > > Also, is there any element numbers for the last few 5 years of dividends or payout?
> > > >
> > > > Thanks,
> > > >
> > >
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (9)
2.

Use of SMF Formulas in Excel 2010 Tables

Posted by: "PLA" optionzz@gmail.com   option2z

Wed Apr 18, 2012 2:22 pm (PDT)



Perhaps this will help some others, and maybe Randy can comment.

I have a Table (this is a named structure in 2010) that has summaries of
strategy performance simulations for the 102 stocks that make up Dividend
Champions. It had three columns that needed SMF formulas to complete the
102 rows of data for each column. As a test, I entered a formula in column
C to get the company name. I entered it by typing in
=RCHGetElementNumber(
Then moused to Col B which has the ticker value (ABM). Then I typed in
13862, the code for the company name.
To my surprise, the formula looked like this: [@Ticker], 13862 )
To my even greater surprise, the whole column filled out with the company
names for the 102 tickers in column B. It happened VERY fast, much like I
had entered a CSE array formula.but I hadn't. I did the same thing for the
sector and industry columns. They filled out very fast as well.
=RCHGetElementNumber( [@Ticker], 13865 )
=RCHGetElementNumber( [@Ticker], 13867 )
The whole thing completed in a second or two!
Another surprise was when I added an additional ticker, col B, in the first
empty row below the table, it instantly got added to the table AND the three
columns populated in a second or so without me extending the formulas. This
is quick and easy for some sorts of analysis. I'd always avoided tables
because in earlier versions of Excel, there had been lots of "problems."

I'm pondering how the "table logic" actually works. I entered exactly one
formula in the first row of the table, for each column. It was
automatically extended the length of the table, and the values appeared
quickly. Might be a useful technique for some of us?

Here's what the results of single entry formula (without CSE) looks like in
this table (for those who can read imbedded images):

And with formulas displayed.

Pete A

Amateurs practice until they get it right, experts practice until they can't
get it wrong
(Faith Duck)

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (1)
3a.

Using SMF Add-in over multiple worksheets

Posted by: "thomas91112" thomas91112@yahoo.com   thomas91112

Wed Apr 18, 2012 4:22 pm (PDT)



I currently have multiple Excel workbooks (one workbook for each stock plus some analytics) using RCHGetYahooHistory and RCHGetYahooQuotes to retrieve High, Low and Close values. Would like to combine these numerous workbooks into a single workbook but with separate worksheets, one for each of the current workbooks - that way each worksheet will contain the price history and analytics for each stock.

As individual workbooks the data updated just fine, but when I combined these workbooks as described above, have begun encountering problems with the data when updating. Sometimes data items would not fully update leaving off the last two months of data but it seemed random, you never know when a worksheet will update fully...have to keep hitting ForceReCalc until the full update occurs.

Is there a problem using SMF throughout multiple worksheets?

Thanks
Mike

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
3b.

Re: Using SMF Add-in over multiple worksheets

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed Apr 18, 2012 5:01 pm (PDT)



Rarely is anything I do beyond a single worksheet, so I can't really
comment with any authority. But on the few I do have that are multiple
worksheets, I've not run into such problems.

On Wed, Apr 18, 2012 at 4:22 PM, thomas91112 <thomas91112@yahoo.com> wrote:

> I currently have multiple Excel workbooks (one workbook for each stock
> plus some analytics) using RCHGetYahooHistory and RCHGetYahooQuotes to
> retrieve High, Low and Close values. Would like to combine these numerous
> workbooks into a single workbook but with separate worksheets, one for each
> of the current workbooks - that way each worksheet will contain the price
> history and analytics for each stock.
>
> As individual workbooks the data updated just fine, but when I combined
> these workbooks as described above, have begun encountering problems with
> the data when updating. Sometimes data items would not fully update
> leaving off the last two months of data but it seemed random, you never
> know when a worksheet will update fully...have to keep hitting ForceReCalc
> until the full update occurs.
>
> Is there a problem using SMF throughout multiple worksheets?
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
4a.

Re: RCH GET History Question.

Posted by: "clifford.dowler" clifford.dowler@yahoo.com   clifford.dowler

Wed Apr 18, 2012 6:11 pm (PDT)



Thanks Randy for your input. Copying the formula over would be what I always do but these CSE formulas seem a little tricky. If I just get one price for one date, then I don't need to hold ctl+shift down to enter it and the cells copy perfectly with the ticker symbol changing to the reference at the top of each column.

However, when I have used CSE to pull a range of dates and thus get a long list down the column of stock prices, it's that array formula I am having problems copying.

When I copy it, the first column works and all the rest of the columns show the formula but produce no data. And when I click on say the third column over, the formula in the cell still has the original column listed as the cell reference without it being set to the reference at the top of the column. It's behaving as if I had set the ticker symbol to an absolute cell ($x$1) which I didn't.

My thinking is this might have to do with your macro that not only pulls one historical piece of data, but you can ask it to pull a whole array of data across multiple columns. Somehow I think it is getting confused when I am simply asking for one piece of data referencing a different ticker symbol in each column.

I did figure a work around though that is cumbersome but works. If you just get the first date in the series by hitting enter rather than CSE, then you can copy that unbracketed formula for all the columns and the cell reference updates automatically. After you do that, if you select the orgiginal formula and all the cells down to the beginning date you want to set up the array area, all you have to do is CSE and all the data populates. The only challenge is you have to do this manually column by column so I just popped in a good music CD and like a zombie cranked it out.

Surprisingly since you are only extracting historical data for one symbol at a time, Yahoo handled it fine and I didn't lose any prices. Also once I had all the data extracted manually, I just copied the results "value only" into another sheet to do the analysis. I haven't even tried to open the original sheet knowing it would take hours for the macro to update the data.

So in a nutshell my question is, is there some special way to copy an array formula {} thats array range is only one column wide across multiple columns updating the cell reference at the top of each column? Copying a simple formula () works, but I have problems with the bracketed kind.

Thanks Cliff

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> On Sat, Apr 14, 2012 at 2:13 PM, clifford.dowler
> <clifford.dowler@...>wrote:
>
> > I have successfully retrieved the closing price "C" from one date to
> > another for a particular stock down a column after selecting the cell range
> > and CSE closing it. I can also re-input the same formula and CSE close it
> > to manually get the same data for another stock in another column.
> >
> > My question is, presuming I have a stock symbol at the top of the column
> > and I have 1000 symbols across the columns of the sheet, how do I mass copy
> > the formula to retrieve that specific data item for all stock symbols in
> > the range?
> >
>
> Just copy it from one column to all the others.
>
> However, I can't think of a good reason to get that many different sets of
> historical quotes. If you're doing some type of screening, you'd be better
> off using something like StockFetcher. Yahoo hasn't been all that reliable
> lately, so I am thinking at least a few of those 1000 symbols are going to
> error out. And that means it will take a LONG time to get the data. And
> some of it will not be there.
>
>
> > When I try to set the ticker data item to a range of cells, I get the
> > dreaded #Value# error, or if I simply copy the formula, I get the data in
> > the first column, blanks in the rest, and the formula that is copied does
> > not change the ticker variable to the cell at the top of the column.
> > Instead it retains the ticker cell reference of the first column.
> >
>
> The function only retrieves data for a single ticker symbol.
>
> The cell reference should change when you copy the formula, unless you're
> using an absolute reference.
>
>
> > Ultimately, what i want to do is to get the closing prices of a group of
> > stocks for say daily data going back 10 years and perhaps also get any
> > dividends paid. Still the work around would be to get just the closing
> > prices in one table and the dividends in another,and then combine the two
> > tables to get total return for any given stock over a specific period.
> >
>
> If you're doing total returns, you should be using adjusted closing prices.
> Their changes account for dividends.
>
> Getting total returns for 1000 stocks is this manner is NOT a trivial task.
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (6)
4b.

Re: RCH GET History Question.

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed Apr 18, 2012 6:23 pm (PDT)



I have no trouble at all copying array-entered formulas. But you do need to
copy and then paste the range. You can't do it cell by cell.

If I have an array-entered formula in a range within a single column, I
generally will just copy the column itself.

On Wed, Apr 18, 2012 at 6:11 PM, clifford.dowler
<clifford.dowler@yahoo.com>wrote:

>
> So in a nutshell my question is, is there some special way to copy an
> array formula {} thats array range is only one column wide across multiple
> columns updating the cell reference at the top of each column? Copying a
> simple formula () works, but I have problems with the bracketed kind.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (6)
5a.

Getting Historical Value for a Particular Day

Posted by: "Sudhir Agarwal" Agarwal.sudhir@gmail.com   sudhir_agarwal1

Wed Apr 18, 2012 10:10 pm (PDT)



Hi,

I am looking for downloading the Historical Values for a set of Stock.

I know I can do that by array entering for a particular stock and
replicating the process for all the stocks I want the price For.

Is there a Way by which I can create a scalable process where I get the
Historical Values when I add the stock Symbol.

If someone has worked on that and has a worksheet, that will be of great
help.

Sudhir

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
5b.

Re: Getting Historical Value for a Particular Day

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed Apr 18, 2012 10:35 pm (PDT)



Just use the RCHGetYahooHistory() function with the necessary parameters.
Since you will only have a single data item returned, you don't need to
array-enter the formula.

On Wed, Apr 18, 2012 at 10:09 PM, Sudhir Agarwal
<Agarwal.sudhir@gmail.com>wrote:

> I am looking for downloading the Historical Values for a set of Stock.
>
> I know I can do that by array entering for a particular stock and
> replicating the process for all the stocks I want the price For.****
>
> Is there a Way by which I can create a scalable process where I get the
> Historical Values when I add the stock Symbol.****
>
> If someone has worked on that and has a worksheet, that will be of great
> help.****
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
Recent Activity
Visit Your Group
Ads on Yahoo!

Learn more now.

Reach customers

searching for you.

Yahoo! News

Get it all here

Breaking news to

entertainment news

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Need to Reply?

Click one of the "Reply" links to respond to a specific message in the Daily Digest.

Create New Topic | Visit Your Group on the Web
Diposting oleh EXCEL FEED LEARNING di 01.12

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)