Every day, high-volume traders and investors in Capital Markets make decisions based on the future outlook of a security. They often automate their signals with an Investment System - sometimes comprised solely of rules that are triggered upon changes in market conditions. More complex systems may be developed through the combination of several different models or strategies into one overall investment system.
Typically such systems may require the user to go into Microsoft Excel and manually update formulas or construct reports that summarize various aspects of the system's projections and results. But what if we could provide all this information without any manual input? What if we could show all calculations, reports, and other behaviour directly within Excel itself? It is possible to develop automated and programmable trading systems using Visual Basic for Applications (VBA) embedded within Microsoft Excel.
For those that currently are not aware of the existence, this article provides an introduction to Visual Basic for Applications (VBA) and the use of VBA programming in Capital Markets applications. For those who are new to VBA, it may provide a useful first step towards developing your own trading systems that can also be used by other people on your team.
Since the vast majority of Capital Markets firms use Microsoft Excel for report writing, visualizing data, running key performance metrics, or constructing financial models relevant to decisions being made across all areas of the firm, developing automated software systems via Visual Basic for Applications has many advantages.
Firstly, Excel is a widely available software application in most Capital Markets organizations. There are few firms that do not use Microsoft Excel on a regular basis, and therefore the availability of Excel to learn VBA programming is very high among users within an organization.
Secondly, there exists a large number of resources online for learning how to program Visual Basic for Applications into any spreadsheet application - including Google Sheets and Open Office Calc - that can easily be adapted by advanced users looking to expand upon this implementation. Therefore, if you learn programming basics from one platform it will be easy to transfer those skills when customizing your own systems using another spreadsheet application or a custom-built Investment System software solution.
Finally, the use of VBA within spreadsheets is relatively versatile and can be used to create automated tasks such as:
• Data collection and formatting;
• Report generation;
• Key performance indicator calculations; and,
• System development for automated trading.
The first step in creating any programmable system is to determine what we want the system itself to do, as well as what strategy or strategies we would like to implement. The level of complexity that you wish your system to have will determine how involved it becomes when writing the Visual Basic for Applications scripts themselves. For those with little programming experience, a user-friendly approach should begin by developing simple programs that are able to read data from files saved on hard drives or shared servers located inside a network, and then write that data into a corresponding Excel file. The user should also be able to easily modify the time period of analysis or selection criteria without having to re-write complicated VBA scripts. This kind of system is easy to design and will suffice for simple data collection and formatting that does not need to be dynamic or interactive.
For example, if you would like your automated spreadsheet system to collect specific data from Metatrader and then write that collected data into a new Metastock file, we will need to create some Metatrader VBA scripts that run directly on the Metatrader platform.
One such use for Metasolutions is to quickly backtest collected trading strategies without having to download and install Metatrader or Metastock locally.
Metatrader allows the user to build trading strategies, test them against historical data, modify certain parameters of the strategy, change quantities being traded, etc. The VBA scripts are run directly inside the software itself - not inside an Excel file as we might expect.
This VBA script is used to collect data from Metatrader and then write that data into a new Metastock file, which can then be opened by Metastock for further analysis.
So, what happens when we wish to do something more advanced - such as run an automated trading strategy?
The easiest way to introduce the concept of automated trading systems is to create a simple strategy that uses moving averages. We can use VBA scripts to write specific data into different spreadsheets depending upon certain conditions occurring throughout the day.
For example, let's say we would like buy or sell arrows to appear in separate Excel worksheets depending upon whether the market closed up or down for that day. The opportunities to benefit from automation via VBA can seem endless.
Copyright © All Rights Reserved
