Hansen Natural (NYSE: HANS) — 06/04/08
Value investing represents a “Tale of Two Cities” — where the stock represents the best and the worst of times. On the one hand, we seek a company that is performing competently and growing. On the other hand, we seek a stock price that is in the toilet. At its core, value investing is a disconnect between the bipolar psychology of Mr. Market and the intrinsic value of the company. The efficient market theorist would have us believe this is impossible, but the market’s volatility in the absence of actionable information suggests otherwise. Hansen Natural (NYSE: HANS) seems a case in point — where the company is doing well but the stock price has been unreasonably hammered (thank you, Shorts).

I don’t know whether the stock was overvalued at its peak. That is not the subject of this analysis. I have every reason to believe, however, that it is undervalued at today’s price, and that is the subject of this article.
Company Description
SEC filing description of the company:
Hansen Natural Corporation, through its subsidiaries, engages in the development, marketing, sale, and distribution of beverages in the United States and Canada. It offers natural sodas, fruit juices and juice drinks, energy drinks and energy sports drinks, fruit juice smoothies and functional drinks, non-carbonated ready-to-drink iced teas, lemonades, juice cocktails, and children’s multi-vitamin juice drinks. The company also provides energy drinks under the Monster Energy, Lost Energy, Joker Mad Energy, Unbound Energy, and Ace brand names, as well as the Rumba brand energy juice. In addition, it markets and sells Java Monster non-carbonated dairy-based coffee drinks; natural sodas, premium natural sodas with supplements, organic natural sodas, seltzer waters, sports drinks, and energy drinks the under Blue Sky brand name; and non-carbonated lightly flavored energy waters under the Hansen’s brand name. Further, the company provides vitamin and mineral drink mixes in powdered form under the Fizzit brand name. Its customers include retail grocery and specialty chains, wholesalers, club stores, drug chains, mass merchandisers, convenience chains, full service beverage distributors, health food distributors, and food service customers. The company was founded in 1985 and is based in Corona, California.
Fundamental Overview
The company enjoys a strong financial posture. Yahoo Finance reports the following:
The forward price-earnings multiple is expected to come in at 14.02, with a price-to-earnings-to-growth (PEG) ratio significantly below 1.0. It should be noted that the price to sales and the price-to-book values exceed the normal value investing thresholds. In other words, Hansen is not undervalued based on these metrics.
The profitability and management effectiveness results, on the other hand, are exceptional — especially the return on equity, which exceeds 45%.
The key figures in the previous graphic are the year-over-year results for revenues and earnings, posting 27.9% and 42.6%, respectively. While the core data provided (below) will focus on the year-over-year results, these figures provide shorter-term investors with an indication that earnings and revenues are growing intra-year.
The company’s total debt/equity ratio, at 0.001, is abundantly healthy, and the current ratio at 3.966 provides our first indication that the company is an unlikely bankruptcy candidate. This is confirmed by the Altman-Z Score:
At 27+, the company significantly exceeds the 1.81 threshold indicating a bankruptcy threat, to say nothing of the top-end grey-zone figure of 3.0.
The short ratio of 4.1 (25.5% of the float) represents an increase of shares short from 17.8 6 million last month to 19.9 6 million as of May 12. This serves to explain the recent price decline in the stock. To fully understand this, it is helpful to recognize that insiders accounted for 16.99% and institutions accounted for a further 88% of the outstanding shares. Therefore, a relatively small number of shorts have the ability to move the stock price disproportionately on any given day. Neither, insiders nor institutional holders are known for day-trading. Our goal, as value investors, is to allow the market to exercise its depressive psychosis to our benefit — providing buying opportunities that would not otherwise be available if the market were truly efficient.
Financial Analysis
Turning to the 10K figures:
Gross Profit.
Revenues have been parabolic, and gross profit exceeds cost of goods sold.
Operating Expenses
The decline of sales, general, and administrative expenses in 2006 and the commensurate rise in “other” represents a change in accounting methods. The increase in this category, however, is consistent with the increase in sales. For those who require further evidence, the company describes their allocation of capital as follows:
Operating Expenses. Total operating expenses were $237.0 million for the year ended December 31, 2007, an increase of approximately $79.0 million or 50.0% higher than total operating expenses of $158.0 million for the year ended December 31, 2006. Total operating expenses as a percentage of net sales was 26.2% for 2007, slightly higher than 26.1% for 2006. The increase in operating expenses in dollars was partially attributable to increased out-bound freight and warehouse costs of $8.7 million primarily due to increased volume of shipments, increased expenditures of $18.4 million for sponsorships and endorsements, increased expenditures of $10.3 million for commissions and royalties, increased expenditures of $4.9 million for sampling programs, increased payroll expenses of $12.1 million, increased expenditures of $8.6 million for professional services costs, including legal and accounting fees, and increased expenditures of $2.5 million relating to the costs associated with terminating existing distributors. Included in legal and accounting fees are costs of $9.8 million (net of $2.5 million insurance reimbursements) in connection with our special investigation of stock option grants and granting practices, related litigation and other related matters. Total operating expenses, exclusive of expenditures of $15.3 million and $12.7 million for 2007 and 2006, respectively, attributable to the costs associated with terminating existing distributor and exlcusive of expenditures of $9.8 million (net of 2.5 million in insurance reimbursements) and $3.8 million for 2007 and 2006, respectively in connection with our special investigation of stock option grants and granting practices, related litigation and other related matters, as a percentage of net sales, were 23.4% for both 2007 and 2006.
Taxes
Taxes paid are in-line with expectations and growing at a rate below earnings.
Cash Flows From Operations
This is as expected; although, it did marngally separate from net income in 2006. All other contributors were not material.
Cash From Investments
Cash flows used in investing activities – Net cash used in investing activities was $194.7 million for the year ended December 31, 2007, as compared to $95.2 million used in investing activities in the comparable period in 2006. For 2007, cash used in investing activities was primarily attributable to purchases of short- and long-term investments, particularly available-for-sale and held-to-maturity investments. Cash provided by investing activities was primarily attributable to sales and maturities of held-to-maturity and available-for-sale investments. For both periods, cash used in investing activities included the acquisitions of fixed assets consisting of vans and promotional vehicles and other equipment to support our marketing and promotional activities, … .
Cash from Financing
Exposure to Financial Liquidity Crisis
The company does have exposure to the recent financial-market liquidity “crisis.”
At December 31, 2007, we had $12.4 million in cash and cash equivalents and $290.2 million in short- and long-term investments.
Their exposure to auction rate securities (ARS) has been shifted from short-term to long-term. At $237 million, operating expenses are more than covered by gross profits of more than $468 million (for a 51.7% gross operating profit as a percentage of net sales). If seeking to determine the impact on capital expenditures, I came up with the following estimates:
These calculations are designed to segregate growth and maintenance capital expenditures as a check against declared depreciation. The net difference over the past decade amounts to less than 1%. With declared cash of over $12 million and a current ratio of 3.966, the company appears to have sufficient working capital to accommodate the recent liquidity issues within the broader financial markets, as indicated by this graph comparing free cash flow is to capital expenditures:
Moreover, free cash flows of $131 million provides complete coverage of current liabilities ($76 million) plus capital expenditures ($4.1 million), combined, with a margin of safety of more than $50 million ($131 – $76 – $4.1).
Assets
Total current assets leveled off from 2006 to 2007, as the company evidently invested in growth through inventories and accounts receivable with a decline in cash and cash equivalents. The cash and equivalents decline was also due to added plant, property, and equipment and the conversion of ARS investments to long-term assets from short-term assets.
In addition to the ARS conversion, the increase in other long-term assets includes expansion of the company’s unique emphasis on trial marketing, which is described in the 10K filing:
Our sales and marketing strategy for all our beverages and drink mixes is to focus our efforts on developing brand awareness and trial through sampling both in stores and at events. We use our branded vehicles and other promotional vehicles at events where we sample our products to consumers. We utilize “push-pull” methods to achieve maximum shelf and display space exposure in sales outlets and maximum demand from consumers for our products, including advertising, in-store promotions and in-store placement of point-of-sale materials and racks, prize promotions, price promotions, competitions, endorsements from selected public and extreme sports figures, coupons, sampling and sponsorship of selected causes such as cancer research and SPCAs, as well as extreme sports teams such as the Pro Circuit – Kawasaki Motocross and Supercross teams, Kawasaki Factory Motocross and Supercross teams, Robby Gordon Racing Team, Kawasaki Factory International Moto GP Team, Kenny Bernstein Drag Racing Team, extreme sports figures and athletes, sporting events such as the Monster Energy® Supercross Series, the Monster Energy® Pro Pipeline surfing competition, Winter and Summer X-Games, marathons, 10k runs, bicycle races, volleyball tournaments and other health and sports related activities, including extreme sports, particularly supercross, motocross, freestyle, surfing, skateboarding, wakeboarding, skiing, snowboarding, BMX, mountain biking, snowmobile racing, etc., and we also participate in product demonstrations, food tasting and other related events. In store posters, outdoor posters, print, radio and television advertising, together with price promotions and coupons, may also be used to promote our brands.
Liabilities
The increase in current liabilities is consistent with the increase in inventories, designed to drive sales expansion.
While current liabilities accounts for the majority of the increase in total liabilities, other long-term liabilities increased from $5.4 million to $20.5 million from 2005 to 2006 and from $20.5 million to $39.6 million from 2006 to 2007. The 10K indicates this is primarily due to a canning contract with Rexam Beverage Can Company, having a maximum $6 million liability. The remainder is primarily attributable to leases:
The $50 million in excess free cash flows (described earlier), should be sufficient to cover the $37.5 million due in the current year. Lease obligations decline thereafter.
Shareholder’s Equity
The company’s strong operating margins are best put into perspective with a chart that compares the growth of liabilities to shareholder’s equity.
Investment Ratios
With each of the investment ratios, we will consider the results from two perspectives.
The first will provide the yearly results, with overlaid process control charts. Process control charts consider the level of performance variation and apply one, two, and three standard deviations above and below the mean. The process control results for the first five years of the 10-year results are not trended, while the results for the most recent five years is. This will allow us to do things. First, I will provide an indication of whether the most recent multi-year performance exceeds the norm by a statistically significant amount. Second, it will allow us to put the most recent year’s performance results into perspective.
The second chart considers the year-over-year rate of growth or decline and contrast it with the company’s long-term rate.
The first item to consider is Owner’s Earnings — a Warren Buffett measure. Owner Earnings adjusts reported earnings for a number non-cash items and maintenance capital expenditures. In the Berkshire filing of 1986, Mr. Buffett describes it as follows:
These represent (a) reported earnings plus (b) depreciation, depletion, amortization, and certain other non-cash charges such as Company N’s items (1) and (4) less ( c) the average annual amount of capitalized expenditures for plant and equipment, etc. that the business requires to fully maintain its long-term competitive position and its unit volume. (If the business requires additional working capital to maintain its competitive position and unit volume, the increment also should be included in ( c).
…
Our owner-earnings equation does not yield the deceptively precise figures provided by GAAP, since( c) must be a guess – and one sometimes very difficult to make. Despite this problem, we consider the owner earnings figure, not the GAAP figure, to be the relevant item for valuation purposes – both for investors in buying stocks and for managers in buying entire businesses. We agree with Keynes’s observation: “I would rather be vaguely right than precisely wrong.”
The most recent owner’s earnings results significantly exceed the range established over the first five years. The 2007 results, which represent an increase over 2006, falls marginally below the trended one standard deviation line, indicating that the most recent results are consistent with the trend and would be expected in 68 percent of yearly results (or more). The year-over-year chart indicates a return to the 50 percent growth range — which significantly exceeds Buffett’s reported preference for growth in excess of 15 percent.
Having provided the logic behind reading these two charts in combination, I will provide similar charts for other investment measures, starting with book value.
Book value follows owner’s earnings for a reason. We expect that owner’s earnings will translate into book value, which should translate into shareholder’s equity. In this case, owner’s earnings translation into book value is exceedingly efficient. In fact, the 2007 book value growth results exceeded the upper one standard deviation line.
And the translation of book value and owner’s earnings into shareholder’s equity has been impressive, as well. Note that the year-over-year growth in the last four years has trounced the 10-year average.
Diluted earnings per share may be getting a head of itself, as it approaches the trended two standard deviation line (96.8 percent of expected events). This is most likely due to the accounting anomalies described by Buffett, where net earnings consider a number of non-cash items that are not germane to investors or the company’s senior leadership. Earnings per share, however, are what uninformed investors use as a measure of company performance, and, if these results revert to the mean over the short term, the stock price may take a temporary hit.
The Securities Market Line chart is similar, in that it considers the stock’s volatility as a measure of whether the equity return exceeds the broader market’s. Stock price volatility, however, is a market circumstance that may have nothing to do with the performance of the company. Nevertheless, when a company’s results are above the securities market line, the investor is rewarded for the stock’s price volatility. In the case of Hansen, the results are exceptional, even through the volatility is well above the norm for the broader market.
Return on invested capital is not a market effect, and the Hansen results are impressive.
Benjamin Graham sought ROIC results for stable and established firms in excess of 6 percent. For all others, he demanded 10 percent. Hansen exceed both in every year for the past decade. Moreover, the most recent results have been above the five-year and ten-year averages.
Stock Valuation
Discounted Cash Flow Analysis
Discounted Cash Flow Analysis projects bankable profits in the future and discounts them back to today’s dollars. DCF is only as good as the projections, however. Nevertheless, it provides a basis on which to arrive at an informed estimate of per-share value.
We start with the relevant financial information.
Consider free cash growth rates by calculating the median over rolling five-year and seven-year time frames:
And take the median of the rolling medians. For Hansen, this comes in at a whopping 59 percent growth rate. Is this sustainable? Not according to what I was taught in business school.
So, let’s cut that growth rate by 25 percent over the next three years and reduce it further by 10 percent increments as follows:
This gives us out-year free cash projections of:
We would ordinarily discount these future cash flows back to present dollars at a 10 percent Net Present Value rate. To be exceedingly conservative, however, we will increase that by 50 percent and use 15 percent as the discount rate. This would, however, identify the value of the company without consideration of cash flows beyond twenty years or the accrued value of the company, its savings, or assets today. The be conservative, we will use current shareholder’s equity as the residual value of the company and add it to the net present value of the projected cash flows over the next 20 years.
Divide the results by the current shares outstanding and the company is worth $103.87 per share.
At various points, our calculations have been exceedingly conservative. We reduced expected cash flows by 25 percent over the next three years and made additional 10 percent cuts thereafter. We took the median of the median, rather than employing averages. We used a 15 percent NPV rather than a 10 percent NPV rate. And we used current shareholder’s equity as the residual value of the company beyond the next 20 years. To add another layer of conservatism, we demand a 50 percent discount from the per share value in identifying our maximum purchase price. Half of $103.87 is $51.94. With the stock selling at my personal purchase price of around $32.80 ($32.99 to be precise), the actual discount to intrinsic value ($103.87) was around 68 percent, and the potential gain on the purchase is over 300 percent.
Of course, it may take the market a while to fully value the stock. So, we should consider the compounded rate of return at various time frames.
Even if it takes 5.5 years, the return is above 20 percent and far exceeds the norm for the stock market.
All of this, however, assumes a growth rate that is beyond the maximum sustainable level of 30 percent. To calculate the degree to which the stock us under- or over-valued at different growth rates, we perform sensitivity analysis:
This indicates that the stock is under-valued by 31 percent if the growth rate is 30 percent for the next three years, followed by the same 10 percent reductions, and it is undervalued by 11 percent if that growth rate drops to just 25 percent. Breakeven is a growth rate of 22.73 percent. Growth, therefore, can decline from 59 percent to 22.73 percent before stockholders have overpaid for part ownership of the company, if purchasing the stock at $32.80.
Replication Value
How much would it cost a new competitor to enter the market and achieve the equivalent stature and market share of Hansen? Yahoo Finance lists the book value at $4.83 per share. Using the Graham approach, replication value comes in at something around $5.08. The current stock price is, therefore, selling at 6.46 times its replication value.
This price-to-adjusted-book value is very high, and it would be a concern if Hansen were an operations intensive concern — high in plant, property, and equipment. With an infrastructure value of just $8.6 million out of total assets of $544.6 million, price-to-adjusted-book is not the way to value the company. Clearly, Hansen’s value is driven by the end product and its popularity among consumers.
To get at this, it is necessary to consider the company’s earnings power and franchise value.
Earnings Power and Franchise Value
The earnings power of the company is the company’s earnings divided by its cost of capital plus the difference between cash and debt. To get at this, we must calculate the weighted average cost of capital:
WACC, which considers the cost of debt (and the tax shield benefits) and the expected return on equity financing, should be less than the cash return on invested capital — otherwise, growth degrades the stock value. In this case, CROIC exceeds WACC by nearly one percent, and the entirety of WACC is attributable to equity. This provides a measure comfort for the company and stockholders due to the low level of leverage. We will, nevertheless, use 13.37 percent as WACC for calculation of earnings power.
The table above provides the concluding figures for the calculation of Earnings Power Value, with sensitivity analysis on top and a comparison of EPV to Replication Value (RV) below. As long as EPV exceeds RV, the company can grow profitably and franchise value exists … in the current year, at least. EPV must typically exceed RV for a number of years for this strategic and financial benefit to be reliably reflected in the stock price, according to Professor Bruce Greenwald of Columbia’s Business School. Here are the yearly figures:
And a chart that graphically depicts the relationship:
So, What is this Stock Worth?
My friend Joe Ponzio at Meridian in Chicago has produced a chart that compares two methods of calculating intrinsic value and comparing it with the stock price movements. While I am proud to say that Joe and I worked on this together, the concept, design, and layout are his creation. Both use Discounted Cash Flows as the basis but make marginal changes to the calculations. The first is founded on the original approach created by John Burr Williams and modified by Monish Pabrai, and the second is Joe’s approach — which is typically more generous. The benefit of considering both is that they provide a range of intrinsic value and neither requires more than 10 years of data for calculation of intrinsic value in the early years.
Here are the initial assumptions:
Note that we have reduced the growth rate for the first three years to a more sustainable 30 percent.
By this measure, the stock is significantly undervalued, even though it did get ahead of itself in the middle of 2006 and October of 2007. At 30 percent free cash growth, intrinsic value is $57.79.
If we reduce the initial growth rate to 20 percent, the stock is fairly valued today.
It should be understood that fair value is not a negative if the company is growing and/or generating profits for the owners. That is why our earlier consideration of operating margins and free cash flows was important. In the case of Hansen, where profits are strong and growth is abundant, both provide another measure of comfort for stockholders.
Investment Thesis
Hansen’s principle growth driver is energy drinks which provide a punch to the system that is stronger than coffee. Those wagering on the stock’s decline maintain that energy drinks are a fad and that, with a declining economy, consumers will be less willing to devote dwindling discretionary incomes to energy drinks. In past recessions, however, several themes have emerged which suggest this pessimistic thesis may be wrong.
The US consumer and the US worker are one and the same — with both possessed of the same competitive spirit. We may complain about combating foreign competition on an uneven playing field, but we are loath to exit the stadium of competition. That was our response during the 70s and 80s when the Japanese were purchasing Rockefeller Center and threatening to buy Hawaii, when we were compelled to ramp up our productive infrastructure at the start of World War II, and, more recently, in the face of European economic consolidation and the creation of the EU. I expect the same response to the economic emergence of China, India, Brazil, and Russia. In this more competitive climate, where low wages and cost of production in emerging markets will confront the competitive spirit, practical ingenuity, and efficiency of the US market and workforce, workers will seek to produce more in less time, and, just as the coffee break has been long viewed as a benefit to office productivity, the stronger power of energy drinks will be perceived by workers seeking to retain employment in a down economy as a differential advantage. And this bodes well for Hansen — especially, as our overseas competition does likewise.
My only concern about this thesis is the number of new energy-drink offerings introduced by the company — believing that these may serve to cannibalize Monster (Hansen’s leading product) market share and profitability while increasing the cost of production for this expanding menu of offerings. During a talk with students at the University of Florida, Warren Buffett indicated that Coke is one of the few drink products where the consumer never tires of the taste … and he is right. If viewed in this light, the new offerings by Hansen may well serve to expand market share with limited cannibalization. The customer is unlikely to purchase an other Hansen product in place of Monster if seeking an taste alternative in the first place.
Moreover, product segmentation is a natural and beneficial market progression — seeking to more fully satisfy customers and cultivate buyer loyalty to their preferred brand. Intelligent branding and market segmentation (especially for growing segment categories, such as energy drinks) represent a potential positive. As Hansen seeks to expand market share and consumer loyalty, the franchise value of the company should grow and deepen its moat — barriers to competitor entry –, where share of mind becomes more important than share of market as a predictor of future success.
Conclusion
Hansen is buy below $52 a share and a strong buy below $45.














































Hi Robert,
I’m from the AEO board, I’ve spoke with you a few times before in regards to your DCF models and overall investment analysis process.
Lately I’ve been looking at something we talked about, the Kelly formula. I’ve got some questions about how you calculated it.
I believe this is how you calculated it (ignore the arbitrary probabilities)?
Odds up = (tgt price-current)*.5
Odds down = (current price * .3)*.5
Odds = Odds up – Odds down
Gain = (.9*tgt price) – current price
kelly = odds/gain
The more I think about it, the more this seems wrong to me. If you increase your margin of error on your gain to .8, your expected gain goes down but your optimal kelly weighting goes up? So this method seems incorrect to me…..
I’m not really sure how this formula is equal to the original Kelly formula http://en.wikipedia.org/wiki/Kelly_criterion
Best,
Ben
Ben
June 9, 2008 at 10:28 am
Ben,
Thank you for taking the time to look at the Kelly Formula or Criterion. As mentioned on the board (Yahoo! Finance AEO board), Kelly is anew measure for me and is not one with which I am yet fully comfortable. That comfort level has risen based on comments from folks like you, and I’ll take a closer look at your concerns when I return later this week from a trip to Richmond.
This, of course, raises the question of why include Kelly in a couple of recent postings. For those postings, I changed the formula to be more conservative than the calculations you reference. Specifically, the up-side is the intrinsic value determined by the adjusted discounted cash flow model, which is abundantly conservative because it starts with a 25 percent hair cut from the median five-year and seven-year free cash flow medians, discounts projected future cash flows back to present at a 15 percent NPV (rather than the conventional 10 percent), reduces free cash flow projections for the out years by 10 percent every three or four years during the first decade, and projects free cash flow at a much lower rate for the second decade, and, finally, it takes shareholder’s equity as the terminal value. On the odds down side, I use adjusted book value (replication value) as the maximum down-side price, while retaining the 30 percent odds.
What do you think? Does that resolve the concerns you’ve noted or is it still skewed in your view? As mentioned earlier, I’m still working on Kelly, and any comments you or others can provide will make my analysis stronger. Ultimately, however, Kelly (or, more accurately, 1/2 Kelly) is used for the limited purpose of preventing me from wagering too much on a given position. Only if Kelly turns negative does it commend not investing at all, and at no time does it constitute a measure that urges taking a position in a given equity. The merit of a given opportunity is in the combination of a company’s strength (financial and market) and the cheapness of the stock price. Kelly provides no insight to either of those first-principle questions.
Thanks, again, Ben. I appreciate the time taken to consider and investigate Kelly, and I’ve enjoyed our email and Yahoo! Finance exchanges. I wish there were more thinking at your level and willing to question my methods.
Robert
rcrawford
June 9, 2008 at 6:37 pm
Please note that the Kelly Criterion, which is the subject of Ben’s question/comment, does not appear as a consideration in this posting. It does appear (without comment) in the UNH and AEO postings, and it is referenced in the most recent posting on the Temporary Personnel Staffing sector. In none does it represent a significant input in my analysis; although, it does urge exclusion from consideration of one stock in the Temp Personnel Sector analysis.
rcrawford
June 9, 2008 at 6:42 pm
I guess my question has more to do with the overall structure of your formula. I’m not sure it actually is consistent with the Kelly formula. This becomes apparent when you adjust the “gain” part of the formula. I’m working on finals right now and then I’m driving to Florida for my internship (equity research). I’ll think more about this next week when I get there and I’ve had a chance to settle in.
I too am interested in applying the Kelly formula to my investment analysis but I’d like to be certain I’m using it correctly.
Best,
Ben
Ben
June 10, 2008 at 4:28 am
This is a fantastic analysis. Do you have your workbook for download available – I would love to be able to take a look at it to try to follow your calculations as I am just learning all these analysis tools. Thanks for a great writeup!
Al
August 8, 2008 at 8:06 am
Al, thank you for the compliment. For a number of reasons, the workbooks are not available for download. Much of the work is proprietary, following a consulting project. There are a number of workbooks that are inter-linked. The workbooks operate based on programmed macros. And the workbooks access several external data sources, which, if overused, prompts at least one provider to change formatting — making re-programming necessary.
Thanks,
Robert
rcrawford
August 8, 2008 at 9:35 am
Understood. Have you seen the free SMF Add In? It’s an excel .xla posted by its creator here:
http://finance.groups.yahoo.com/group/smf_addin/
It’s a fantastic tool which lets you easily import much if not all of the financial data available on yahoo finance, google finance, morningstar, etc. right in to excel automatically. There are tons of great examples and templates in the group’s download section. If you want to get lots of financial data in to Excel with no work, take a look.
Al
August 8, 2008 at 8:12 pm
SMF is similar to a small portion of my programming. It (SMF) was programmed using Excel 2002, which is problematic for significant portions of VBA used with Excel in Office 2008; although, most (not all) of the compatibility challenges are not related to the Get External Data functions. Most of the problems centered around variable declaration for loops and management of memory — which VBA fails to address but needs to given the significant expansion of serviced cells in the latest version of Excel. If you have any ideas on that front, I’d be most interested.
Thanks for the suggestion.
Robert
rcrawford
August 8, 2008 at 8:44 pm
I’m afraid I don’t – I thought Excel 2007 was a disaster so I have stayed with Excel 2003 which is plenty for my needs. In any case, VBA is not really suited for any ‘real’ programming, and I would think that you are running up against its limits, particularly with memory management. It might be time to go to a SQL/.Net solution in your case – by which you would lose the ease of use of Excel but would gain tremendous performance advantages via a relational database and compiled code. You can quite legally use a development version of SQL Server on your personal computer, and it only costs about $50. Or look in to the lite version “SQL Server 2005 Express” which is free and quite good.
Al
August 9, 2008 at 4:43 am
Thanks, Al. We’ll see how things go, but I doubt I’ll go that far with it.
Robert
rcrawford
August 9, 2008 at 7:29 am
How did you get the standard deviation lines to plot above and below the trendline in the graphs?
Al
August 9, 2008 at 11:49 pm
Figured it out! =TREND()+/1STDEV(), plot the points and fill with a trendline
Al
August 10, 2008 at 1:00 am