An Excel App to Facilitate Manual Strategy Testing on Tradingview

Discussion in 'Technical Analysis' started by Good1, Oct 1, 2022.

  1. Good1

    Good1

    How many folks here would be interested in the easiest way to get information from chart drawings on Tradingview into an Excel sheet for analysis?

    Are you drawing lines on Tradingview to get an idea whether your method is working or not?

    Are you in a pre-automation phase in which the method you are testing is not described by any existing automated strategy script, and so, you are manually marking up charts using the replay feature?

    The problem is Tradingview has no script which can read the marks you've manually marked on your charts, and transfer them to any kind of analysis software.

    The other problem is getting enough data to warrant the extra effort to bot whatever method you are testing, or just to go live, without a bot.

    I went ahead and solved this problem for myself, and developed an Excel spreadsheet that would facilitate data entry. I also developed methods of marking up charts on Tradingview, and preparing them for data entry.

    One of the features i wanted was to analyze the same method on different time frames, side by side. This is to be able to use higher time frames to use as filters for the lower time frames. The logic is that if a method works on higher time frames, it's guidance could greatly enhance the profit factor of trades taken on the lower time frames that are going in the same direction.

    Now i'm working on an engine that will read the data in the Excel sheet and spit out statistics and charts.

    If anyone is interested in employing my sheet for data entry, i'd be happy to run the data through my analysis engine and post the results in this thread.

    If there is enough interest i'll upload the Excel sheet that facilitates data entry, and include detailed instructions.

    I need some feedback about where, which forum this thread belongs.
    ?
    Technical Analysis
    Journal
    Strategy Building
    Risk Management
    Trading Software
    Data Sets and Feeds
    App Development
    ?
     
  2. How do you plan on accomplishing that ? Does trading view support DLL or COM interfaces ?
     
  3. Good1

    Good1

    The data collection sheet is in VBA and takes advantage of the double-click event. Depending on where you double click, it understands what you are trying to do, and reduces the need for a whole lot more clicks. Should only need to touch the keyboard when entering price. It helps with that too, for example if the price is five digits, it will suggest the first two, so most price entry is not more than three digits. Those three can be entered by voice too.

    The analysis engine also uses VBA to read the data thats been collected and output stats to other sheets, the immediate window and to charts. I'm not feeding the data back into Tradingview for it's analysis engine. Don't think that is possible anyway. The engine outputs some basic and important stats right now, lists trades in and out showing percentage gain/loss, and will continue to add details so that money management schemes may be tested on the faux trades. It recognizes a master stream of data, and sub streams on lower time frames which will trade only in harmony with the master. This never fails to improve profit factor.
     
  4. Good1

    Good1

    My solution is for speeding data collection similar to using the replay function in Tradingview. I don't use replay anymore, just marking the charts according to my rules, confident i am not cheating. I believe a demo account is a walk-forward test in real time, which has it's place. This is for first establishing if walking forward is worth it, and how to improve the walk forward.
     
  5. Still....a lot of manual effort...which is error-prone.,
    Many platforms like Ninja Trader and Multicharts have an analysis module which outputs a vast array of trade statistics...no need to calculate them.
     
  6. Good1

    Good1

    The presumption is that manual marking of charts, for testing, is needed whenever ones method is not described by an existing robot, and/or the ability to transform into a robot is not forthcoming anytime soon.

    Tradingview offers excellent options for marking up charts. But without any scripts to read manually marked charts, we have to get creative about how to collect data off those charts for more serious analysis.

    I've satisfied my own desire to reduce errors to a minimum while easing the speed of collection to maximum through crafty use of VBA.

    In development, everytime I made an error, I improved the VBA to reduce it. At this point I don't know how it could be better.


    If those platforms could consume Excel sheets for that purpose ild be interested to know. As is, my homespun engine is well on the way to printing every important statistic needed to verify my ideas. The basics have been verified to work and am just fine tuning now with which time frame, or market to use, and how applying more or less exposure per trade can be derived from the stats.

    Let's say I want to get involved with a capital provider and needed to limit my drawdown to 5%. Then I would want to really drill down on using the stats for money management.

    Tradingview will consume an Excel sheet, but must be in process of running an indicator or strategy. Maybe I'll look closer at that, but again, the basic engine is done and it's easier just to work with VBA at this moment.
     
    Last edited: Oct 3, 2022
  7. MKV666

    MKV666

    Hi @Good1

    I'm interested in how you managed to succeed in this topic. Tradingview is my platform of choice, however, I hate that there isn't a native way to export that type of information.

    You can only export strategy results, but as you can not enter trades manually in the "strategies", section, to this day drawings are the easiest way for simple backtest practices. Especially on high time frame.

    [​IMG]
     
  8. MKV666

    MKV666

    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.


    upload_2023-3-7_19-5-12.png

    Would you please share how did you achieve this by using VBA's?
     
    Good1 likes this.
  9. Good1

    Good1

    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.
     
    Last edited: Mar 7, 2023
    Tradess0610 likes this.
  10. MKV666

    MKV666

    This is all very detailed and helpful. Thank you for taking the time to reply in such a fast manner. Would you mind providing some screenshots or even better a screen record so I can visualize everything you've done?

    Also, how exactly do you copy the information from tradingview? I don't understand how is it possible to write the first two dates manually and Excel to do the rest of the work. Do you still do it manually or use some sort of magic?

    Thank you!
     
    #10     Mar 8, 2023