Category Other Published on 21/08/2021

VFX MultiSymbol Spread Analyzer v1.5

Description

VFX MultiSymbol Spread Analyzer allows you to analyze, evaluate, and compare spreads and trading costs of multiple symbols from the historical Tick data of various brokers.

You can set up the following parameters:

Watchlist [1-3]
Name of the Watchlist with symbols charged commission fee of the selected type
Commission [1-3]
Commission amount charged by the broker. It is converted by the analyzer to the equivalent pips.
Together with the average symbol spread this represents the total cost of trade of the symbol:
- total cost = average spread (avg) + commission pips (comm)
Type [1-3]
Commission type charged by the broker (for one side of the trade; i.e. round turn / 2):
- USD_mil - in USD per 1 million
- USD_lot - in USD per 1 lot
- Pct_vol - in % of the volume traded
UTC Hours Shift
Time zone correction
Start / End Hour
Time interval of each day during which the robot will analyze the spread (useful for analyzing selected markets hours only)
Export to File
You can choose to export the aggregated results of one broker's account to a .csv file (saved on the Desktop). Multiple files generated from different accounts (brokers) can be merged e.g. in MS Excel or Google Sheets for further cross-broker analysis (see the below example)
By Hours
Data export can be aggregated by hours instead of the standard daily aggregation
Normalize Symbol Names
When selected the analyzer attempts to convert various symbol names (e.g. EURUSD and EUR/USD) used by brokers to a unified format so that data exported from different brokers can be aggregated and analyzed together (e.g. in a pivot table)
See the NormalizeSymbolName() function for the mapping rules. In case of need you can add your broker's symbol names

Make sure to use the Tick data from Server (accurate) for Backtesting (you can ignore the Starting Capital and Commission fields):

Choose the time period over which you want to analyze the spreads (no more than 1 year is suggested if you use the Create File together with By Hours option)

IMPORTANT NOTE: Should you experience errors during the backtest run, ensure the start date is a working day

The aggregated results are written to the Log:

min - minimum spread identified
max - maximum spread identified
avg - calculated average spread
comm - commission fee converted to the equivalent pips
total cost - total cost in pips
normalized - total cost in "normalized pips" where pip size is determined by the analyzer for each symbol. This ensures the normalized pips to have the same size for selected symbol across all tested brokers, thus allowing for an easier total costs comparison
ATR - total cost as the % of the average daily ATR of the selected symbol; the best way of comparing cost of trading of symbols of various asset groups (eg. FX vs metals vs indices vs crypto)
ticks - total number of ticks detected and used for the analysis

Example of a further analysis of the extracted data in MS Excel PivotTable:

Version history:
1.0    - initial release
1.1    - commission-free watchlist added
1.2    - normalized total pips added to the log output and the exported file
1.3    - fixed usage of normalized symbol names in the backtesting log
1.4    - minor enhancements
1.5    - commission types per lot and % of volume added
- inputs reorganized to 3 watchlists, type of each and commission can be selected
- Start and End hour inputs added (allows measuring only a selected intraday interval)
- total ticks measured and total cost as % ATR columns added to the log output and the exported file
- list of symbol names for normalization extended to cover more brokers

Source Code


//  MultiSymbol Spread Analyzer v1.5 by VFX
//  https://ctrader.com/algos/cbots/show/2625

//  Version history:
//  v1.0    - initial release
//  v1.1    - commission-free watchlist added
//  v1.2    - normalized total pips added to the log output and the exported file
//  v1.3    - fixed usage of normalized symbol names in the backtesting log
//  v1.4    - minor enhancements
//  v1.5    - commission types per lot and % of volume added
//          - inputs reorganized to 3 watchlists, type of each and commission can be selected
//          - Start and End hour inputs added (allows measuring only a selected intraday interval) 
//          - total ticks measured and total cost as % ATR columns added to the log output and the exported file
//          - list of symbol names for normalization extended to cover more brokers

using System;
using cAlgo.API;
using cAlgo.API.Internals;
using cAlgo.API.Indicators;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.IO;

namespace cAlgo
{
    [Robot(TimeZone = TimeZones.UTC, AccessRights = AccessRights.FullAccess)]
    public class _VFX_MultiSymbol_Spread_Analyzer : Robot
    {
        [Parameter("Watchlist 1", DefaultValue = "SpreadTest1")]
        public string parWatchlistName1 { get; set; }
        [Parameter("Type 1", DefaultValue = CommissionType.USD_mil)]
        public CommissionType parType1 { get; set; }
        [Parameter("Commission 1", DefaultValue = 25, MinValue = 0)]
        public double parCommission1 { get; set; }

        [Parameter("Watchlist 2", DefaultValue = "SpreadTest2")]
        public string parWatchlistName2 { get; set; }
        [Parameter("Type 2", DefaultValue = CommissionType.USD_lot)]
        public CommissionType parType2 { get; set; }
        [Parameter("Commission 2", DefaultValue = 2.5, MinValue = 0)]
        public double parCommission2 { get; set; }

        [Parameter("Watchlist 3", DefaultValue = "SpreadTest3")]
        public string parWatchlistName3 { get; set; }
        [Parameter("Type 3", DefaultValue = CommissionType.Pct_vol)]
        public CommissionType parType3 { get; set; }
        [Parameter("Commission 3", DefaultValue = 0.08, MinValue = 0)]
        public double parCommission3 { get; set; }

        [Parameter("UTC Hours Shift", DefaultValue = 2, MinValue = -12, MaxValue = 12)]
        public int parHoursShift { get; set; }

        [Parameter("Start Hour", DefaultValue = 0, MinValue = 0, MaxValue = 24, Step = 0.5)]
        public double parMinHour { get; set; }
        [Parameter("End Hour", DefaultValue = 24, MinValue = 0, MaxValue = 24, Step = 0.5)]
        public double parMaxHour { get; set; }

        [Parameter("Export to File", DefaultValue = false)]
        public bool parCreateFile { get; set; }

        [Parameter("By Hours", DefaultValue = false)]
        public bool parByHours { get; set; }

        [Parameter("Normalize Symbol Names", DefaultValue = false)]
        public bool parNormalize { get; set; }

        private Functions Fn = new Functions();

        private int hrShift;
        private const string csvSeparator = ",";

        private bool failure = false;
        private string FileName;
        private StringBuilder csv;
        private long csvLines;

        public enum CommissionType
        {
            USD_mil,
            USD_lot,
            Pct_vol
        }

        public class HourItem
        {
            public double minSpread;
            public double maxSpread;
            public double sumPrice;
            public double sumSpread;
            public double sumATR;
            public long count;

            public HourItem()
            {
                this.minSpread = double.PositiveInfinity;
                this.maxSpread = double.NegativeInfinity;
                this.sumPrice = 0;
                this.sumSpread = 0;
                this.sumATR = 0;
                this.count = 0;
            }
        }

        public List<Bot> bots = new List<Bot>();
        public class Bot
        {
            public Symbol symbol;
            public double sumSpread, minSpread, maxSpread, avgSpread, comSpread, sumPrice, avgPrice, sumAccCurrRate, normCoef;
            public long count;
            public int precision;
            public HourItem[] hourData;
            public DateTime prevTickDate;
            public bool isFirstTick;
            public CommissionType commissionType;
            public double commission;
            public Ticks ticks;


            public Bot(Robot robot, Symbol symbol, CommissionType commtype, double comm)
            {
                this.symbol = symbol;
                this.sumSpread = 0;
                this.minSpread = double.PositiveInfinity;
                this.maxSpread = double.NegativeInfinity;
                this.count = 0;
                this.precision = 2;
                this.sumPrice = 0;
                this.sumAccCurrRate = 0;
                this.prevTickDate = DateTime.MinValue;
                this.isFirstTick = true;
                this.commissionType = commtype;
                this.commission = comm;
                this.ticks = robot.MarketData.GetTicks(symbol.Name);
                this.normCoef = Math.Pow(10, Math.Round(Math.Log10(symbol.Bid / symbol.PipSize) - 4, 0));
                this.hourData = new HourItem[24];
                for (int h = 0; h < 24; h++)
                    this.hourData[h] = new HourItem();
            }
        }

        protected override void OnStart()
        {
            hrShift = parHoursShift;
            Symbol[] MySymbols1 = null, MySymbols2 = null, MySymbols3 = null;
            bool WL1found = true, WL2found = true, WL3found = true;

            try
            {
                MySymbols1 = Symbols.GetSymbols(Watchlists.FirstOrDefault(wl => wl.Name == parWatchlistName1).SymbolNames.ToArray());
            } catch (Exception)
            {
                WL1found = false;
            }
            try
            {
                MySymbols2 = Symbols.GetSymbols(Watchlists.FirstOrDefault(wl => wl.Name == parWatchlistName2).SymbolNames.ToArray());
            } catch (Exception)
            {
                WL2found = false;
            }
            try
            {
                MySymbols3 = Symbols.GetSymbols(Watchlists.FirstOrDefault(wl => wl.Name == parWatchlistName3).SymbolNames.ToArray());
            } catch (Exception)
            {
                WL3found = false;
            }

            if (!WL1found && !WL1found && !WL3found)
            {
                failure = true;
                Print("Watchlist not found");
                Stop();
                return;
            }

            try
            {
                if (WL1found)
                    foreach (Symbol s in MySymbols1)
                        bots.Add(new Bot(this, s, parType1, parCommission1));
                if (WL2found)
                    foreach (Symbol s in MySymbols2)
                        bots.Add(new Bot(this, s, parType2, parCommission2));
                if (WL3found)
                    foreach (Symbol s in MySymbols3)
                        bots.Add(new Bot(this, s, parType3, parCommission3));
            } catch (Exception)
            {
                failure = true;
                Print("Failed to get the Tick data");
                Print("Make sure to use the \"Tick data from Server (accurate)\" Backtesting options and a working day for the start date");
//                or delete the \"BacktestingCache\" folder at \"{0}\"", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\" + Account.BrokerName + " cTrader");
                Stop();
                return;
            }

            foreach (Bot bot in bots)
                bot.ticks.Tick += OnBotticksTick;

            // Set up the string writer for CSV:
            if (parCreateFile)
            {
                FileName = Account.BrokerName.Replace(' ', '_') + "_Spread_Analyzer_Output";
                csv = new StringBuilder();
                var headerLine = string.Format("Broker{0}Symbol{0}Date{0}Week day{0}Hour{0}Ticks{0}Min spread{0}Max spread{0}Sum spread{0}Sum cost spread{0}Sum total cost{0}Sum normalized cost{0}ATR relative cost", csvSeparator);
                csv.AppendLine(headerLine);
                csvLines = 1;
            }
        }

        void OnBotticksTick(TicksTickEventArgs obj)
        {
            double actSpread;
            foreach (Bot bot in bots)
                if (obj.Ticks.SymbolName == bot.symbol.Name)
                {
                    double hourtime = (obj.Ticks.LastTick.Time.AddHours(hrShift).Hour + obj.Ticks.LastTick.Time.Minute / 60.0);
                    if (hourtime < parMinHour || hourtime > parMaxHour)
                        break;

                    bot.count++;
                    bot.sumAccCurrRate += bot.symbol.PipValue / bot.symbol.PipSize;
                    actSpread = (obj.Ticks.LastTick.Ask - obj.Ticks.LastTick.Bid);
                    bot.sumSpread += actSpread;
                    bot.sumPrice += (obj.Ticks.LastTick.Ask + obj.Ticks.LastTick.Bid) / 2.0;
                    if (actSpread < bot.minSpread)
                        bot.minSpread = actSpread;
                    if (actSpread > bot.maxSpread)
                        bot.maxSpread = actSpread;

                    if (parCreateFile && obj.Ticks.LastTick.Time.Date > bot.prevTickDate.Date && !bot.isFirstTick)
                        WriteHourData(bot);

                    int hour = (parByHours ? obj.Ticks.LastTick.Time.AddHours(hrShift).Hour : 0);
                    bot.hourData[hour].minSpread = (actSpread / bot.symbol.PipSize < bot.hourData[hour].minSpread ? actSpread / bot.symbol.PipSize : bot.hourData[hour].minSpread);
                    bot.hourData[hour].maxSpread = (actSpread / bot.symbol.PipSize > bot.hourData[hour].maxSpread ? actSpread / bot.symbol.PipSize : bot.hourData[hour].maxSpread);
                    bot.hourData[hour].sumPrice += (bot.symbol.Ask + bot.symbol.Bid) / 2.0;
                    bot.hourData[hour].sumSpread += (actSpread < 0 ? 0 : actSpread / bot.symbol.PipSize);
                    bot.hourData[hour].count++;

                    bot.prevTickDate = obj.Ticks.LastTick.Time.Date;
                    bot.isFirstTick = false;
                }
        }

        private void WriteHourData(Bot b)
        {
            string symbolname = parNormalize ? Fn.NormalizeSymbolName(b.symbol.Name) : b.symbol.Name;
            HourItem[] hdata = b.hourData;


            for (int h = 0; h < (parByHours ? 24 : 1); h++)
            {
                double sumCommSpread = 0;
                if (b.commissionType == CommissionType.USD_mil)
                    sumCommSpread = b.commission * 2.0 * hdata[h].sumPrice / 1000000.0 / b.symbol.PipSize;
                else if (b.commissionType == CommissionType.USD_lot)
                    sumCommSpread = b.commission * 2.0 * hdata[h].count / b.symbol.LotSize / b.symbol.PipSize;
                else if (b.commissionType == CommissionType.Pct_vol)
                    sumCommSpread = b.commission * 2.0 / 100.0 * hdata[h].sumPrice / b.symbol.PipSize;

                double sumTotalCost = hdata[h].sumSpread + sumCommSpread;
                double sumNormalizedTotal = sumTotalCost / b.normCoef;
                var newLine = string.Format("{1}{0}{2}{0}{3:yyyy/MM/dd}{0}{3:ddd}{0}{4}{0}{5}{0}{6}{0}{7}{0}{8}{0}{9}{0}{10}{0}{11}{0}{12}", csvSeparator, Account.BrokerName, symbolname, b.prevTickDate.Date, h, hdata[h].count, hdata[h].count > 0 ? Math.Round(hdata[h].minSpread, 4).ToString() : "", hdata[h].count > 0 ? Math.Round(hdata[h].maxSpread, 4).ToString() : "", hdata[h].count > 0 ? Math.Round(hdata[h].sumSpread, 4).ToString() : "",
                hdata[h].count > 0 ? Math.Round(sumCommSpread, 4).ToString() : "", hdata[h].count > 0 ? Math.Round(sumTotalCost, 4).ToString() : "", hdata[h].count > 0 ? Math.Round(sumNormalizedTotal, 4).ToString() : "", hdata[h].count > 0 ? Math.Round(sumTotalCost * b.symbol.PipSize / hdata[h].count / Indicators.AverageTrueRange(MarketData.GetBars(TimeFrame.Daily, b.symbol.Name), 100, MovingAverageType.Exponential).Result.LastValue, 6).ToString() : "");
                csv.AppendLine(newLine);
                csvLines++;
                hdata[h] = new HourItem();
            }

        }

        protected override void OnStop()
        {
            if (failure)
                return;

            long totalTicks = 0;

            foreach (Bot bot in bots)
            {
                bot.avgPrice = bot.sumPrice / bot.count;
                bot.avgSpread = (double)bot.sumSpread / (double)bot.count;
                if (bot.commissionType == CommissionType.USD_mil)
                    bot.comSpread = bot.commission * 2.0 * bot.avgPrice / 1000000.0;
                else if (bot.commissionType == CommissionType.USD_lot)
                    bot.comSpread = bot.commission * 2.0 / bot.symbol.LotSize;
                else if (bot.commissionType == CommissionType.Pct_vol)
                    bot.comSpread = bot.commission * 2.0 / 100.0 * bot.avgPrice;

                totalTicks += bot.count;
                double totalCost = (bot.avgSpread + bot.comSpread) / bot.symbol.PipSize;
                double normCost = totalCost / bot.normCoef;
                double pctATR = 100.0 * totalCost * bot.symbol.PipSize / Indicators.AverageTrueRange(MarketData.GetBars(TimeFrame.Daily, bot.symbol.Name), 100, MovingAverageType.Exponential).Result.LastValue;

                Print("{0} (pip size:{6}): (min: {1} - max: {2}) avg: {3} + comm: {4} = total cost: {5} pips | {7} normalized (x{8}) | {9}% ATR | {10} ticks", parNormalize ? Fn.NormalizeSymbolName(bot.symbol.Name) : bot.symbol.Name, Fn.ToRoundedString(bot.minSpread / bot.symbol.PipSize, bot.precision), Fn.ToRoundedString(bot.maxSpread / bot.symbol.PipSize, bot.precision), Fn.ToRoundedString(bot.avgSpread / bot.symbol.PipSize, bot.precision), Fn.ToRoundedString(bot.comSpread / bot.symbol.PipSize, bot.precision), Fn.ToRoundedString(totalCost, bot.precision), bot.symbol.PipSize, Fn.ToRoundedString(normCost, 2), Fn.ToRoundedString(1.0 / bot.normCoef, 1),
                Fn.ToRoundedString(pctATR, 2), Fn.ToRoundedString(bot.count, 0));
            }

            if (parCreateFile)
            {
                string fullPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\" + FileName + "_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".csv";
                File.WriteAllText(fullPath, csv.ToString());
                Print("{0} lines written into {1}", csvLines, fullPath);
            }
        }

        public class Functions
        {
            public string GetCurrSign(string curr)
            {
                if (curr == "EUR")
                    return "€";
                else if (curr == "USD")
                    return "$";
                else if (curr == "GBP")
                    return "£";
                else if (curr == "JPY")
                    return "¥";
                else if (curr == "BTC")
                    return "₿";
                else if (curr == "BCH")
                    return "Ƀ";
                else if (curr == "XRP")
                    return "✕";
                else if (curr == "ETH")
                    return "Ξ";
                else if (curr == "LTC")
                    return "Ł";
                else if (curr == "DSH")
                    return "Đ";
                else
//            else if (curr == "DSH")
//                return "◊";

                    return curr;
            }


            public string ToRoundedString(double num, int type, bool fix = false)
            {
                if (fix && (Math.Abs(num) < 1.0))
                    return num.ToString("#,##0." + new string('0', type));
                else
                {
                    if (Math.Abs(num) < 1E-05)
                        num = 0;

                    int shift = 0;

                    if ((type != 0) && (num != 0))
                        shift = (int)Math.Floor(Math.Log10(Math.Abs(num))) + ((Math.Abs(num) < 1) ? 1 : 0);

                    if (type - shift > 0)
                        return num.ToString("#,##0." + new string('0', type - shift));
                    else
                        return num.ToString("#,##0");
                }
            }

            public string NormalizeSymbolName(string code)
            {
                if ("|XNG|XNG/USD|NAT.GAS|NATGAS.f|NatGas||".Contains("|" + code + "|"))
                    return "XNGUSD";
                else if ("|CL|WTI|XTI/USD|USOIL.cash|SpotCrude||".Contains("|" + code + "|"))
                    return "XTIUSD";
                else if ("|BRENT|XBR/USD|UKOIL.cash|SpotBrent||".Contains("|" + code + "|"))
                    return "XBRUSD";
                else if ("|GERMANY 30|GER30|DAX|Germany 30 (Mini)|GER30.cash||".Contains("|" + code + "|"))
                    return "DE30";
                else if ("|GERTEC30||".Contains("|" + code + "|"))
                    return "TecDE30";
                else if ("|USTECH100|US TECH 100|NSDQ|US Tech 100 (Mini)|US100.cash|NAS100||".Contains("|" + code + "|"))
                    return "USTEC";
                else if ("|US 30|DOW|US30.cash|Wall Street 30 (Mini)||".Contains("|" + code + "|"))
                    return "US30";
                else if ("|US 500|SP|US SPX 500 (Mini)|US500.cash||".Contains("|" + code + "|"))
                    return "US500";
                else if ("|UK 100|FTSE|UK100.cash||".Contains("|" + code + "|"))
                    return "UK100";
                else if ("|EUSTX50|Europe 50|EUROPE 50||".Contains("|" + code + "|"))
                    return "STOXX50";
                else if ("|FRA40|France 40|FRANCE 40||".Contains("|" + code + "|"))
                    return "F40";
                else if ("|SPA35|Spain 35|SPAIN 35||".Contains("|" + code + "|"))
                    return "ES35";
                else if ("|JAPAN 225|NIKKEI|JP255.cash|JPN225|Japan 225||".Contains("|" + code + "|"))
                    return "JP225";
                else if ("|CN50||".Contains("|" + code + "|"))
                    return "CHINA50";
                else if ("|HONG KONG 50||".Contains("|" + code + "|"))
                    return "HK50";
                else if ("|NETHERLANDS 25||".Contains("|" + code + "|"))
                    return "NETH25";
                else if ("|ITALY 40||".Contains("|" + code + "|"))
                    return "IT40";
                else if ("|SWITZERLAND 20||".Contains("|" + code + "|"))
                    return "SWI20";
                else if ("|AUSTRALIA 200|AUS200.cash|Australia 200||".Contains("|" + code + "|"))
                    return "AUS200";
                else if ("|Bitcoin||".Contains("|" + code + "|"))
                    return "BTCUSD";
                else if ("|Ethereum||".Contains("|" + code + "|"))
                    return "ETHUSD";
                else if ("|Litecoin||".Contains("|" + code + "|"))
                    return "LTCUSD";
                else if ("|XBN/USD|BitcoinCash||".Contains("|" + code + "|"))
                    return "BCHUSD";
                else if ("|DASHUSD|Dash||".Contains("|" + code + "|"))
                    return "DSHUSD";
                else if ("|DX.f||".Contains("|" + code + "|"))
                    return "USDX";
                else if ("|ERBN.f||".Contains("|" + code + "|"))
                    return "ERBN";
                else if ("|USTN10.f||".Contains("|" + code + "|"))
                    return "USTN10Y";
                else if ((code.Length == 7) && (code.Substring(3, 1) == "/"))
                    return code.Substring(0, 3) + code.Substring(4, 3);
                else
                    return code;
            }
        }
    }
}

vitofx

Joined on 08.03.2021

Distribution: Free
Language: C#
Trading platform: cTrader Automate
File name: VFX_MultiSymbol_Spread_Analyzer_v1.5.algo
Rating: 5
Installs: 1502
Modified: 13/10/2021 09:54

Download

Warning! Running cBots downloaded from this section may lead to financial losses. Use them at your own risk.

Note that publishing copyrighted material is strictly prohibited. If you believe there is copyrighted material in this section, please use the Copyright Infringement Notification form to submit a claim.

Comments

vitofx · 3 years ago

Hi evgrinaus - the file is saved on your desktop

evgrinaus · 3 years ago

not fining my source file (saved in backtesting)

evgrinaus · 3 years ago

Nice

Copyright © 2024 Spotware Systems Ltd. All rights reserved.
cTrader Ltd offers through its group of companies the cTrader platform. The information on this website is for general informational purposes only and does not constitute financial or investment advice. cTrader does not solicit retail investors. Reliance on this information is at your own risk.