For a lot of the types of analytical explorations I am interested in the charting programs don’t go there. Over the years I have mounted numerous data spelunking expeditions into the depths of market behavior using Excel and VBA. There are several free options available that might also work, but the Excel/VBA integration is most likely the best for this type of work, and it is the one I have experience with.
I signed up for the Microsoft 365 Personal Subscription for $6.99/month. Over the weekend I created an Excel database with 250 days of daily OHLCV data on 50 mostly random NYSE stocks.
The data is on a single sheet. In the past I have tried it with each stock having its own sheet, and reversing the order so the most recent date is at the bottom. This makes it easier to update the database on a daily basis, and the VBA code can be written in such a way that adding new rows, or new stocks, does not break the code. I will be making a database with this structure, and I will provide the macros I write and publish the results of my investigations.
Trade Management Optimization
For now, I just wanted to demonstrate a simple exploration that Excel/VBA makes possible. Trading can be broken down into two functions –
- Trade Selection
- Trade Management
Over the weekend I dusted off my programming skills by creating some macros to compare the results of trade management rules on trade outcome. Before I began the explorations, I “normalized” the price data by adding some columns to the database –
All of these numbers are percentage change.
- C-O: Close to Open the next morning. The “gap”
- C-H: Close to next day High
- C-L: Close to next day Low
- C-C: Close to Close
- O-H: Open to High (I call this the Head)
- O-L: Open to Low (I call this the Tail)
- O-C: Open to Close
Normalization of the price data allows us to compare percent changes in stocks trading at very different price levels. By looking at percent change and percent return we can calculate the value based on any investment level. A couple of reality checks –
- You cannot buy a partial share of stock. Even when you go “all in” with your trading account, you are not really all in. Most people trade in increments of 100 share blocks.
- At lower levels of investment, the trade commission has a higher percentage of cost. Trade 1 share of a $1/share stock that has a 100% gain, it cost you a 500% penalty to make that 100%. As investment level increases, the commission cost dwindles to insignificance.
Trade Management Matrix
One of the exploration I did this weekend was to discover what effect setting different Stop and Target percentages from the Open would have on the cumulative Result. Here is a piece of one of the Result Tables –
The column heading 1.5×2 represents a 1.5% Stop and a 2% Target. The percentages in the table represent the percent Return from a trading simulation that involved 248 trades in 248 days for each stock, represent by each row.
Looking at the first row, trading the stock AA Open to Close EVERY trading day for just under a year had a 26.63% return if you used a 1.5% Stop and a 2% Target, and a 52.1% return if you used a 1.5% Stop and a 4% Target.
Again, these return percentages were based on ZERO trade selection constraints. They were intended to compare management rules independent of trade selection. I will continue to blog my explorations and conclusions. I am just getting this adventure under way, and I am still working out the post structure.
Here is the price data from the first stock in the database –
AA opened on on 3/27/2017, the first day in the database, at $31.74/share. It closed on 3/22/2018, the last day in the database, at $44.92/share. If you bought it on Open 3/27/2017, and sold on Close 3/22/2018, your return would have been 35.22%
I find these differences interesting. Even more interesting was what happened when I put a simple restriction on whether or not to trade that stock that day. The number of trades went down over 50%, while the %Return went up 50%. Since you would still trade every day, choosing a stock that meets your trade selection criteria, there can be a dramatic difference.