In general, I am biased towards using Excel for basic data work. While some people frown upon Excel and think that it is for amateurs, there are good reasons for using Excel. One of the strongest selling points for Excel is that your data is stored visibly and any changes you make on the spreadsheet can be viewed real time. This way, it is easier to learn and pick up. The formulas are also stored visibly in the spreadsheet. Statistical packages such as SAS and R do not have as rich a graphical user interface. You cannot view your data and formulas in real time the same way as in Excel. As such they are harder to learn and take precious time away from doing your real work.
Usually, people distribute their work in R or other software as ‘libraries’, complete codes of some algorithm for your usage. I cannot explain the frustration I feel having to read through lines and lines of code just to make one or two customizations. In Excel, work is distributed as an add-in or as a workbook. Well-designed workbooks usually allow me to make changes to the inputs or the formulas on the spreadsheet without going into the code.
As Excel is designed for general use, its interface is meant to be intuitive. Many trading simulation/back-testing software, such as NinjaTrader and MetaTrader, make assumptions about how you will use them and can be restrictive. For example, your data has to be in a certain fixed format before you can import it into NinjaTrader. Even if you get the format right, you cannot programmatically add and import data for multiple instruments at one time without subscribing to a data feed. On the other hand, I do not face such issues in Excel. I just need to click File, followed by Open.
You can up the level of customization and automation by writing your own VBA code. I find VBA language intuitive and easy to learn because of its pervasiveness and tight integration with Office applications. You can easily find community support on the internet and convert your actions into VBA code by ‘recording’ it. Basically, you click on the ‘record’ button in Excel, do whatever you want and click the ‘stop’ button to complete recording a macro. You will find a new VBA macro written up in the Excel Visual Basic Editor, accessible by pressing Alt+F11. A macro is a set of instructions in VBA language. To amend the code, you do an internet search on any part of the VBA language that you do not understand and read the articles listed.
Furthermore, Excel comes with many built-in functions that can be called from your macro or can be coded into the spreadsheet by your macro as a formula. Excel’s calculation engine is highly optimized and its built-functions such as sort and replace are probably much faster than you can build in another language. This makes up for the fact that other languages such as C++ are faster than VBA for the machine to read and compile. Actually a lot of time can be saved by writing algorithms that are efficient. Poorly written program in an extremely fast language in C++ can lose out to a well written VBA macro.
Excel workbooks are also easier to deploy because regular users are probably already using Excel in some way or another. Less commonly used software means that any new co-worker or user must either be already competent in that software or spend additional time learning it. Companies like Bloomberg, Thomson One Banker also have Excel Add-Ins for their users. I am not surprised if some banks build pricing tools in Excel for their traders.
What are the downsides of Excel?
1. It has limited charting functions and its charts are much slower in Excel 2007. Most traders look at charts and most other software come with many nice features to enrich your charts at the click of your mouse. I only plot line graphs, bar charts and two variable scatter plots with less than 2000 data-points in Excel. If I really need the charts, I perform the calculations in Excel and export the data to other software for plotting.
2. It suffers from issues in precision and statistical accuracy. Formulas that are supposed to have a value of 0 sometime show up in scientific format as something like 1.0259xxxE-13. You will lose precision when using very small or large numbers due to how Excel stores and manages numbers. The t statistic function, TDIST, does not accept fractional degrees of freedom. Likewise, the Bessel functions truncate fractional orders. When using new Excel built-in functions, I typically read the help file to understand the calculations involved.
3. It uses only a single core to read and compile your VBA code, even though you may have a quad core CPU. At least this is what I suspect since functions that you are write in VBA, known as user-defined functions are calculated using only a single-core. However, the built-in functions and formulas in cells are calculated using multiple cores. I typically incorporate as many built-in functions in my code as possible.
4. It is not designed for any specific use. Honestly, I prefer specialized packages for back-testing of common technical indicators to avoid the hassle of coding everything again. However, I use Excel when coding is needed for both software packages. For example, certain trading software do not support more sophisticated procedures such as neural network or genetic algorithms unless I fork out more money and time to purchase and learn more software.
5. Most importantly, VBA is a less powerful language than VB.NET, C++, and Python, i.e. VBA has less functionality out of Excel and Office. If you are automating your trading system, you will probably need more of the other languages out there. Excel workbooks and VBA projects are not 100% secure. There are codes available on the internet to crack VBA projects. You typically write DLL files in another language and obfuscate the code to make sure your work cannot be easily compromised.
Ultimately, it is about striking a balance between learning how to use certain software, calculation speed and doing actual analysis work. My intuition is that Excel is not always the best instrument, but it makes for a great tool for fast back-testing work involving less than 100,000 observations not involving multiple users at any one time. For more professional, it might be worthwhile considering other applications.
Some References
http://www.decisionmodels.com/calcsecretsc.htm
This website nicely explains the excel calculation process.
http://blogs.msdn.com/excel/archive/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers.aspx
More information on the precision problem with Excel
Like what you have just read? Digg it or Tip'd it.
The objective of Finance4Traders is to help traders get started by bringing them unbiased research and ideas. Since late 2005, I have been developing trading strategies on a personal basis. Not all of these models are suitable for me, but other investors or traders might find them useful. After all, people have different investment/trading goals and habits. Thus, Finance4Traders becomes a convenient platform to disseminate my work...(Read more about Finance4Traders)
0 comments:
Post a Comment