Retail Investors and Microsoft Excel

Retail Investors and Microsoft Excel

Retail Investors and Microsoft Excel

If you dabble in stocks and shares, you will probably want to keep detailed records of your investments. You may also want to experiment with different trading strategies using historical data. The only way to analyze daily share movements is to use spreadsheets built in Microsoft Excel. It's the one area where the retail investor can use the same tools as Wall Street.

First things first, how do you get price data into Excel? Chances are that you don't have your own Bloomberg terminal linked up to the stock exchange so you'll be relying on price data from the internet. I would recommend Yahoo Finance, as they let you download csv files containing full historical information for all major stock symbols. Csv files are spreadsheets, just without any formatting. The alternative is simply to copy a table of information directly from your web browser. You can quickly develop a database containing all the information you need.

Then you will need to record the contents of your portfolio for each and every day. The simplest way to do this is to record the number of shares held in each company in your database. The value of your portfolio is the amount you have sat in cash plus the number of shares multiplied by the individual close prices. To calculate the amount sat in cash, you will need to see where buy/sell instructions were executed e.g. there was a change in the amount of shares held.

Once you have a basic spreadsheet, you can increase the level of complexity. First, you can plot charts of your portfolio's performance. You can also add in deductions for any fees/transactional costs incurred in managing your portfolio. If you are buying many different shares, it may be impractical to have a column stating your position in every single one of them. Especially if you sold out of most 5 years ago. At that point, you may wish to start using lookups.

Of course, the holy grail is to have a simple spreadsheet in which you can view your portfolio value/return over any given time period, and you only need to enter the buy/sell commands that you executed, instead of re-stating your portfolio's composition on a daily basis. This then shifts from being intermediate spreadsheet development to an advanced job requiring knowledge of Visual Basic (VBA).

VBA is Excel's programming language and enables you to automate any analysis you wish to perform on your data. It can be used to automate the retrieval of data from Yahoo Finance. This would remove the requirement for you to download that data yourself. If you are unsure how to create such sheets yourself, you can always seek out an Excel expert on the web who will be happy to help you solve your problem.

If you do contract an external consultant, it would also be worth asking them what summary statistics they can provide for your data. You may be interested to know what your total exposure was last April, or what gross return was last June. Even if you do not require this information in the first instance, it's worth ensuring these things can be added if required. If they can't, it suggests your Excel developer is planning to build something unnecessarily complicated which, inevitably, will make it more expensive to maintain and develop.

Finally, you may be interested in scenario testing e.g. what if I'd held onto my tech shares last year? I would recommend not using too many hard numbers in your sheet's formulas. Instead try to make every number come from another cell. It's very easy to do when you first create a sheet and makes it much easier to vary parameters reliably at a later date.

POST COMMENT

Microsoft Office 2010 – Maximize Your Efficiency! IE9 is Much Better - The Microsoft Explanation and The Facts Macintosh cum Microsoft SGI Altix UV 1000 fully configured and freshly certified by Microsoft Microsoft Remote Keyboard - Get the finest Out of It Free Microsoft Points - A Solution To Expensive Gaming How to remove Microsoft Office 2007 documents password? Hire Microsoft Office Excel Programmer Microsoft Remote Keyboard Report - Unlikely Look On Microsoft Office 2010 - Is it Worth It? Steps To Remove Microsoft Debug System - How To Remove Microsoft Debug System The Easy Way Microsoft weighs in on SaaS CRM Microsoft Debug System Removal - How To Remove Microsoft Debug System Easily From Your PC