|
You are probably familiar with using Excel’s subtotal function to perform a common
mathematical calculation such as sum, average, count etc. on a table of data. The advantage
of using the SUBTOTAL function rather than the SUM or AVERAGE function is that
the SUBTOTAL function ignores hidden cells. This makes it particularly useful when used in
combination with Excel’s Auto Filter, which hides rows of data which do not meet specific
criteria. Excel has a wizard which guides you through the process of using the subtotal function.
You can apply subtotals to a range of data by selecting Subtotals… from the Excel’s Data menu. You can
also enter the SUBTOTAL function manually:
| =SUBTOTAL(function number, range) |
The SUBTOTAL function has two parameters. The first is function number. The function number
specifies which mathematical operation will be done to calculate the subtotals. If you wanted
the subtotals to be sums the function number would be 9. If you wanted the subtotals to be an
average the number would be 1. Check Excel’s help for additional details. The second parameter
is the range of cells you want subtotaled.
Our example has an All Positions Database section which contains a database of long transactions
for a group of equity instruments. This is the section to which you would download all your
positions from your database query. In our example there are fields for the Date, Symbol,
Shares, Cost Price and the Dollar Cost Value which is the number of shares multiplied by the
price. The down pointing triangles in the cell containing the field headings indicate that
Auto Filter has been applied to the database. The Auto Filter feature allows you to display
only the records in the database which meet specific criteria. For example, in order to display
only the transaction records for Pfizer, you would click on the triangle in the Symbol field
and select the symbol for Pfizer, (PFE). Rather than seeing all your positions, only the long
positions for Pfizer will be displayed.
In the Position Summary section of the worksheet we have entered the SUBTOTAL functions
which sum the Shares and the Dollar value of the positions in the database. When the Auto
Filter is applied only the visible cells are summed. If we selected to display only the records
for Pfizer, we would see the total number of shares and dollar value for this position.
Dividing the dollar value by the number of shares would give us our average cost price or
breakeven price. Comparing the break even price with the real-time last price lets you see
immediately whether you have a profit or loss. If you multiply the number of shares for the
position by the real time last price you will get the current dollar value of your position.
Subtract the current dollar value from your average cost dollar value to get the dollar value
of your profit or loss for that specific position. By clicking on the Auto Filter in the
Symbol column and making a selection you can easily get the total number of shares, the dollar
value and average cost of any position in your database.
In our example we have created a small equity data base with only a few positions. In a real
world example, the database would probably contain several hundred rows. The greater amount of
data the more useful become the Auto Filter and the SUBTOTAL function.
If you are financial market professional you will probably be able to apply this technique to
find the profit and loss of many types of investment and trading strategies.
|
