Tip #2002-10: Position Monitoring with Excel
Recent Willow Tips have focused on using the features of Excel to analyze real-time and
historical data to develop financial models. Excel can also be used to help you monitor and
analyze the holdings in your portfolio. With the financial markets becoming increasingly
volatile, keeping track of your holdings, becomes critically important. If you are a
portfolio manager or trader measuring performance and controlling risk becomes as important
as making buy and sell decisions.
With stocks being bought and sold over a period of time at different prices it becomes
difficult to quickly figure your profit and loss. A lot of time, effort and computing power go
into tracking positions and generating customized profit and loss reports. The greater the
number of transactions the more formidable becomes the task of computing the profit or loss
on a specific position. Excel has some database analysis tools which allow you easily eliminate
the unnecessary data and get the details on specific holdings.
However it is important to note that Excel is not a relational database. Most operating information, such as buy and sell
transactions, are stored in large relational database applications such as SQL Server or Oracle.
Although this Information is stored in an external database Excel has an Import External Data feature
which allows you to easily bring information from an external relational database into Excel. You can
also query a relational database programmatically using VBA. In this Willow Tip we are going to discuss
some of Excel’s database analysis features which you can use to analyze the data after you have imported
it form a database. These tools simplify the process of monitoring trading positions and can help you
make sense of large amounts of buy and sell transactions.
We have created a workbook which contains a simple database of buy and sell transactions. It
uses Excel’s Auto Filter in combination with the SUBTOTAL function to analyze a database of
investment holdings. Using these features together makes is easy to find the average cost of a
position. Download this sample workbook to see how these Excel features can help you monitor
positions. The workbook contains comments which guide you on how to use these features.
Using Excel’s SUBTOTAL Function
Willow Solutions Inc. is a Microsoft Certified Partner with proven expertise and knowledge in developing
proprietary applications used primarily in the financial services industry. The company specializes in solutions
that combine the power of Microsoft Excel and Visual Basic, with an in-depth knowledge of financial markets.
Contact us if you would like to employ our services to help you enhance this model or help you design financial
models or customized applications.
|
