Forget Your Stockbroker: How to Measure and Monitor the Correlation of Your Stocks Using Excel

Correlation is a measure of the degree to which a stock tends to move in the same direction as another stock or the broad market.

Normally, correlation measures use stock returns but in Excel you don’t have to use returns, as this article reveals.

The lower the correlations of your stocks with one another and with the market, the better diversified your portfolio – this does not mean the higher your returns though. Correlations range from 0 to 1 and a measure of 0.5 or higher indicates a high correlation.

When you know the correlations of your stocks, you are able to make objective buy or sell decisions.

For example, say, two of yours stocks, A and B, are highly correlated and you want to sell one of them to rebalance or reallocate your portfolio. Say stock A is also highly correlated with the S&P 500 while stock B is not. To improve the diversification of your portfolio, your best move would be to sell stock A - even if you're in love with it - because it correlates more with the market.

While you can get a stock’s correlation with the S&P on good financial information sites like Yahoo!Finance, you still need to know your stocks’ correlations with one another.

If you don’t have software that can give you these statistics at the press of a button, you can easily get them in Excel by using the “CORREL” function.

The trick is to use a system of letters or numbers to indicate up and down movements, and then apply the CORREL function to the two data sets for the two stocks in question, to get the correlation between them.

In the picture illustration below, you see that I use 1 (green shade) for upside movement and 2 (red shade) for downside movement.



To format your cells to flag high correlations – indicated by the yellow cells in the picture – you use the “Conditional Formatting” feature in Excel.

The good thing about the layout in the picture above is that you can see all your correlations in one window and make quick comparisons.

Of course, the downside to this whole exercise is that you have to manually record the daily movements of your stocks. Otherwise, you may not get accurate measurements.

Bullish on Africa With AFK

A couple of weeks ago, I was still searching for a stock with which to play Africa.

Well, I finally got into AFK, which is an exchange traded fund (ETF) that tracks the Dow Jones Africa Titans 50 Index.

Now my portfolio is exposed to every nook and cranny of the world, and AFK constitutes 9% of the portfolio. Yeah, that's right...9%!

I got into AFK at $22 a piece, well before the stock crossed its 200-day moving average to the upside, which is always a bullish technical signal.

Here's Jim Cramer of CNBC's Mad Money show talking up AFK in the video below.
Yes, like Cramer said, over a quarter of AFK is weighted in South African stocks, but i have a feeling this will gradually change as the economies of smaller African countries kick into gear.

In fact, it has to change. Take a look the latest sector allocation for AFK, Banks, Basic Materials, Oil & Gas, and Telecoms dominate the list. Three of these sectors - excluding Basic Materials - are growing fastest not in South Africa but in other African countries. Therefore, at some point the fund will have to account for this faster growth in smaller countries.

As at the close of trading on May 22, 2009, I'm up almost 18% from my entry point into AFK, so I'm sitting pretty.

However, compared to other emerging market ETFs, AFK still has a long way to go in terms of year-to-date (YTD) returns.

Let's go Africa!

Apple’s Excellent Customer Service Makes the Stock a Buy

I don’t have any Apple (AAPL) products yet, not even a "Pod". However, I do use iTunes, but that’s not a product, it’s a service.

Anyway, last week I had an issue with purchasing iTunes gift certificates online. Although there was no helpdesk number to call – something that’s now common practice with companies – I did get to “chat” online with a customer service rep who promised to follow up through email…and actually did!

Not only was there a follow-up email, dude went back and forth with me on email until he confirmed I was satisfied with the problem’s resolution. I thought to myself, that’s what customer service is.

Now, AAPL does make cool products, but I don’t think that’s the sole reason for the company’s success. Excellent customer service has to be part of the reason.

I know what you’re thinking. Maybe the dude who attended to me had just reunited with his high-school sweetheart. Nah. I think great customer service is just how AAPL rolls; it’s part of their mojo.

I’ve been in their store many times and “test-driven” like 10 different Pods and Macs without buying a thing, and none of their people gave me as much as a “beat it” look.

Look, if you want to know how well or awful a company is run, just experience its customer service. And Wall Street knows this. That's why the stocks of well managed companies often command a premium in the market. Just compare the price/earnings (P/E) ratio of AAPL with those of its competitors.

Costco (COST) is another company that enjoys a higher premium than its competitors partly because Wall Street believes it's better managed.

I have thought about buying some AAPL (no pun intended), but my portfolio is already tech heavy – it’s 18% weighted in tech versus S&P 500’s 15%.

Furthermore, I am not a fan of “hardware” tech stocks, because they are more susceptible to commodity prices than their “software” counterparts are. This doesn’t mean I won’t buy or use AAPL products though.

If you’re looking to get into a tech stock you should consider AAPL. I admit customer service is a crude way to decide whether to invest in a stock, but it seems to be an accurate predictor of a long-term moneymaker.

When deciding whether to get into a stock, some investors look at fundamentals, others look at technicals. Now you can go one up on them by also looking at customer service.

5 Stock Market Tips to Make You A Smarter Newbie Investor

In investing, the most potent firearm to carry is knowledge. As you begin to invest in stocks, here are five tips to help you navigate the choppy waters of the stock markets:

1. Never invest in the market more money than you can afford to lose. Okay, so this is more a rule than a tip, but it’s probably the most important point to make about investing in stocks.

While you can make a lot of money investing in stocks, you can also lose big, though the chances of you losing big decrease the longer you stay invested in the market and the less frequently you trade.

If you are highly risk-averse, then it’s better to totally avoid the stock market. Stick to cash or money market instruments like certificate of deposits (CDs) and commercial paper.

2. Never invest in a stock unless you understand how the company makes money. If you’re going to hold a stock for a very long time, it pays to understand how the company makes money. This will help you make better buy and sell decisions – see tip 4.

To understand how a company makes money, you’ll probably have to read its Annual Report or 10-K. Another good way to get this insight is to listen in on a company’s earnings conference call, where stock analysts grill company executives about the strategy and financial health of a company.

3. Practice Dollar Cost Averaging (DCA) when building position in a stock. DCA is an investing strategy whereby you invest the same amount of money in a stock at regular intervals – like every week or month – regardless of the price of the stock when you buy it.

If you buy when the stock is trading high, you get fewer of the stock, and if you buy when the stock is trading low, you get more of it. Many online stock broking companies will automate this process for you.

DCA allows you to build a position in a stock at a pace or contribution level you are comfortable with. Furthermore, with DCA, you don’t have to “time” the market - to try to buy at a particular price.

Until you know what you’re doing, DCA is more reasonable than buying on “gut” feeling or buying because Jim Cramer said something good about the stock on Mad Money.

4. Buy on the rumors and sell on the news. This is a common adage among investors. Once you’ve built position in a stock to a level you’re satisfied with you may want to buy and sell some of it once in a while.

Studies have shown that when the market anticipates good news from a company - like a good earnings report - its stock price often starts to rise days before the news becomes official.

Therefore, you want to buy the stock once the rumor breaks. If you buy on the day the company delivers the good news, you’d probably miss out on much of the gains.

Traders, who typically don’t hold stocks for a long time, will often sell and take “profit” once the company breaks the good news. This is what investors mean by “sell on the news”.

If the market expects bad news, then don’t wait until the company breaks the news before you sell because by that time, the stock may have bled profusely.

Now, realize that your stock does not have to be the news-maker before you act. If you hold company A’s stock and Company B, a competitor, is the news-maker, then your stock is likely to react to company B’s news since they are in the same industry.

5. To get the best prices make your trades at the start or at the end of the trading day. The first and last 20 minutes or so of the trading day often witness the most activity by investors.

During the start of trading, investors try to capitalize on news before the opening bell, so the scramble to get in or out of a stock tends to make stocks “gap up” (price shoots higher than previous day’s high) or “gap down” (price shoots lower than previous day’s low) depending on the nature of the news.

Institutional investors often come into the market to trade during the quiet times - like lunchtime - or towards the end of the trading day.

The Learning Never Stops

Continue to arm yourself with knowledge and recognize that a "trader" is different from an "investor". A trader hits and runs while an investor buys and holds.

These days, however, most self-directed investors are not on the extremes. Sometimes they run like traders, and other times they walk like investors. Get in where you fit in.

Africa On My Mind

Africa remains the only region I am currently not exposed to in any meaningful shape or form. I say meaningful because I do have a small exposure to South Africa and Egypt through the MSCI Emerging Markets Index Fund (EEM), which is one of my favorite holdings.

I don't want to be exposed to just one Africa country, so what I'm looking for is an Exchange Traded Fund (ETF) stock that covers several African countries. To this end, I've got the Market Vectors Africa ETF (AFK) on my watch list.

When I took a closer look at AFK, i realized it's heavily weighted in the fastest growing sectors of the continent, such as telecommunications and banking, which is a good thing.

At this point I'm yet to find another ETF similar to AFK in exclusivity to Africa, so right now it's in pole position to become the portfolio flagbearer for Africa. I shall decide soon.

The Banks Just Pimped Us

No, seriously.

Six months ago free market capitalism as we know it was about to end, and big bank stocks were trading for less than an ATM access fee.

Analysts were breathing down the necks of bank executives, daring them to declare just how "balanced" their balance sheets were.

To keep hedgies from turning their companies into penny stocks and avoid the wrath of average Joe and Jane, executives begged the government for mercy and got plenty of government funds and government guarantees of their debt. I don't buy the argument that banks were forced to take the money. If they hadn't taken it how many of these banks would be around today?

Now, all of a sudden, they don't need this money anymore?

I smell a rat.

After using taxpayers money to pay executive compensation, raise capital, and avoid a mutiny by investors, could it be that the big banks now want to return the money before government clamps down on executive compensation and credit card interest rates hikes?

Well, well, well. If that's not pimping taxpayers, then i don't know what is.

IBM to GE: I’m the New American Idol

IBM is one of the “giants” of American industry, with a history dating back to the 19th century.

In case you haven’t noticed, Big Blue is no longer just a “tech” company. It’s now a company that just happens to be tech.

This company has been busy. Just google “IBM Acquisitions” and check out the Wikipedia table of its buying spree since 2000. Sun Microsystems is its latest target.

Big Blue has always had plenty of cash but now it’s flaunting it, right in the midst of a credit crunch that has many other “conglomerates” begging credit rating agencies for mercy.

The shopping spree is changing Big Blue fast. It’s now involved in, or looking to be involved in, major sectors of the U.S. economy: Healthcare, Energy, Industrial, Transportation, Nanotech, Financial/Consulting, Communications, and Retail.

Are you thinking what I’m thinking? That’s like GE, right? Exactly.

Big Blue may be the new GE.

So what?

Well, if you are new to investing in stocks, and you’re thinking about creating a “diversified” stock portfolio, you really could just buy Big Blue and you’d be diversified. You’d even get global exposure since it now earns around two-thirds of its revenue from outside the U.S.

However, would you really want to have a one stock portfolio of just IBM stock?

Many years ago, when I started to get into stocks and wanted to create a diversified portfolio, one of my options was to just buy GE and call it a day.

When it comes to gauging the health of the U.S. economy, GE is considered the “bellwether” company because it’s involved in pretty much every major sector of the economy. As GE goes, so goes the economy. GE moves markets…I’m not sure Wall Street still sees it this way but that’s a debate for another time.

Anyway, I decided not to buy just GE back then. In fact, I didn’t buy GE at all. Regardless of its “blue-chip” status, I figured it would be too risky to put all my eggs in one basket. I also reasoned that holding GE would have made my portfolio too heavily weighted in certain sectors of the economy where GE was heavily involved – as I added more stocks to the portfolio.

Today, I hold neither GE nor IBM in my current portfolio, but I’m as diversified as the S&P 500 Index. Think about that as you construct your stock portfolio.

So is Big Blue now the new face of the U.S. economy?

It’s too early to tell. However, one thing is clear. The current financial crisis has GE in the financial doghouse, while Big Blue is living it up with its buying spree. We may be witnessing the emergence of a new idol of the U.S. economy.