My Photo
My investing philosophy mostly centers around the Value discipline and GARP- Growth at a Reasonable Price. This blog includes commentary on market conditions as well as fundamental analysis of specific companies. Graduated from Rhodes College with a degree in Business with concentration in Finance & Marketing. Currently working on obtaining the CFA designation. Previously worked in Mortgage Trading for a major bank. Use MS Excel extensively for developing investment models, notably valuation models based on DCF methods.
Showing posts with label Excel. Show all posts
Showing posts with label Excel. Show all posts

Thursday, January 31, 2008

Importing Financial Web Data into Excel, part 2

MS Excel is an excellent tool for performing fundamental analysis. One problem that arises is collecting the data in a spreadsheet so that analysis can be applied. Entering by hand is painstakingly tedious. Copy-paste works if it’s a possibility, yet reformatting can be a headache. An incredible tool that imports financial data from the web into MS excel is the Stock Market Function Add-in developed by Randy Harmelink.

See my previous article

The add-in can be retrieved from his yahoo group. Installation is simple. Create a folder called SMF Add-in in your program files folder on the c: drive. Download the add-in from Mr. Harmelink’s site and save inside that folder you just created. Then launch Excel, go to tools-> add-ins-> and browse for the add-in and install it, more detailed instructions are available at the yahoo group. The links are listed below.

Mr. Harmelink’s Excel Stock Market Function Add-in is located in the files section on this website:
It’s a yahoo group where members discuss ideas and share spreadsheets.

Additionally, Models I have created that use this add-in are posted in the files section at this yahoo group:

At this website you can find various spreadsheets I have created that automatically import company data. The SMF Add-in can be downloaded from here as well. Just look in the files section.

A particular model I want to highlight is the EPS_Estimates_MultiSource_v5 that you can download from the files section.

Following trends in analysts’ EPS estimates can be a valuable tool. Research suggests that companies experiencing downward revisions will likely receive more in the future. This spreadsheet shows the trends in EPS revisions. In addition, the model tracks historical EPS announcements. This can be valuable information too, especially if the company has been surprising on the upside and estimates have just started to be revised upward. This may depict a situation where The Street hasn’t fully grasped the potential earnings growth. This may lead to an undervalued situation if there is evidence that growth is underestimated and not priced into the stock.

Download the SMF Add-in and check out the EPS Estimates model.

Saturday, August 11, 2007

Using Excel to Import Financial Data from the Web

There truly is no better program when it comes to crunching financial data than MS Excel. It is very simple to build valuation and analysis models by creating spreadsheet formulas to perform calculations on a firm’s financial data. Getting the data into Excel can be a problem especially if it has to be manually keyed-in. That can become very time consuming and transcribing figures manually is very error prone. With the use of VBA macros, Excel can import specified data automatically from the web. This is an extremely powerful tool for researching investments. Randy Harmelink developed an Excel Add-in that imports a whole myriad of stock data. The best part is that anyone can download and use.

Professionals on Wall Street have had this type of automated analysis for years through such services as Bloomberg. Yet, for the individual investor not willing to shell out $2k a month, he/she resorted to a legal pad and financial calculator. Every investor needs to do their own math so that they get a grasp to the larger story that the numbers tell.

Mr. Harmelink’s Excel Stock Market Function Add-in is located in the files section on this website:

It’s a yahoo group where members discuss ideas and share spreadsheets.
Additionally, Models I have created that use this add-in are posted in the files section at this yahoo group:

I recommend visiting these sites and downloading/installing the add-in and then look at the spreadsheets already created by group members.

When I conduct my analysis of a given stock, I usually apply three valuation methodologies

1) 5yr Forecasted EPS / Exit Multiple
2) 3-Stage Discounted Cash Flow Valuation
3) Relative Price-Multiple Valuation

For this post, I will discuss the first model and will discuss the others in subsequent posts.

The EPS / PE model doesn’t require much data to calculate.
-Historical EPS
-Historical DPS
-EPS estimates (current yr and next yr)
-Projected 5yr EPS growth rate
-Stock’s Beta

First, I analyze historical EPS growth to see if it’s accelerating, decelerating, or constant. I compare this to the analyst’s forecasts and make any adjustments to arrive at what I think is a reasonable growth rate.
Then I take last year’s EPS and project it out 5 years- (EPS * (1+g)). If Consensus estimates are available for this year and next year I will use those figures for year 1 & 2 EPS, and the apply my forecasted growth rate for years 3-5. At the end of year 5, I multiple EPS by a P/E ratio that is reasonable- Industry average etc. That will give the projected stock price in year 5. To calculate today’s value we must discount Year 5’s price back to the present using the required rate of return on equity. If the stock pay’s dividends, those too must be discounted back to the present and added to the PV of the stock price.

Using the Excel Stock Market Add-in all the required inputs can be downloaded automatically in excel. I have created a spreadsheet called Cash Flow Model that includes the above calculation that can be found at the locations mentioned above.

The primary drivers are the projected growth rate and exit multiple so it is crucial that these assumptions represent the most accurate predictions as possible. A useful strategy is to “invert” the model, meaning finding the input assumptions that equate model value to market value. For example, using the consensus growth rate and then finding the exit multiple value that returns the current price. Alternatively, the user could input a desired multiple then find the growth rate implied by the Market price.

The usefulness of valuation techniques is not to arrive at a single valuation as calculated by the model, yet to formulate a range of values for various assumptions. For instance, the assumptions that are implied by the price and valuations for pessimistic / optimistic scenarios. This provides the practitioner with an understanding of what must occur to support a given valuation.

I will discuss methods for estimating growth rates and exit multiple in upcoming posts.

Share it

Contact Financial Alchemist

Email: gmail-turley.muller
Phone: (901) 213-8875