|
PureBytes Links
Trading Reference Links
|
Sounds
like you only want to sum/track the closed trades.
<FONT face=Arial color=#0000ff
size=2>
If so,
h<FONT face=Arial color=#0000ff
size=2>ere is how I do it using the f<FONT
face=Arial><SPAN
class=430580118-04062001>ollowing columns in
Excel:
<SPAN
class=430580118-04062001>I will describe each column's data rather than give
specifics.
<SPAN
class=430580118-04062001><FONT
size=2><SPAN
class=430580118-04062001>
<SPAN
class=430580118-04062001>If not stop reading right
here.
<SPAN
class=430580118-04062001>
<SPAN
class=430580118-04062001>You need input values that are provided by the user in
columns 1 thru 6
<SPAN
class=430580118-04062001>FORMULAS in columns 7 thru 10 refers to specific
math functions built into Excel.
<SPAN
class=430580118-04062001><FONT
size=2><SPAN
class=430580118-04062001>
<SPAN
class=430580118-04062001>
<SPAN
class=430580118-04062001>You will need a SUBTOTAL function (see Excel
help) on top of the labels in columns 5, 6 and
7.
<SPAN
class=430580118-04062001>The specific Excel SUBTOTAL function is
=SUBTOTAL(9,F5:F104).
<SPAN
class=430580118-04062001>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.
<SPAN
class=430580118-04062001>
<SPAN
class=430580118-04062001>Finally, you need to apply a filter over each of the
labels in columns 1 thru 10.
<SPAN
class=430580118-04062001>Filters can be access using "File | Data | Filter" from
the Excel menu.
<SPAN
class=430580118-04062001>
<SPAN
class=430580118-04062001><FONT
face=Arial>col
1) Security Description - Input value
<SPAN
class=430580118-04062001>col 2) # Shares /
contracts - Input value
<SPAN
class=430580118-04062001>col 3) Date
Acquired - Input value
<SPAN
class=430580118-04062001>col 4) Date Sold
- Input value
<SPAN
class=430580118-04062001>col 5) Net Sales
Price - Input value
<SPAN
class=430580118-04062001>col 6) Cost or
Basis - Input value
<SPAN
class=430580118-04062001>col 7) Gain /
(Loss) - FORMULA (col 5 - col 6)
<SPAN
class=430580118-04062001>col 8) Holding
Period - FORMULA (ABSOLUTE VALUE of (col 3 - col
4))
<SPAN
class=430580118-04062001>col 9) L/T or S/T
- FORMULA (IF col 8 > 365,Then "Long Term", Else "Short
Term")
<SPAN
class=430580118-04062001><FONT
size=2><SPAN
class=430580118-04062001>col 10) Open / Closed Position - FORMULA (IF col
3 > 0 and col 4 > 0, , Then "Closed", Else
"Open")
<SPAN
class=430580118-04062001>
<SPAN
class=430580118-04062001>Basically, you enter your long/short trades as you
place them or complete the roundtrip.
<SPAN
class=430580118-04062001>The info in columns 7 thru 10 are calculated for
you.
<SPAN
class=430580118-04062001>The SUBTOTAL function will sum the values in each of
the columns 5 - 7.
<SPAN
class=430580118-04062001>As you use the <FONT
face=Arial><SPAN
class=430580118-04062001>filter to limit your views to whatever criteria
you specify, the summed amounts will change.
<SPAN
class=430580118-04062001>I'm sure there are other
uses.
<SPAN
class=430580118-04062001><FONT
size=2><SPAN
class=430580118-04062001>
-----Original Message-----From:
owner-metastock@xxxxxxxxxxxxx [mailto:owner-metastock@xxxxxxxxxxxxx]On Behalf
Of neoSent: Monday, June 04, 2001 5:10 AMTo:
metastock@xxxxxxxxxxxxxSubject: Excel Portfolio
Tracker
<FONT face=Arial
size=2>----------
From: <FONT face=Arial
size=2>neo[SMTP:NEO1@xxxxxxxxx] <FONT face=Arial
size=2>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?
<SPAN
class=490020512-04062001>
<SPAN
class=490020512-04062001>thanks,
neo
|