I use a spreadsheet to keep track of my trades.
Once upon a time I decided to stick a version of that spreadsheet on my website ... here.
Now, when I look at it, no rational person would want to use it.
>And what does that say about you?
So I decided to do another one, more like the contest spreadsheet described here.
>Why not just use that contest spreadsheet?
The contest spreadsheet assumes you start with umpteen dollars and never deposit new money or withdraw money.
You just trade with what you got.
Anyway, in this new spreadsheet, you:
- Start the spreadsheet by sticking in the starting date and the amount of CASH you have ... cells A5 and H5, below
- Then stick in all the shares you hold as of that starting date ... stocks or funds ... columns A, B and D as of Feb-20-04
- Then type all the stock or fund symbols in a LIST ... the ones you hold and any that you're interested in ... column K
(You can modify this LIST at any time.)
- Then click the CALCULATE button and it'll tell you what your PORTFOLIO is worth ... cell J2
(The number of shares you hold of stocks on that LIST is calculated, in column L,
and the latest prices of ALL stocks is downloaded from Yahoo, in column O.)
>Why don't you give a picture of the spreadsheet so I know what you're talking about? I rarely understand ...
It looks like this:
In this example:
- We type in our starting Cash amount (on Feb-20-04) in cell H5, namely $25,000.
- Then we've typed in the stocks or mutual funds we own in columns A - D.
(300 shares of vfinx and 1000 of vwelx and 500 of ge etc.)
- Then we click the CALCULATE button and get the value of our PORTFOLIO appears in cell J2.
(In the example above, it turned out to be $108,227 for Feb-20-04.)
- We then click the Record PORTFOLIO button and that value gets put into column I.
(In the example above, the $108,227 gets put into cell I8.)
- Thereafter, if we deposit extra money into our PORTFOLIO we stick in the DATE and Amount and click two buttons
(steps 3 and 4).
(Example: we put an extra $5,000 on Mar-1-04. Note that CASH increased by $5,000.)
(After the two clicks we get the current PORTFOLIO of $113,227 put it into cell I9 ... as of Mar-1-04.)
- When we buy or sell we enter the DATE, SYMBOL, PRICE, #SHARES and any FEE charged for the trade.
(Example: On Mar-23-04 we bought 200 ge at $38.25 and the Fee was $15.
The cost for these shares was $7650, shown in cell G10.)
(Do the two clicks and get the current PORTFOLIO of $113,053 put into cell I10 as of Mar-23-04.)
- Then, on Apr-2-04 we withdrew $500 and ...
Wait! There's more!
You can click the CALCULATE button at any time ... just to see what your PORTFOLIO is worth
(You then wouldn't click the Record PORTFOLIO button since you're "just asking".)
After you click CALCULATE (and get your most recent PORTFOLIO),
you can also get some Returns:
Note: In the above example we put our start Date as Jan-1-04, but did NOT record a portfolio value on Jan-1-04.
- Click the CAGR button (G3) to get your Annualized Return (or Compound Annual Growth Rate) in cell H3.
- Type in some start Date in cell F2 then click the YTD (G2) button to get your total return since that date, in cell H2.
This is meant to give Year To Date return from Jan 1 of the
current year, but you can try any start Date.
Just be sure you've Recorded your Portfolio on that Date
In this case, the YTD number makes little sense ^$%#@!*?
If you think you may want the Year-To-Date return then, when the start of the year rolls around, be sure to enter that Date in column A
... then do the two clicks to record your PORTFOLIO on that Date, like so:
No it isn't!
To start the spreadsheet you:
... after that you need only type in the info for each new transaction and click a button or two.
- Enter a starting Date and the number of shares of each stock or fund that you hold and your CASH amount.
- Type the LIST of stocks or funds you're interested in.
- Click two buttons.
What could be easier?
To download the spreadsheet, click on the picture above or, if that don't hardly work,
RIGHT-click on the picture and Save Target.
Note that there are four buttons:
CALCULATE to compute your current portfolio value. ... click it any time you're on the net, just for fun.
Record PORTFOLIO to enter the current portfolio value in column I ... assuming you want it recorded there.
You can enter or delete this stuff in column I yourself, if you like. The Record PORTFOLIO button is there if you're too lazy, like me.
YTD to give the total return from the specified start Date ... remembering that it's NOT an annualized return.
CAGR to give the CAGR from the starting date of the spreadsheet ... this IS an annualized return.
The spreadsheet uses the Excel XIRR function to calculate the annualized return, using all the cash flows into and
out of your portolio since the spreadsheet starting date.
On the other hand, the YTD button calculates the annualized return from the specified start Date
in F2, using just the cash flows into and out of your portfolio since the that Date. But then it scales this value to the time period from the
start Date to the current date.
Just be sure you've Recorded your Portfolio on that Date
For example, if the annualized return is 8.9% (that'd be for a 365 day time period)
and the time period is just 234 days (from your specified start Date to the current date),
then the YTD value
is calculated as: (1.089)234/365 - 1
= 0.056 or 5.6%
Each button calls upon a macro which (hopefully) will perform the necessary calculations.
However, you may have to reduce the security level of your Excel to allow the macros to run
And there are some charts. For example ...
The spreadsheet changes from time to time so may not be exactly as shown.
(Isn't that what they all say?)
There's also a sheet which plots neat charts. It looks like this:
You stick the Yahoo symbols all-in-a-row (like
vfinx, vbmfx, vivax, veurx, xom etc.)
then click a button or three.
It's explained like so:
>Do you guarantee that the spreadsheet actually ...?
Actually works? Uh ... I offer a money-back guarantee.
And did I mention that you get a bunch of correlations?
... with some fixes and a better explanation.
Spreadsheet last updated Feb 6, 2005
>Something wrong with the above explanation?
Well ... I think it's confusing.
>No question about that!
Okay, so there's now an Explain sheet which looks like this:
There are also some mods to the last sheet which plots stuff
... and this spreadsheet is constantly
(and it keeps gettin' bigger)
Oh, I almost forgot.
If y'all get cash dividends then you want your CASH to go up as well as your
Current Portfolio, so stick in a negative Fee.
The Fee just gets subtracted from CASH, so if it's negative then ...
>It gets added.
There's a much simpler Portfolio Tracker described here, in case y'all want to do your own charts etc. etc.