a Coal Index

I've been looking for an Index which covers coal stocks, has historical info (via Yahoo) which can be downloaded to a spreadsheet and ...

>Because you invest in coal stocks, right?
Well, yes ... but it'd be neat to be able to create my own index which covers, say, gold stocks or car companies or retail stores etc. etc.
So I figured I could download a gaggle of stock prices, weight them according to market caps and generate an index.

>Like the DOW.
No, the DOW is proportional to the average price of 30 stocks. Bigger stock prices have more weight.
It'd be more like the S&P where bigger companies (measured by the total value of all stocks) have more weight.

The spreadsheet looks like this (where you can click on the picture to download it):

You type in a dozen (Yahoo) stock symbols and some benchmark (like the DOW), then click a button.

I picked a dozen which have mkt caps over (about) $1B. In fact, them "weights" are the mkt caps, in billions - BTU being the largest.
Note that BTU has a very high weighting. It turns out that the Index acts much like BTU.

A year's worth of prices are downloaded and plotted and an Index is calculated based upon the dozen market capitalizations.
You might ask "Why just U.S. stocks?", but the majority of my readers are from the U.S.

>And I type in a dozen market caps? You kidding?
Uh ... no. There's a separate sheet that'll do that. It downloads the market caps and generates the various weights. It looks like this:

>I'm looking at the picture of the spreadsheet. Judging from the coal index chart, compared to the DOW, coal stocks don't look good.
That's over the past year, but check out the past six months:
Looks good, eh?

>The past six months? Can I ask for that ... in the spreadsheet?
Don't tell nobody, but cell B2 says:   =B3 - 365   (that's going back a year, eh?)
But, if'n you're careful, you can change it to   =B3 - 365/2   (that's a half-year, eh?)

>I assume your coal stocks is doin' good.
You mean GCE and WTN? So far, so good.

Note, however, that you can change the "Title" from COAL INDEX to, say,
CAR INDEX and type in a dozen car company symbols.
Or, maybe, GOLD INDEX where you stick in your favourite Gold companies.
Or, maybe, SHIPPING INDEX where you stick in your favourite Shipping companies, like:
DRYS, DSX, EGLE, ESEA, EXM, FREE, GNK, NM, SB.

>But that ain't a dozen shipping stocks.
That's okay. Try it.


In other news:
Danyon told me about a "Global" Coal Index: the STOWE INDEX
Unlike "my INDEX" (which includes a dozen U.S. companies), STOWE involves coal companies around the globe - primarily in China and the U.S.
Staring intently at the charts for the past year, it seems that there's been a recent divergence:

Very interesting, eh?

So I take a peek at the largest component of the STOWE, China Shenhua Energy (over the past six months):

Mamma mia!


Check out the past year.
Compared to Shenhua, the largest U.S. outfit, BTU, can't get out of 1st gear!!
There's also an ETF that emulates the STOWE INDEX: KOL
I've added that to the spreadsheet ... as a second benchmark.