Re: Excel Portfolio Tracker

["W Lake" <wlake@xxxxxxxxx>]

This has lots of suggestions about how you might want to set up your columns

http://www.marginaccountcalc.com/home.html

also this

http://www.bostoninvestment.com/products.htm

Best regards

Walter

----- Original Message -----
From: Marshall Liu
To: metastock@xxxxxxxxxxxxx
Sent: Monday, June 04, 2001 2:37 PM
Subject: RE: Excel Portfolio Tracker


Sounds like you only want to sum/track the closed trades.

If so, here is how I do it using the following columns in Excel:
I will describe each column's data rather than give specifics.

If not stop reading right here.

You need input values that are provided by the user in columns 1 thru 6
FORMULAS in columns 7 thru 10 refers to specific math functions built into
Excel.

You will need a SUBTOTAL function (see Excel help) on top of the labels in
columns 5, 6 and 7.
The specific Excel SUBTOTAL function is =SUBTOTAL(9,F5:F104).
The "9" value will sum the amounts in the range (F5:F104). There are other
values that do average, maximum, minimum, etc... functionality you might
want to check out on your own.

Finally, you need to apply a filter over each of the labels in columns 1
thru 10.
Filters can be access using "File | Data | Filter" from the Excel menu.

col 1) Security Description - Input value
col 2) # Shares / contracts - Input value
col 3) Date Acquired - Input value
col 4) Date Sold - Input value
col 5) Net Sales Price - Input value
col 6) Cost or Basis - Input value
col 7) Gain / (Loss) - FORMULA (col 5 - col 6)
col 8) Holding Period - FORMULA (ABSOLUTE VALUE of (col 3 - col 4))
col 9) L/T or S/T - FORMULA (IF col 8 > 365,Then "Long Term", Else "Short
Term")
col 10) Open / Closed Position - FORMULA (IF col 3 > 0 and col 4 > 0, , Then
"Closed", Else "Open")

Basically, you enter your long/short trades as you place them or complete
the roundtrip.
The info in columns 7 thru 10 are calculated for you.
The SUBTOTAL function will sum the values in each of the columns 5 - 7.
As you use the filter to limit your views to whatever criteria you specify,
the summed amounts will change.
I'm sure there are other uses.

-----Original Message-----
From: owner-metastock@xxxxxxxxxxxxx [mailto:owner-metastock@xxxxxxxxxxxxx]On
Behalf Of neo
Sent: Monday, June 04, 2001 5:10 AM
To: metastock@xxxxxxxxxxxxx
Subject: Excel Portfolio Tracker





----------
From:   neo[SMTP:NEO1@xxxxxxxxx]
Sent:   Monday, June 04, 2001 5:10:01 AM
To:     metastock@xxxxxxxxxxxxx
Subject:         Excel Portfolio Tracker
Auto forwarded by a Rule


I would like to set up a spreadsheet to track all of my trades after they
are closed with a running summary. Is there a way with Excel to add all of
the values in a column without specifying the end of the column?

thanks, neo