Retail Investors And Microsoft Excel

By Author: Ed Bolton
Total Articles: 17
Comment this article

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.



About the Author: Ed Bolton is the founder of Excel4Business, and an Excel expert.