Kuro5hin.org: technology and culture, from the trenches
create account | help/FAQ | contact | links | search | IRC | site news
[ Everything | Diaries | Technology | Science | Culture | Politics | Media | News | Internet | Op-Ed | Fiction | Meta | MLP ]
We need your support: buy an ad | premium membership

[P]
Should you bother picking stocks? A simple Excel Spreadsheet

By thefirelane in Technology
Wed Mar 29, 2006 at 02:15:58 AM EST
Tags: You Know... (all tags)
You Know...

When investing in the stock market, tracking one's returns accurately is the most important thing no one does.

This article will show how regular investors can track their returns against the S&P 500 index. Any other index could be used.

The instructions in this article give the steps necessary to create an excel spreadsheet that will show two things: the total return on investment of all stock transactions, and the hypothetical return of those same transactions if the funds were instead invested in an exchange traded index fund (in this case, SPY).

Many investors are not aware of what this delta is, and therefore they have no method to measure themselves as a stock picker. It could very well be that they are wasting their time and money when they could simply invest in an index fund and achieve greater performance.


Step 1: adding all transactions

This is the most tedious part, but it is essential to capture all transactions in order to get an accurate picture.
Create an Excel spreadsheet with a row containing the following values as headings:

  • Ticker
  • Date Purchased
  • Date Sold
  • Shares Bought
  • Purchase Price
  • Current, or Sold Price
  • Amount Invested
  • Percent Change
  • Dollar Change
  • S&P Change
  • S&P Dollar Change
  • Difference
For convenience, I have already created an example spreadsheet
Now you must manually type in the following information from your records for each stock:
  • Ticker
  • Date Purchased
  • Shares Bought
  • Purchase Price
Note: some stocks, if purchased at different dates will have this information across more than one row: such as this

If you have sold the stock, add the following information manually: Date Sold, Sold Price. In the example spreadsheet, I've added a couple stocks, two of which are currently held: PTR, EBAY and one which was sold: ARMHY.

If you have not sold the stock, ignore the current price cells for now.

Note: currently purchase price has to be adjusted for any stock splits by dividing it (see eBay in example spreadsheet)

Step 2: Automatic formulas

Next we add the formulas that are automatic across all transactions. In the columns listed, enter the formulas given. Apply these formulas across all rows by clicking the lower right corner of that cell and dragging it down.

  • Amount Invested: =D2*E2
  • Percent Change: =((F2/E2)*100)-100
  • Dollar Change: =(G2*((H2+100)/100))-G2
  • S&P Dollar Change: =(G2*((J2+100)/100))-G2
  • Difference: =K2-I2
Your spreadsheet should now look like this

Step 3: Adding the S&P 500

First, go to this page. In the form, enter the date of your first stock purchase. This date is needed in order to compare the returns between your investments and if you had made that same investment in the S&P 500. Make the end date today's date. Click on 'download to spreadsheet'. Open this .csv file in excel and select all the columns. Copy and paste this information into a new worksheet in your current excel file.
Your spreadsheet should now look like this

Step 4: Vlookup(), your friend

Now, we can compute the S&P 500 change value (for now, just the sold stocks). To do this, we use the VLOOKUP function. VLOOKUP searches for a value in the leftmost column of a table and then returns a value in the same row, in a specified column. Basically, we will use this to look up the value of the S&P 500 at the date we bought (and sold) a stock. For your sold stocks, set this column to the following formula (adjusting for row):
=((VLOOKUP(C2,SP500!A:G,7,)/VLOOKUP(B2,SP500!A:G,7,))*100)-100
This formula takes the values in Date Purchased, and Date sold, and then scans through the S&P 500 historical prices until it finds those dates. When it finds those dates, it returns the adjusted close, which accounts for any dividends and stock splits.

Step 5: getting the current prices

Now you must run a web query. You will want to run a saved query.

Windows: Open, ProgramFiles/MicrosoftOffice/Office11/Queries/MSN MoneyCentral Investor Stock Quotes.iqy

Mac:Insert a new worksheet, and call it 'portfolio'. This worksheet is where you want to add your web query. Go to: Data, Get External Data, Run Saved Query... MSN MoneyCentral Investor Stock Quotes

Now add the stock tickers that you have not yet sold. Be sure to also include SPY! Click 'use this value for future refreshes'.

For windows users this will open a new spreadsheet. Rightclick the worksheet name, and move it to the current spreadsheet you are working on. Mac users should have the portfolio opened into their new worksheet.

You will now have a nice portfolio in an excel spreadsheet. This can be updated with current prices by going to: Data:Refresh Data

In the transactions worksheet, for stocks which were not sold, set their value (column F) equal to the current price in the MSN money download chart.
Example formula: ='MSN Money Central Stock'!D4
Under the column 'S&P change' use Vlookup as described in step 4 to compute the percentage change between the purchase price, and recent price of the S&P500 in the web query
Example: =(('MSN MoneyCentral Investor stock'!D6/VLOOKUP(B2,SP500!A:G,7,))*100)-100

Almost done

Now the column 'Difference' accurately reflects the difference between your actual transaction, and what you would have made if you put the equivalent amount of money in SPY. In this case negative numbers are good As it means you would less money if you invested in the S&P500 index.
As a final touch, insert one more row under each stock that has paid a dividend. In the example file, I included a 100 dollar dividend from PTR. This value must be added as a negative in order for the math to work

Reporting the final result

Create a new worksheet. with the following text in column A

  • Total Amount invested
  • Total Gain
  • % Gain
  • S&P500 Gain
  • S&P 500 % Gain
  • If I invested in S&P 500
In column B, they should have the corresponding formulas:
  • =SUM('all transactions'!G:G)
  • =SUM('all transactions'!I:I)
  • =B2/B1*100
  • =SUM('all transactions'K:K)
  • =B4/B1*100
  • =SUM('all transactions'L:L)
Again, for the final value, negative numbers are better. It means you picked things better than simply investing in the S&P 500 index fund. Sadly, for most people who read this, this number will be positive.

Closing thoughts

There are still a couple inconvenient things that must be done with this excel spreadsheet. For instance, everytime a stock is sold, the S&P500 historical data must either be re-downloaded, or entered by hand. Since most invdividuals interested in this tracking tool are long term investors, this should not be too frequent.

In addition, all the final difference number does not include several important considerations:
You must consider the comisison for trading an ETF. For this chart, commission is not accounted for, as it would be equal for both transactions. However a comparison between trading stocks versus an index mutual fund (where there is no charge to invest further fund) might skew the results in favor of the mutual fund.
Lastly, even if you are one of the fortunate investors to come out ahead of the S&P500, you can now put a hard number on how much your time has been worth. Undoubtedly this has required research, stress, newspaper subscriptions, and time. Is the total amount of time worth the number displayed in the excel spreadsheet? This depends on you.

Sponsors

Voxel dot net
o Managed Hosting
o VoxCAST Content Delivery
o Raw Infrastructure

Login

Poll
You vs. S&P500?
o I don't know 5%
o I don't know, and won't do this to find out 18%
o I'm a day trader, this is too much work 0%
o I don't do stocks 40%
o S&P beat me by 50%+ 0%
o S&P beat me by 40-49% 0%
o S&P beat me by 30-39% 0%
o S&P beat me by 20-29% 0%
o S&P beat me by 10-19% 0%
o S&P beat me by 0-9% 8%
o I beat the S&P by 0-19% 8%
o beat the S&P by 20-29% 8%
o beat the S&P by 30-39% 2%
o beat the S&P by 40-49% 0%
o beat the S&P by 50%+ 8%

Votes: 37
Results | Other Polls

Related Links
o SPY
o example spreadsheet
o such as this
o this
o this page.
o this [2]
o Now add the stock tickers that you have not yet sold
o this will open a new spreadsheet
o Rightclick the worksheet name
o move it to the current spreadsheet you are working on
o Also by thefirelane


Display: Sort:
Should you bother picking stocks? A simple Excel Spreadsheet | 113 comments (90 topical, 23 editorial, 1 hidden)
I wouldn't give microSUCK the pleasure. (1.61 / 13) (#2)
by Josh Ferien on Sun Mar 26, 2006 at 01:34:22 PM EST

While some people might be willing to step on a dying baby to make some money on the stock market, others of us actually have moral standards and principles that say the ends don't justify the means. As an open source advocate, I only use software that has been tested and vetted by the free software foundation. That's why I use debian on all my computing devices -- NOT Micro$oft Office.

Let me remind all of the readers that Micorsoft is the leading software supplier of the U.S. MILITARY, an organization dedicated to the systematic elimination of women and children. When you make an Excel spreadsheet, you might as well have an abortion while you're at it, 'cause that's what you're doing: Killing babies.

It's time to put a stop to irresponsible investment practices like those advocated by the author of this piece. Instead, we need articles that promote socially responsible investing. It's time to stop giving into the monopolists who support the murder of children, the destruction of rainforests, and the theft of music and video entertainment.

I say enough.

Cordially,

Josh Ferien

The J is for Justice!

'systematic elimination of women and children' (3.00 / 5) (#3)
by tkatchevzz on Sun Mar 26, 2006 at 02:36:01 PM EST

how amazingly cool, a new name for a rock band.


[ Parent ]
You're a few years late, champ. (none / 0) (#20)
by kitten on Sun Mar 26, 2006 at 10:21:46 PM EST


mirrorshades radio - darkwave, synthpop, industrial, futurepop.
[ Parent ]
...and then we loled. Oh how we loled! /nt (3.00 / 2) (#21)
by MotorMachineMercenary on Sun Mar 26, 2006 at 10:25:48 PM EST


--
"My mental image of you is Wyatt's brother Chet in Weird Science."
- Parent ]

"Excel" and generalization (none / 0) (#30)
by artsygeek on Mon Mar 27, 2006 at 02:13:29 PM EST

Well, I'd say that "Excel Spreadsheet" has become akin to saying a "Kleenex" or "Rice Krispies", and has begun to describe spreadsheets in general.

[ Parent ]
For you maybe (none / 1) (#39)
by BottleRocket on Mon Mar 27, 2006 at 09:22:10 PM EST

I find that "spreadsheet" makes as much sense as "Excel® Spreadsheet", and doesn't leave anyone out.

$ . . . . . $ . . . . . $ . . . . . $
. ₩ . . . . . ¥ . . . . . € . . . . . § . . . . . £
. . . . * . . . . . * . . . . . * . . . . . * . . . . . *
$ . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . $
Yes I do download [child pornography], but I don't keep it any longer than I need to, so it can yield insight as to how to find more. --MDC
$ . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . $
. . . . * . . . . . * . . . . . * . . . . . * . . . . . *
. ₩ . . . . . ¥ . . . . . € . . . . . § . . . . . £
$ . . . . . $ . . . . . $ . . . . . $
$B R Σ III$

[ Parent ]

I do too.... (none / 0) (#56)
by artsygeek on Tue Mar 28, 2006 at 06:14:23 PM EST

The problem is, when I talk to pointy-haired types, I have to say "Excel" because that activates the little neurons and makes them think of what I'm talking about.

[ Parent ]
I can't, I just- (none / 0) (#35)
by spooked on Mon Mar 27, 2006 at 07:37:40 PM EST

lol what?

Seriously.
[ Parent ]
Compressed Volume advocate here (none / 0) (#85)
by hackwrench on Thu Mar 30, 2006 at 12:30:01 PM EST

Reasons I use Microsoft:
1. NTFS has transparent compression
2. Input Method Editors

3. Wordweb dictionary 4. JWPce 5. Agent Ransack

[ Parent ]
HAHA (none / 0) (#112)
by 808blogger on Sun Aug 06, 2006 at 03:09:59 AM EST

You are an idiot

[ Parent ]
Interesting (2.16 / 6) (#4)
by Herring on Sun Mar 26, 2006 at 03:04:15 PM EST

Market principles:
  1. In an efficient market, all information is available to all people instantly.
  2. Therefore there are no arbitrage opportunities
  3. Therefore outperforming the market is a matter of luck and should go wrong as well as right just as often
Since people do outperform the market, 1 is false, therefore consistent winners do so on the basis of insider information.

Say lol what again motherfucker, say lol what again, I dare you, no I double dare you
Incorrect (3.00 / 5) (#5)
by thefirelane on Sun Mar 26, 2006 at 03:09:09 PM EST

On two points.

Consistent winners could be consistently lucky. With millions of people, one person would call the correct sequence of 100 coin flips.

Also, consistent winners do not negate point 1, as all information can be available to people, but not all people bother to read or research it. Furthermore, some people are just smarter and will do different things with that information.

-
Prube.com: Like K5, but with less point.
[ Parent ]
So, (none / 1) (#7)
by Herring on Sun Mar 26, 2006 at 04:23:12 PM EST

what's your take on efficient market theory? Bollocks or not? LTCM et al believed they could use leveraged arbitrage opportunities to achieve unlimited returns. Why did it go wrong?

I have a top horse betting strategy which (in the few times I've tried it) has been successful. It involves hedging by taking a Lucky 15 on 4 second favourites. In what way is that different?

Say lol what again motherfucker, say lol what again, I dare you, no I double dare you
[ Parent ]

Your reasoning is wrong (none / 1) (#8)
by thefirelane on Sun Mar 26, 2006 at 04:36:45 PM EST

You stated that since some people are sucessful, then either they are insider trading, or markets are inefficient.

I meerely replied with another way in which markets can be efficient, and people can succeed without insider trading.

Going to your horse analogy... it is quite possible everyone has the same stats, yet some people consistently beat others. One fact does not negate the other.

understand?

-
Prube.com: Like K5, but with less point.
[ Parent ]

No (none / 1) (#9)
by Herring on Sun Mar 26, 2006 at 05:10:09 PM EST

The definition of effecient markets implies that if you beat the market then it is through luck or the market is not efficient. Since so many of the "winners" on the late 80s/early 90s market ended up in prison, I would suggest that you draw your own conclusions.

Say lol what again motherfucker, say lol what again, I dare you, no I double dare you
[ Parent ]
Then I don't accept your choice (2.00 / 2) (#10)
by thefirelane on Sun Mar 26, 2006 at 05:32:41 PM EST

I don't accept the choice you are forcing then: that either markets are inefficient, or those who succeed are lucky.

My personal belief is that information is distributed in a highly efficient way, but not acted on in such a way. In other words, everyone can go to Yahoo finance and do research, but not everyone does. This also means that I do not believe in the efficient market theory of securities prices... because the current prices do not reflect an objective assessment of value, but mix in other factors such as emotion. In this way, people can suceed, without breaking the law or being lucky.

Do you not agree?

-
Prube.com: Like K5, but with less point.
[ Parent ]

Rational Actors (none / 0) (#82)
by geekmug on Wed Mar 29, 2006 at 06:15:19 PM EST

Rational actors one of the first assumptions that is made when dealing with macroeconmics. As you point out, people are not rational actors. Therfore, invoking any rules with saying "by definition" is meritless because the definition of an "efficient market" invokes the concept of all participants being "rational actors." In this case, Eugene went out of his way to specify that, so there should be no debate:
"An 'efficient' market is defined as a market where there are large numbers of rational, profit-maximizers actively competing, with each trying to predict future market values of individual securities, and where important current information is almost freely available to all participants. [...]"

- Eugene Fama, "Random Walks in Stock Market Prices," Financial Analysts Journal
Both of you are right, you just aren't talking about the same thing. Herring is assuming the model holds and his assertions are then correct. Whereas you, thefirelane, are actually saying the model is wrong. These arguments are not the same.

My own personal take is that the model is not accurate. Some of the participants are neither rational nor profit-maximizing.

-- Why reinvent the square wheel?
[ Parent ]
Rational Actors (none / 0) (#91)
by GRAMMERSoft on Fri Mar 31, 2006 at 11:30:20 PM EST

I was about to post something along the same lines. I think it is clear that the "rational actors" assumption does not match the real world.

Consider a craps table in a casino. Bettors at the table can be considered investors - the various bets that are available are investments. All relevant information is known by all investors. This information indicates that any investment they make (any bet) has a negative expected value. If the investors were rational actors, none of them would ever place a bet.

Clearly, human beings make financial decisions that are not rational, or casinos would not exist. One could try to argue that investors in financial markets are rational while bettors in casinos are not, but to me that is a fairly extraordinary claim. And I think there are numerous examples of investor behaviour to back that up, including that fact that many investors probably do not even measure the relative performance of their investments.

[ Parent ]

Wow. Way wrong. (2.00 / 2) (#80)
by fn0rd on Wed Mar 29, 2006 at 04:03:12 PM EST

Millions of people? Try nonillions. The probability of guessing one hundred coin flips in a row is 1 in 1,267,650,600,228,229,401,496,703,205,376.

This fatwa brought to you by the Agnostic Jihad
[ Parent ]

19 flips would still be fairly lucrative (nt) (none / 0) (#81)
by geekmug on Wed Mar 29, 2006 at 06:01:08 PM EST


-- Why reinvent the square wheel?
[ Parent ]
specious reasoning (2.50 / 2) (#6)
by Delirium on Sun Mar 26, 2006 at 04:15:24 PM EST

The fact that people do outperform the market in no way shows that any of your three principles are incorrect. It merely shows that some strategies sometimes outperform the market, but this is true even in a perfectly efficient market, unless taken over infinite horizons. If we choose, say, 100 fixed strategies, along the lines of "always buy a stock if it's gone down more than 10% in the past week", "always buy stocks starting with 'B' if their current price ends in a '5'", and so on, and evaluate their performance over some period of time (say, a month, or a year, or 10 years), it is nearly certain that some of these strategies will outperform the market. This has nothing to do with them being good strategies, but simply luck.

[ Parent ]
You make a huge false assumption (2.75 / 4) (#11)
by TheGaffer on Sun Mar 26, 2006 at 06:24:08 PM EST

Namely that people will always make the same correct decisions when presented with the same set of data. As the Intelligent Design 'debate' has shown, complete dolts considerably outnumber smart, rational people. There are myriad opportunities for arbitrage thanks to most people making really abysmal decisions. Economic bubbles such as the .com boom are a perfect example - the market is moving very strongly in a partucular direction for completely irrational reasons. The data (namely the toilet-paper business plans) is freely available but the market generally ignores it. Short sell to all the dolts snapping up stock and profit by merit of your superior interpretation of the data available.
Poker for Linux, Mac & Windows
[ Parent ]
x? (none / 0) (#14)
by thankyougustad on Sun Mar 26, 2006 at 07:17:46 PM EST

i beat the market by a small but nice margin and have 0 insider information. please explain this paradox.

No no thanks no
Je n'aime que le bourbon
no no thanks no
c'est une affaire de goût.

[ Parent ]
Haha (none / 1) (#89)
by thenerd on Fri Mar 31, 2006 at 01:52:20 PM EST

I love the way that you completely disregard the behavior of a lot of exceedingly skilled people by saying they don't do what they actually do, in 3 simple principles. I'm sure it's very nice sneering at everybody from up there, but you have no idea what you are talking about. If you research trading to a reasonable level you'll realise you are talking complete guff.

[ Parent ]
market efficiency (none / 0) (#93)
by xmnemonic on Fri Mar 31, 2006 at 11:56:59 PM EST

Just FYI, the general consensus among traders and investors is that for many well-known stocks, the pricing is highly efficient. This usually includes any company whose market cap exceeds 1 billion and engages in a major industry (i.e. nothing obscure or stupid). On Yahoo! Finance, one can estimate how "discovered" a company is by checking how many analysts cover it. The actual analyst ratings are usually worthless (they end up buying high and selling low), but they're useful for gauging the popularity of a stock.

[ Parent ]
-1 Mathematics are vendor-neutral (2.42 / 7) (#12)
by toulouse on Sun Mar 26, 2006 at 06:58:58 PM EST

Instead of explaining how to set up an Excel spreadsheet to perform the tasks outlined, it would have been much better to outline the data set, what you were trying to measure and why. Excel-specific instructions could then have been given as an example of the mathematical comparisons being made. This way, people could have concocted their own implementations using whichever technology they are most comfortable with; Excel macros are not necessarily the best tool for querying and manipulating remote data, for example.

Yes: Intelligent (or mind-numbingly bored) readers can reverse-engineer the data, algorithms and rationale from your hands-on description, but why should they? If your methods are valid, they will be valid regardless of the underlying technology.


--
'My god...it's full of blogs.' - ktakki
--


I do this (none / 1) (#16)
by thefirelane on Sun Mar 26, 2006 at 07:36:37 PM EST

I do exactly what you say. I say what we are trying to accomplish, what the functions I use do, and what they produce. All you have to do is substitute in your preferred instruction.

Also, the addition of the header titles makes it very clear what should be placed there as a formula.

If you have any specific suggestions about how I am unclear, I'd enjoy hearing it.

I have a feeling this is coming from a knee jerk reaction against an MS product... even thought I have a sneaking suspicion a lot of people here use it, or at least have it installed on their computer, and are quite familiar with it due to business settings.

-
Prube.com: Like K5, but with less point.
[ Parent ]

Article fails to make this clear (none / 0) (#23)
by Enlarged to Show Texture on Mon Mar 27, 2006 at 01:04:52 PM EST

Therefore, you get the -1 you deserve.


"Those people who think they know everything are a great annoyance to those of us who do." -- Isaac Asimov
[ Parent ]
I'm curious (none / 1) (#38)
by thefirelane on Mon Mar 27, 2006 at 08:38:21 PM EST

Are web queries possible in Open Office?
What is the VLOOKUP() equivalent in Open Office?

Thanks.

-
Prube.com: Like K5, but with less point.
[ Parent ]

Why OpenOffice? (none / 0) (#49)
by vadim on Tue Mar 28, 2006 at 01:21:21 PM EST

OO is for writing documents, and bloated enough already.

I'd use Perl -- which has the advantage of being a decent language, and not limited to spreadsheet specific uses, and perhaps something like Data::Dumper, Berkley DB or mysql/postgres for the storage.

Then, since the OO format is open, you can generate an OO document from it. Or you could generate LaTeX, HTML to use it as a CGI, use a graphing module to make a graph, etc.

I don't understand why you'd want to limit an useful tool to being single purpose when you can make it in such a way it's adaptable to many others.
--
<@chani> I *cannot* remember names. but I did memorize 214 digits of pi once.
[ Parent ]

vlookup in openoffice (none / 0) (#79)
by v1z on Wed Mar 29, 2006 at 12:27:05 PM EST

http://www.tutorialsforopenoffice.org/tutorial/Lookup_Tables.html

(first hit on google)

From what I know about different spreadsheets functions like vlookup are pretty standard fare. And just about everyone is emulating everyone else. I'm sure someone on here knows a bit more about the open document standard for spreadsheets and can shed som light on any differences.

As for web queries, it apperears oocalc supports it, but I'm not familiar enough with oo to give an example (and I'm typing this on a semi-broken debian amd64 etch install which doesn't have an oo package readily available).

In general OO2 has very good support for MS office formats, and I'd be surprised if you can't simply open your current xl sheet in oo, and have everything work.



[ Parent ]
ok... (none / 0) (#52)
by khallow on Tue Mar 28, 2006 at 02:02:58 PM EST

Use an Open Office or Gnumeric spreadsheet instead. There are other vendors compatible with Excel out there.

Stating the obvious since 1969.
[ Parent ]

Sorry, mistook you for the Excel bashers (nt) (none / 0) (#75)
by khallow on Tue Mar 28, 2006 at 11:34:08 PM EST


Stating the obvious since 1969.
[ Parent ]

you could go through all that effort (2.50 / 2) (#13)
by thankyougustad on Sun Mar 26, 2006 at 07:14:14 PM EST

or you could just use yahoo's great portfolio tracker which does all that shit and more, automatically.

No no thanks no
Je n'aime que le bourbon
no no thanks no
c'est une affaire de goût.

Thier Tracker (none / 0) (#15)
by thefirelane on Sun Mar 26, 2006 at 07:31:57 PM EST

Does their tracker compare each investment against the hypothetical investing of those funds in the S&P500? I didn't see it, but would be happy if it did. That is the most important thing, and I don't see it happening a lot.

-
Prube.com: Like K5, but with less point.
[ Parent ]
why is that the most important thing? (none / 0) (#17)
by thankyougustad on Sun Mar 26, 2006 at 07:45:15 PM EST

i'm not using a condescending tone here; i'm curious as to why you think the S&P is the most important benchmark. I don't know if yahoo uses it, I don't care because I never use it and am happy with my investments. I have always looked at past performance and P/E and have made nothing but solid, longterm investments. Since a lot of people aren't even investing in S&P companies it would seem to be a mostly arbitray benchmark. . .

No no thanks no
Je n'aime que le bourbon
no no thanks no
c'est une affaire de goût.

[ Parent ]
Good points (none / 0) (#18)
by thefirelane on Sun Mar 26, 2006 at 08:08:19 PM EST

I think I'll elaborate on this more in the article then.

The reason I chose a benchmark, such as the S&P500 has nothing to do with whether the companies you invest in are in the S&P500. Nor does it have anything to do with the criteria with which you use to choose stocks.

My article meerly describes a method that can be used to judge your past choices. I chose the S&P500 as a benchmark simply because so many investors do worse than it over the long term.

Once again, when completed, this spreadsheet would tell you if you would have more money now had you simply dumped all your funds into a benchmark, instead of choosing individual stocks.

Please let me know if this is still unclear, as I would like to clarify it in the article text.

-
Prube.com: Like K5, but with less point.
[ Parent ]

no no, its clear (none / 1) (#19)
by thankyougustad on Sun Mar 26, 2006 at 10:16:07 PM EST

but you should clarify in the article, maybe explain the difference between the s&p, and markets like nasdac, nyse, and the other smaller markets, and why people would want to track their investments in terms of the 'market.' also you should mention inflation, i think, and maybe make a column in the spread sheet for it.

No no thanks no
Je n'aime que le bourbon
no no thanks no
c'est une affaire de goût.

[ Parent ]
Useful rules of thumb (none / 0) (#95)
by xmnemonic on Sat Apr 01, 2006 at 12:17:22 AM EST

10% : 50-year average annual growth of the S&P 500
6%  : 50-year average annual growth of the S&P 500 when highs and lows were similar to those of 2003
3%  : Pessimistic estimated annual inflation of the USD... roughly averaged over the past 5 years (i.e. recovering 9-11 induced recession), it's probably 2.7%

These values are enough to plan your retirement portfolio, since almost everybody uses S&P 500 index funds for that.  

[ Parent ]

Another point (none / 0) (#22)
by thefirelane on Mon Mar 27, 2006 at 12:06:32 PM EST

I never use it and am happy with my investments

Just because I'm curious... how do you know you are happy? Because they have returned a positive amount? Could it be the case that even though both have done well, the market has done better? In the same vein, your 'loser' stocks might actually have lost less than the market.

I don't mean to pry, I'm just curious how you currently judge your own performance. Perhaps the overall returns are so high you are quite obviously beating the market... which is indeed a possibility for some. :)

-
Prube.com: Like K5, but with less point.
[ Parent ]

i generalize (none / 0) (#32)
by thankyougustad on Mon Mar 27, 2006 at 06:13:00 PM EST

every now and then i do look at how the s&p, nasdac, and nyse has been doing percentage wise, and compare that to my own stocks. i clobber them all so i'm happy.

No no thanks no
Je n'aime que le bourbon
no no thanks no
c'est une affaire de goût.

[ Parent ]
RE: Yahoo (none / 1) (#78)
by antijava on Wed Mar 29, 2006 at 10:29:22 AM EST

Yahoo seems to work on the surface, but it gets confused by even simple corporate actions like splits, and can't handle mergers, acquisitions spinoffs or dividends in your portfolio. Track your portfolio at Marketocracy if you want easy S&P500 comparisions and you don't want your share count messed up when stocks do funky things.

[ Parent ]
+1 MS Excel (3.00 / 3) (#24)
by Psychopath on Mon Mar 27, 2006 at 01:28:43 PM EST

I am just again discovering what nice things one can do with MS Office.

Of course you have to know what is possible with it and what's not. Using MS Access, for example, as a DBMS is of course not possible. Using it for a nice address book with some 100-1000s entries works very nicely.

Same with Excel. A nice little product!
--
The only antidote to mental suffering is physical pain. -- Karl Marx

I am just curious..... (1.75 / 4) (#25)
by terryfunk on Mon Mar 27, 2006 at 01:30:40 PM EST

but why use a spreadsheet? Why not something like mysql, sqlite, or even Access?

I like you, I'll kill you last. - Killer Clown
The ScuttledMonkey: A Story Collection

I forgot, OOorg Base.... (none / 0) (#26)
by terryfunk on Mon Mar 27, 2006 at 01:34:20 PM EST

which would replace M$ Access.

I like you, I'll kill you last. - Killer Clown
The ScuttledMonkey: A Story Collection

[ Parent ]
I feel I must Respond (3.00 / 3) (#45)
by thefirelane on Mon Mar 27, 2006 at 11:58:48 PM EST

All the comments have been along several lines, unrelated to the actual topic. I thought I'd respond to them all here:

You are using Excel!?

Yes, use the tool that solves the problem! How many times have we said this about a language? I say the same thing for using software. Does OS software do what do here? I honestly don't know, can any of you answer whether Open Office can do web queries and VLOOKUP functions?

Secondly, if I were to describe this article in non-vendor specific ways, it wouldn't be useful. Everyone knows they should simply compare the change in S&P500 versus change in their stock. What I'm showing, is a specific way of making a useful tool using a commonly held bit of software

I don't need a system like this

Why? The point of this article is to simply measure your system of choosing stocks. If you don't know how good you are, how do you know you should continue? It could very well be that you are paying money to continue your stock picking hobby.

I can do this online service X

Please provide a link if you can. Many websites track all your transactions, but I have not found a site online that compares each of your transactions against an objective index, and sums this up to see your overall ability

-
Prube.com: Like K5, but with less point.

I must respond too (1.00 / 5) (#50)
by vadim on Tue Mar 28, 2006 at 01:41:24 PM EST

Yes, use the tool that solves the problem! How many times have we said this about a language? I say the same thing for using software. Does OS software do what do here? I honestly don't know, can any of you answer whether Open Office can do web queries and VLOOKUP functions?

That, IMHO, is a completely braindead software design methodology.

You have a very simple task you want to accomplish. Why in the world would you want to lock it into a proprietary vendor's system, and also limit it to one unique function?

Why depend on a complex piece of software to do such a trivial task when it's possible to take say, Perl to implement the logic, store the data using a mechanism designed for it, then generate say LaTeX to obtain the output.

LaTeX doesn't *need* to do web queries. LaTeX is good at producing documents, Perl at parsing text, and BDB/postgres/whatever at storing data. Just combine a few good tools, and you'll be able to do things in a much more flexible manner.

Doing a bit more thinking you can implement it in such a way that you don't depend on a GUI, or a particular way of representing the output. The whole thing can be done in such a way that you can easily generate office documents, PDFs, send email, present it on a website, or pronounce it through a speech synthetizer.


--
<@chani> I *cannot* remember names. but I did memorize 214 digits of pi once.
[ Parent ]
my take on this (3.00 / 3) (#51)
by khallow on Tue Mar 28, 2006 at 02:01:12 PM EST

You have a very simple task you want to accomplish. Why in the world would you want to lock it into a proprietary vendor's system, and also limit it to one unique function?

It's not a proprietary format. You can easily port to Open Office or Gnumeric. Second, spreadsheets are ideal for this sort of work since it is fast and interactive. Combining perl, latex, and a database isn't because it isn't. OTOH, if I had to automate the filling out of an IRS tax form based on several hundred (or more) stock trades (something I actually considered at one time), then the latter system would be much more useful.

Stating the obvious since 1969.
[ Parent ]

huh (none / 0) (#108)
by mpalczew on Thu Apr 06, 2006 at 12:10:39 PM EST

> It's not a proprietary format.
ok, point me to the spec. then.  I've been looking for it.
-- Death to all Fanatics!
[ Parent ]
ROR @ pronounce it through a speech synthesizer (3.00 / 3) (#66)
by BottleRocket on Tue Mar 28, 2006 at 09:59:17 PM EST

Zero point eight four six five
One point four one three three
Zero point seven seven zero one
Zero point eight one nine two
Zero point eight nine six eight
Zero point nine two seven one
Zero point nine six five five
One point two one zero eight
One point zero zero eight four
Zero point nine eight three three
Zero point nine four two two

$ . . . . . $ . . . . . $ . . . . . $
. ₩ . . . . . ¥ . . . . . € . . . . . § . . . . . £
. . . . * . . . . . * . . . . . * . . . . . * . . . . . *
$ . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . $
Yes I do download [child pornography], but I don't keep it any longer than I need to, so it can yield insight as to how to find more. --MDC
$ . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . $
. . . . * . . . . . * . . . . . * . . . . . * . . . . . *
. ₩ . . . . . ¥ . . . . . € . . . . . § . . . . . £
$ . . . . . $ . . . . . $ . . . . . $
$B R Σ III$

[ Parent ]

Why so literal? (none / 0) (#77)
by vadim on Wed Mar 29, 2006 at 06:45:09 AM EST

"You're losing money!" and "Earnings are up 20 percent" are perfectly possible.
--
<@chani> I *cannot* remember names. but I did memorize 214 digits of pi once.
[ Parent ]
Great Idea (none / 1) (#97)
by Verbophobe on Sun Apr 02, 2006 at 01:57:59 AM EST

Why depend on a complex piece of software to do such a trivial task when it's possible to take say, Perl to implement the logic, store the data using a mechanism designed for it, then generate say LaTeX to obtain the output.

So, you know, it remains a trivial task instead of becoming something akin to ripping off your foreskin without anesthetic.

And, BTW, Gnumeric can read xls files.  Just saying.

Proud member of the Canadian Broadcorping Castration
[ Parent ]

I'm not sure you need to justify yourself (none / 0) (#55)
by elaineradford on Tue Mar 28, 2006 at 05:53:17 PM EST

I'm a little afraid to check my own stock-picking performance, but I like this topic, and I like that you've shown us how to do it step-by-step in Excel. I can't believe that the same crowd that is looking for lessons in squirrel hunting and how to pick up hookers at the local gas station is too sophisticated for Excel.

[ Parent ]
hot to use Excel? (2.00 / 3) (#57)
by United Fools on Tue Mar 28, 2006 at 07:01:37 PM EST

You know, there are many of us who do not know how to use Excel! What do we do?

We are united, we are fools, and we are America!
Why? (none / 0) (#58)
by jd on Tue Mar 28, 2006 at 07:20:16 PM EST

There are hundreds of stock tracking programs you can download for free, designed specifically to do all of this work without having to mess around with punching things in. Some even connect to online stock companies and allow you to do direct, automated trading.

If you do want to use a spreadsheet, why go for one that has features you'll never want or use? There's nothing in the article that would require anything more powerful than Visicalc - and you can't get much less powerful than that.

If you want serious overkill, then what's wrong with OpenOffice's spreadsheet? It'll work on most machines, most things that are valid on OpenOffice will be valid in Excel, but the converse doesn't hold up nearly as well.

Finally, tracking the value of stocks is rarely that useful. Stay diversified, buy when cheap, sell when doing well (but not yet topped out). That's the advice that most stock traders seem to give and they seem to do well from it.

Please read my other post (none / 0) (#60)
by thefirelane on Tue Mar 28, 2006 at 08:18:52 PM EST

here

Specifically these questions:

I honestly don't know, can any of you answer whether Open Office can do web queries and VLOOKUP functions?

I have not found a site online that compares each of your transactions against an objective index, and sums this up to see your overall ability

Thanks

-
Prube.com: Like K5, but with less point.
[ Parent ]

Yes, I can answer that (none / 1) (#63)
by BottleRocket on Tue Mar 28, 2006 at 09:25:25 PM EST

OpenOffice's Calc program does both VLOOKUP and web queries. Frankly, it annoys me to see "Excel spreadsheet" where what is really meant is just "spreadsheet".

$ . . . . . $ . . . . . $ . . . . . $
. ₩ . . . . . ¥ . . . . . € . . . . . § . . . . . £
. . . . * . . . . . * . . . . . * . . . . . * . . . . . *
$ . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . $
Yes I do download [child pornography], but I don't keep it any longer than I need to, so it can yield insight as to how to find more. --MDC
$ . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . $
. . . . * . . . . . * . . . . . * . . . . . * . . . . . *
. ₩ . . . . . ¥ . . . . . € . . . . . § . . . . . £
$ . . . . . $ . . . . . $ . . . . . $
$B R Σ III$

[ Parent ]

kinda silly (none / 0) (#83)
by speek on Thu Mar 30, 2006 at 07:52:26 AM EST

I thought you were going to show me a great way to measure returns for an account to which one is continually adding some constant amount. Anyone who can add, subtract and multiply can figure out their returns on an initial lump sum.

--
al queda is kicking themsleves for not knowing about the levees

I do this.. (none / 0) (#84)
by thefirelane on Thu Mar 30, 2006 at 08:27:09 AM EST

I add a constant amount to my account, and this spreadsheet can accomidate that. You just add another transaction each time you do a purchase.

-
Prube.com: Like K5, but with less point.
[ Parent ]
Stock Picking (none / 0) (#86)
by Grayworld on Thu Mar 30, 2006 at 05:36:40 PM EST

is not worth most people's time. But if you are going to do it, then you need to spend every available minute researching your portfolio and as little as possible measuring your return. In fact, I find looking at my account balances on line every few days works quite well in measuring my returns against the S&P or other benchmarks.

And as far as picking stocks, I find that spending some money on good investment letters leverages your time much more effectively than trying to search through a universe of thousands of publicly traded companies. The same is true in searching for mutual funds (Schwab offers decent mutual fund research if you have an account there).

It seems to me that most people ought to invest the bulk of their funds in ETF's or mutual funds and keep back a little slug of investable cash (10% to 20%) to go for the 5, 10 or 20 baggers using IBD or services like Motley Fool Hidden Gems. Getting a couple of those grand slams in your life is worth some time trying to achieve.


Fair but a bit unbalanced to be sure!

Question (none / 0) (#87)
by thefirelane on Thu Mar 30, 2006 at 06:19:50 PM EST

In fact, I find looking at my account balances on line every few days works quite well in measuring my returns against the S&P or other benchmarks.

I'm curious though, how, by looking at the balances of your account do you know that all those transactions beat the S&P 500? That's what I was trying to get at with the article. Not so much a system that people could use to constantly check... but a system that was objective and definitive. How do you go about it?

-
Prube.com: Like K5, but with less point.
[ Parent ]
well your question becomes more of an issue the (none / 0) (#88)
by Grayworld on Thu Mar 30, 2006 at 07:14:38 PM EST

greater the number of positions you have and whether you are a short term trader or long term investor, and if the latter, what is the strength of your conviction relating to the fundamental analysis you've done on your positions.

For instance, if you're a trader, you're looking for small returns over a short period on a number of transactions. You really can only measure success in a situation like that by taking snapshots of the net result of your various trades over a given period, a week, a month, a quarter, or whatever, and compare that to your benchmarks for that period. It makes little sense to be measuring each short term trading position against the S&P for example because the point of short term trading is to measure your success against your short term trading targets, frequently set by "buy rumor, sell news" type analysis or by reference to technical (chart reading) analysis and seldom set by reference to serious fundamental analysis. These targets (which can be set with respect to short or long positions by the way) are likely to always exceed what you expect the S&P to do over your trading time horizon.

So you run a high volume of trades according to your trading metrics and at the end of every month or so, you look at whether all the effort was worth it by seeing if your account has gone up by more than if you had put the cash in an ETF. If you begin to see a pattern that you're not beating the ETF's or the S&P, you get smart and hand your money over to a mutual fund or managed account or put it in ETF's and ride the averages.

Now if you're a long term holder, using benchmarks makes more sense. But here, since your time frame is much longer, you don't necessarily care about monthly or quarterly S&P performance. You want to measure your performance against annual or 18 month or even longer time frame benchmarks.

I say that for instance because you know how good stocks can run into periods of high volatility where you can be up 20% in four months and then see 75% of that gain go away in wave of profit taking over a two or three week period, only to reconsolidate and move up another 25 to 30% over the next twelve months.

If you look at that position and compare it to the S&P during the month of the sell off, you look pretty bad and begin to think of selling off the position to cut losses. But if the sell off is not due to any clear fundamental reason, and you believe in your DD and other fundamental analysis, you hold on and even add to your position in these periodic sell offs. Then your overall return gets juiced on the rebound.

This is what I meant when I said fundamental analysis and conviction are far more important than looking at the S&P for longer term investors because the time to look at the benchmarks is more over annual, or longer (sometimes much longer) periods. Because longer term horizons require more fundamental analysis, you'll find that won't be able to have that many positions because you can't follow fundamental developments for more than 5 to 10 stocks at a time.

Again though, whatever that time frame is, if you find you are not beating the averages, then it's back to the mutuals, managed accounts and/or ETF's with the cash.

Probably the most successful investors use a combination trading/investing strategy. They do enough DD and fundamental analysis to get comfortable that a particular stock is a long term winner. They then spend alot of time studying the trading patterns for the stock so they know its trading habits relative to the market, its peers, seasonality and so on.

All this leads to taking a core position in the stock, which they hold over their long term horizon. They then apply their knowledge of the stock's trading habits to trade in and out of it around that core position. Profits are commonly viewed as reducing the net cost of the core position and again the S&P and such are less of a target than what the investor feels the potential of the long term core holding is over a given period of time.

If it isn't obvious already, I'll be the first to admit I'm certainly not a know it all about this stuff as evidenced by the fact I lost a good part of my ass during the bubble burst. I will say though that I am finally starting to see my balances tick up nicely again, not in the least because I haven't forgotten the lessons I learned a few years ago.

Happy trading (or investing) to you!


Fair but a bit unbalanced to be sure!
[ Parent ]

Isn't following the advice of investment letters (none / 0) (#100)
by skyknight on Sun Apr 02, 2006 at 09:05:57 PM EST

a guaranteed way to get to the party late?

It's not much fun at the top. I envy the common people, their hearty meals and Bruce Springsteen and voting. --SIGNOR SPAGHETTI
[ Parent ]
Must Mention Markowitz, Sharpe, & Miller (none / 0) (#90)
by rfhayes on Fri Mar 31, 2006 at 08:36:57 PM EST

A nice illustration of the impact of explicit transaction costs on investing. But even without these costs, it is *impossible* to beat the market over time by stock picking. You can increase your return by accepting increased risk, but eventually that increased risk will come home to roost. Harry Markowitz, along with William Sharpe and the late Mertron Miller, demonstrated this long ago. Yes, some stock picking portfolio managers may appear to do that well, but there are no more of them than the expected distribution of results would lead you to expect. There are portfolio managers who might actually be beating the market by exploiting inefficiencies in the way the market understands risk correlation. In some sense they are cousins of the firms that made money by implementing Black-Scholes on their option trading desks. In options, the advantage was born of a deeper understanding of the market structure. Eventually, everybody used an option pricing model and the easy money was no more.

Another gift from Markowitz: Index funds may be a better bet than ETFs like Spyders. The holdings of many index funds aren't *exactly* the same as the index. Instead, they hold an efficient subset (what Markowitz called a corner portfolio). This allows them to mirror the performance of the index without incurring the transaction costs (explicit and implicit) of trading the index exactly.

If anyone is interested on all of this, there is a spectacularly good book on the topic: "Active Portfolio Management" by Richard Grinold and Ronald Kahn.

-rfh

rfh (none / 0) (#92)
by Grayworld on Fri Mar 31, 2006 at 11:50:16 PM EST

Do we want to leave readers with the impression that they should not expect to beat the market over the long term and, therefore, should simply stick to index funds?

Your piece inspired me to do a little research as to whether the average investor had access to stock pickers that would consistenly beat the averages for them over both the short and long term. I was surprised to find there were a number of them in the form of 5 Star mutual funds that significantly beat the S&P over each of a 3 month, 1 year, 3 year, 5 year and 10 year time horizon.

The symbols of the first 6 that came up were UMBIX, SCUIX, VLEOX, RIMSX, TBAIX, and AMAGX. These were all available through Schwab without loads or fees. Their expense ratios ran from 1.09% or so to 1.69%.

Now admittedly I do not know how tax efficient these funds were over these periods. But the outperformance was so significant over each time frame that taxes would have had to have been very high to negate the tax efficiency that is a trademark of index funds.

It may very well be true that most pros and amateurs will not outperform the averages. But prospective investors should realize that some of those extraordinary consistently outperforming stockpickers are out there for hire.


Fair but a bit unbalanced to be sure!
[ Parent ]

Size of fund. (none / 0) (#99)
by vectro on Sun Apr 02, 2006 at 08:10:30 PM EST

I will concede that I haven't researched your funds in particular, but one thing to consider is the size of the fund in question. It's relatively easy to beat the market with small investments -- if you are managing, say, less than $50,000. But once you get into the hundreds of millions of dollars, your actions start to affect the market itself, and it becomes much more difficult to maintain high returns.

Knowing this, one of the latest tactics of fund managers is to open a fund to only select investors, and keep it small for a while -- say, 5 or 10 years. After it's had enough time to make a record for itself, the fund opens the gates to the public, who buys on the basis of this record -- but now, the size of the fund being much higher, the astonishing results on record are no more, and these "late to the game" investors end up paying high fees for mediocrity.

Another, related trick is to incubate a fund under a "star" manager, but then flop to somebody cheaper when the amount of money under management gets too big. This is good for the star -- he doesn't get a record of lackluster performance while managing the big fund -- but it's even better for the management company, who doesn't have to pay even higher salaries as the fund grows.

Again, I don't know if either of these concerns apply to the funds you mentioned in particular, but it is important to understand that the fund you buy today is not necessarily the same as the fund whose results you see in the prospectus.

“The problem with that definition is just that it's bullshit.” -- localroger
[ Parent ]

Yup (none / 0) (#104)
by Grayworld on Mon Apr 03, 2006 at 12:03:50 PM EST

I agree the size of the fund makes a difference. A good example of that was how difficult it became for the Fidelity Magellan Fund to beat the averages after Peter Lynch built its size to gargantuan proportions.

But again, there is no doubt that as a general rule, most funds do not beat the market. It's the funds that have consistently beat the market over the long term that investors should be looking for, using tools like morningstar ratings. And yes it is also true that a fund can change overnight if it's success is attributable to a single stock picker that retires. However, that consideration, how the funds pick stocks, is one that should enter into the decision as to which fund to invest in. Do they have a system or are they are a one man show?

And just like stocks, you have to stay on top of what is happening with your fund investments.


Fair but a bit unbalanced to be sure!
[ Parent ]

I thought (none / 0) (#105)
by Grayworld on Mon Apr 03, 2006 at 05:53:34 PM EST

you'd find this little article to be very interesting. It basically supports rfh's views on the matter of stock picking.

.
Fair but a bit unbalanced to be sure!
[ Parent ]

This is not new (none / 0) (#94)
by xmnemonic on Sat Apr 01, 2006 at 12:03:17 AM EST

Jim Cramer's "Real Money"
Ben Graham's "Intelligent Investor"
David Gardner's "Motley Fool Investment Guide For Teens"

All of these books, ranging from the faddish to the classical to the juvenile, state that index funds often do outperform most casual investors' stock portfolios.  This is no secret.

Yet how many people act on that advice... (none / 0) (#96)
by thefirelane on Sat Apr 01, 2006 at 09:44:10 AM EST

because they think "I'm smarter"... I have the feeling that especially among this crowd, it is high, so I thought it would be especially usefull to see the real numbers

-
Prube.com: Like K5, but with less point.
[ Parent ]
What a stupid guide (none / 0) (#98)
by Highlander on Sun Apr 02, 2006 at 05:57:25 PM EST

You completely forgot the payouts aka dividends.

But I suppose it is the American way to do stocks, just like running up the largest foreign debts never to be paid back.

Moderation in moderation is a good thing.

Please type your apology below... (none / 1) (#102)
by thefirelane on Sun Apr 02, 2006 at 11:34:06 PM EST

From the article

As a final touch, insert one more row under each stock that has paid a dividend. In the example file, I included a 100 dollar dividend from PTR. This value must be added as a negative in order for the math to work

I suppose your country doesn't stress reading comprehension then?

-
Prube.com: Like K5, but with less point.
[ Parent ]

Ok, I apologize, but .. (none / 0) (#106)
by Highlander on Mon Apr 03, 2006 at 07:08:20 PM EST

Well, but, something is wrong in your set up.

I don't quite get why you would add a row when every other data is placed in columns. And if you add a column, you might as well place it in the head of the instructions with all the others. This would also have allowed you to do away with the instructions on putting a minus sign in front.

I would also guess that anyone capable of ironing out the small details in your spreadsheet or even just capable of adding the formulas by himself would be able to do all steps but step 5 by himself. Regarding the most obvious wishes to me would be to fetch the dividends from somewhere as well, as well as proper calculation of reinvesting.

I suspect the people who will profit from your write most are the ones who will just copy your spreadsheet. But maybe I am wrong, I heard some brokers have dual opterons to run their excel apps.

Lastly, if one should trust anyone to do the math with your money, he'd better not add dividends as an afterthought, but maybe that is just me..

Moderation in moderation is a good thing.
[ Parent ]

I agree (none / 0) (#103)
by lukme on Mon Apr 03, 2006 at 07:30:55 AM EST

It is overly simplified.

In fact, I had set up a similar spreadsheet 10 years ago, when I started investing. After investing for a while, I found it rather useless.




-----------------------------------
It's awfully hard to fly with eagles when you're a turkey.
[ Parent ]
Loans (none / 0) (#109)
by frozenfruit on Tue Apr 11, 2006 at 01:23:33 PM EST

The United States hasn't defaulted on any debts yet!

[ Parent ]
foreign stocks (none / 0) (#107)
by brinded on Mon Apr 03, 2006 at 08:23:42 PM EST

Thanks for the spreadsheet ideas - I've been trying to figure out how to use the lookup function for a while. One problem I now have is that MSN doesn't cover all European exchanges - in particular I need to find the ticker for Vestas in Denmark. Their ticker on yahoo is VWS.CO (Copenhagen), or VWS.DC also works. (note: there's a related stock on a German exchange but I need to track the price in Danish Kr.) Any help appreciated.

Hmm (none / 0) (#110)
by MissMatch on Sat May 13, 2006 at 03:13:33 PM EST

I am sure serious investors/smart investors have something like this, i doubt people randomly buy and sell stocks without calculating returns, but then agian, I thought it was common sense to have a chart like this etc. But this is a very good guide for those of who randomly buy/sell.

Beating index-funds, the easy way. (none / 0) (#111)
by Eivind on Tue May 30, 2006 at 07:09:13 AM EST

Actually it's easy to invest in such a manner as to, on the average, beat index-funds.

The method has two steps, they run like this:

  • Buy random stocks.
  • Wait.

Index-funds in general, by definition do almost as well as the market the indexes cover. (if not, the index-fund and/or the index itself sucks)

I said "almost" because the fund has associated costs, and transaction-costs, neither of which exist in the index itself. (the index is typically only a weigthed average of some stock-prices)

So, by minimizing transaction-costs and the costs imposed by the funds (the brookers working for the fund lives from something...) you end up beating the index-funds with, on the average precisely that much.

For example, if I wanted to track the Norwegian OSEBX index, I could buy an index-fund and immediately pay oh, around 1% in fees, and then thereafter 0.5% yearly for administration, plus actual transaction-costs, perhaps 0.2% or so.

Buying random stock cost me around 0.5% assuming I invest around $2000 in a single stock. (less if I invest more), yearly administration and transaction-costs thereafter are null, beating the index-fund by 0.5% to 0.7% on the average.

The only drawback is that your risk (in the sense of expected deviation, both positive and negative) will be higher unless you have enough to invest that you can spread it on *many* stock, and at the same time invest atleast $1000 or so in each to avoid the purchase-cost being prohibitive.

In practice, this only makes sense if you've got $25.000 or more to invest.



import live data? (none / 0) (#113)
by jago25 on Sat Nov 18, 2006 at 11:35:49 AM EST

I've always wanted to get interesting data sources into Excel for playing with.

Unfortunately this method means typing it in manually. Can't live data be brought in?

Should you bother picking stocks? A simple Excel Spreadsheet | 113 comments (90 topical, 23 editorial, 1 hidden)
Display: Sort:

kuro5hin.org

[XML]
All trademarks and copyrights on this page are owned by their respective companies. The Rest © 2000 - Present Kuro5hin.org Inc.
See our legalese page for copyright policies. Please also read our Privacy Policy.
Kuro5hin.org is powered by Free Software, including Apache, Perl, and Linux, The Scoop Engine that runs this site is freely available, under the terms of the GPL.
Need some help? Email help@kuro5hin.org.
My heart's the long stairs.

Powered by Scoop create account | help/FAQ | mission | links | search | IRC | YOU choose the stories!