I've also developed a spreadsheet that outputs key information and everything is automatically calculated. I'm still figuring out how to add entries automatically as this would drastically decrease my backtesting time.
View attachment 308124
Would you please share how did you achieve this by using VBA's?
Hi there, yes glad to share.
There is:
1. How to mark the charts
2. How to mark the spreadsheet
3. How to evaluate the spreadsheet
4. How to display results
Details:
1. I settled upon the "trend line" marking tool. It leaves your charts most uncluttered come time to gather the necessary info for data entry into the spreadsheet. The "info line", for example, was too cluttered. What either of these don't give is your initial stop loss. If you really need that info there is probably a more uncluttered way to do that. I had not been taking that info. You do however end up with a statistic "average loss" in either points or percent. Ok, so I would use a blue line for longs, and a red line for shorts, which connects from your entry to your exit.
2. Then when you are ready to gather the info, click the eyeball to make the bars disappear. This leaves you only the uncluttered trend lines on your chart. Then use Windows functionality to dock Tradingview on the right side of your screen, and Excel on the left side. Then, going back and forth, use your crosshairs to zero in on your entries and exits to give you the main info you need: date-time at entry, date-time at exit, price at entry, price at exit. I would divide this task by collecting all your date-times first into the spreadsheet, then come back and get all your prices at entry and exit. To facilitate this, first fill a column with all your potential date-times. For example, if you are using hourly bars on Tradingview, fill a column which increments time every hour. I would combine both fate and time in one column. Write the first two date-time, grab those, and pull down. Excel will do the rest of the work. This way you never have to manually enter a date or time for an entry or exit. Instead what you do is scroll to the time of entry and mark it with a "+" for a long trade, or a "-" for a short trade. You know if your trade is long or short by the color of your trend line on your chart. For your exits just put a "c" for closing that position next to the time. After all that, make a second pass by using your crosshairs to give you the actual prices where you got in or out, or reversed. Scroll down Excel and fill in prices next to your entry-exit markers. You can keep Excel in focus by not clicking on Tradingview. Just hover your crosshairs and you will keep Excel in focus do it's easier to keep filling the info you need.
To make this even easier, you would use the double click event to automate a lot of your data entry. Whenever you double click on a cell you can capture an event and create a macro to handle it. These macros can be quite smart and intuitive. The event will tell you what column you clicked on, and what row you clicked on. From that info you can reduce a lot of the data entry work, which also reduces the number of mistakes you might make. So I will be dealing with three columns:
Date-time
Price
Position (as marked by a +, -, or c)
3. Then you will need an evaluation engine to start from the top (earliest time) and loop to the bottom (latest time) and make sense of it. Every time your loop sees a price, it will note the date-time, and whether it is an entry, exit, reverse, or a close. From that you can spit out every kind of statistic you can imagine.
4. I have been using the immediate window to display the information I'm looking for. It's the fastest way to get started, from a programming point of view. That info can be cut and pasted into a text file, or you can also just print directly to a text file or better yet, a .csv file. At any time, when you have time, you can make a prettier presentation by using sheets and ranges and colors...but it takes more time.
Tips on maintaining records:
You only need one column for date-price. You will need two columns per symbol, per trading method. If you are tracking your initial stop loss, you will need a third column per symbol, per trading method. It will be easy to get lost unless the first ten or so rows are reserved for information like:
Symbol
Time frame (granularity)
Exchange or brokerage (where you are sourcing your symbol on Tradingview)
Layout (the name you've given to the layout you are using on Tradingview)
Window (which window you are using on which layout you are using to mark your charts)
Properties of lines (what color and thickness are you using for your longs and short trend line markers)
Time zone
Method (what do you call the algorithm you are applying)
This way you'll be able to go back to Tradingview, find where your marks are, and pick up where you left off. The reason for stating line properties is because you can markup one particular window, in one particular layout, with entries and exits from different time frames...without getting confused. Use different colors and thicknesses for different time frames.
Tips on using double click events for macros:
For example,
If I double click on one column I'll get an "+". If I double click on that I'll get the opposite, "-". If I double click on that I'll reverse it back to a "+". I can change either of these to a "c" by clicking somewhere else. I'm trying to use my mouse for everything possible. Now, if I click on the price column, and it has one of those three symbols in the position column, I'll get an input box. The input box is smart so I only need to put in the full price rarely. It gives me the last full price I entered. Then I can just put in the digits that have changed since last price. It takes care of the rest. But then it will also automatically advance to the next date-time where I have a position marker (+,-,c). From there it gives me another input box telling me what was the last full price I entered (or what it entered for me). Rinse and repeat all the way down. Abort anytime if there appears to be a problem. Use other click locations to navigate through the data, from the beginning...to the end...to the next...to the last...to give you date-time of first and last...etc.
Tips on display:
Charting results is important but programmatically tricky. I succeeded in presenting several symbols on the same chart all automatically, without any manual work to adjust the chart to different begin and endpoints. It's really helpful to be able to compare the performance of one method side by side with another. So I have stats output to immediate window, and/or .csv....and also charts. The chart handles however, or how few symbols/methods I'm looking at, and its also nice to visualize what the underlying symbol did compared to my method. That's all incorporated into the evaluation engine, which was a real headscratcher for a hobbyist programmer, but very doable with the two or three columns of data entry mentioned.
P.S.-- When evaluating data for statistical or charting presentation, evaluate everything down to %percents. You have prices in your data entry, but what we mainly need is the percentage of price that changed the last time I took a position and then exited that position. From that %percent change, plus or minus, you can get every other vital statistic, for presentation, for running your account and running your method. It facilitate side by side comparisons between your different algorithms (methods), time frames, and symbols.
