Export-backtest-equity-curve-data-and-or-BarHistory-for-DataSet-as-CSV

Yes right click on it and copy equity curve data to clipboard, paste into spreadsheet.

Many thanks both

I want to export data for all symbols in dataset to csv-files. I don't see any button to do so (finantic.Export installed), there are no hints.
I would like even more to convert qx files with an external program. How are these files encoded?

Re: no hints - how about these screenshots?
https://www.wealth-lab.com/extension/detail/finantic.Export#screenshots

We don't disclose the QX format, it's our internal proprietary format. If we need to change anything we don't want to have to worry about users relying on the format for external programs.

What exactly do you want to export? What symbol data? The historical price data? When I need to do this I write a C# Strategy that writes the data to a file, and then run it on the DataSet.

Thanks for the quick reply. Yes, I would like to export the historical data.
@Eugene
Under “Backtest Results” I find “Export” but no entry for “BarHistory (date, open, high, low, close, volume)”. This must be done individually for each symbol?
@Glitch
What I actually want: Start WealthLab automatically (when absent) and save (possibly send) the result of a backtest.
Nevertheless, I would like to know how to read the BarHistory and write it to a file. (I have written routines for my server and don't want to download the stock data twice).

@eike: If you are using C# strategies then you can use the following. Call the method from the BacktesetComplete method of your strategy. See the method's comments.

You could add this your library if you have one.

CODE:
    /// <summary>
    ///     For the given bar histories, output the bar data to a CSV file, one file per symbol.
    ///     The output folder is the current user's Downloads folder. For each file's name
    ///     see the code.
    ///     Call this method from method BacktestComplete like this:
    ///         OutputBarHistory(Backtester.Symbols, ",", true);
    /// </summary>
    /// <param name="histories">The bar histories for which the file output should be generated</param>
    /// <param name="separator">
    ///     In the output file(s), each component (i.e. date, open, etc.) is separated by this string. Defaults to a single
    ///     comma.
    /// </param>
    /// <param name="includeHeader">
    ///     Set to true to include a heading line indicating each bar history component's name (i.e.
    ///     Open, Close, etc.). Defaults to true.
    /// </param>
    public static void OutputBarHistory(List<BarHistory> histories, string separator = ",", bool includeHeader = true)
    {
        // output is to the current user's Downloads folder
        var outputFolder = $@"C:\Users\{Environment.UserName}\Downloads\";

        // go through each bar history and output it to a file
        foreach (var barHistory in histories)
        {
            var startDate = barHistory.DateTimes[0];
            var endDate = barHistory.DateTimes[barHistory.Count - 1];
            // note: file is always named with csv extension, regardless of separator
            var fileName =
                $"{outputFolder}{barHistory.Symbol}_{barHistory.Scale.ToString().Replace(" ", "_")}_{startDate:yyyyMMddhhmm}_{endDate:yyyyMMddhhmm}.csv";
            var outputFile = File.CreateText(fileName);
            if (includeHeader)
            {
                outputFile.WriteLine(
                    $"Date/Time{separator}Open{separator}High{separator}Low{separator}Close{separator}Volume");
            }

            // speed things up a tad
            var dates = barHistory.DateTimes;
            var opens = barHistory.Open;
            var highs = barHistory.High;
            var lows = barHistory.Low;
            var closes = barHistory.Close;
            var volumes = barHistory.Volume;

            // for each bar...
            for (var i = 0; i < barHistory.Count; i++)
            {
                // sortable date output for all dates, scales
                var outputDate = dates[i].ToString("s");
                var open = opens[i];
                var high = highs[i];
                var low = lows[i];
                var close = closes[i];
                var volume = volumes[i];
                var line =
                    $"{outputDate}{separator}{open}{separator}{high}{separator}{low}{separator}{close}{separator}{volume}";
                outputFile.WriteLine(line);
            }

            outputFile.Close();
        }
    }

QUOTE:
I want to export data for all symbols in dataset to csv-files.

Here is the answer for non-coders:

With finantic.Export extension installed, go to Help->Extensions->Export.
The help page "Export" says:

Export of BarHistories
A bar history is a TimeSeries with columns Date, Open, High, Low, Close and Volume. Bar histories are exported by a special strategy named ExportBarHistory in the Export folder. Open this strategy and adapt the base Directory. If the strategy is run in Single Symbol mode just data of the selected symbol is exported. If run as a Portfolio backtest a set of export files is created one for each symbol in the portfolio.
Please take notice of your Data Provider's license agreement.

QUOTE:
What I actually want: Start WealthLab automatically (when absent) and save (possibly send) the result of a backtest.

This should be possible with a clever use of WL's workspaces and comandline options.

@eugene: The second part of this thread (exporting BarHistories) is somewhat off-topic (does not match thread's header) and should probably be moved to a new discussion thread.

Re: Post #11. Given how the offtopic has progressed, it would be tricky to split the thread. Let's rename the topic then.

QUOTE:
Here is the answer for non-coders:

With finantic.Export extension installed, go to Help->Extensions->Export.
The help page "Export" says:

Export of BarHistories
A bar history is a TimeSeries with columns Date, Open, High, Low, Close and Volume. Bar histories are exported by a special strategy named ExportBarHistory in the Export folder. Open this strategy and adapt the base Directory. If the strategy is run in Single Symbol mode just data of the selected symbol is exported. If run as a Portfolio backtest a set of export files is created one for each symbol in the portfolio.
Please take notice of your Data Provider's license agreement.

I am trying this extension out and wanted to use the ExportBarHistoryandTimeSeries method.
The code only exports to Parquet files.
Is it possible to export as CSV?
When I tried it throws an error - "Value can't be NaN".

Can anyone advise on how to export to CSV?

I can't answer for finantic's extensions, but the easiest way to export data from one chart is to right click the chart, Copy Price data, and paste. If you really want the comma separators, then you'd have to paste to Excel and save as CSV.

Thanks Cone
I am trying to export multiple symbols at once.

I am trying to export in one file so I can do some correlation studies.
I think your suggestion will actually work if I include the SymbolInd indicator and pull it into the chart.

I remember it was fairly simple with Wealthscript using AnalyseSeries in a single script outputting many series, but I don't know how to do that in c#

Analysis Series (Preferences > Backtest > Perf Visualizers) is "automatic" in WL8. You just select the indicator to analyze, so that's not going to work for your export purpose.

My question for things like this is always, "if you go through all the trouble to pull the data into a strategy, why wouldn't you just code whatever you need to do for correlations into the strategy?"

Anyway if export you must, maybe finantic has what you need (I don't know, but it doesn't sound like it). We have the Concierge Service for custom programming jobs, because this sounds like a job with specifications.

Thanks

I am simply not proficient enough (yet) in c# to code what i want, but yes, can see that when i am, these things are going to be a lot easier.
Perhaps the concierge service would be a good way to accelerate learing

@jayram... here you go...

To use, create a new C# strategy and save it. In WL, in the C# editor, hit CTRL-A and delete key so as to delete the default code. Copy the code that is below into the C# editor and save.

Go to the strategy settings and select your desired backtest dataset, data scale and data range. There is one strategy parameter named "Generate one file per symbol". Set it to zero to place all output data in one file. Set it to one to generate one file per symbol. See the comments for the OutputBarHistory method about file names that are used.

CODE:
using System;
using System.Collections.Generic;
using System.IO;
using WealthLab.Backtest;
using WealthLab.Core;

namespace WealthLabStrategies.Analysis
{
    public class ExportBarHistories : UserStrategyBase
    {
        public ExportBarHistories()
        {
            ParamOneFilePerSymbol = AddParameter("Generate one file per symbol", ParameterType.Int32, 0, 0, 1);
        }

        private Parameter ParamOneFilePerSymbol { get; }

        public override void Execute(BarHistory bars, int idx)
        {
        }

        public override void BacktestComplete()
        {
            OutputBarHistory(Backtester.Symbols, ",", true, ParamOneFilePerSymbol.AsInt != 0);
        }

        /// <summary>
        ///     For the given bar histories, output the bar data to one or more CSV files.
        ///     The output folder is the current user's Downloads folder.
        ///     If output is to a single file then the file name is of the form ManySymbols_{scale}_{ticks}.csv
        ///     If output is to multiple files then the file name is of the form {symbol}_{scale}_{start_date}_{end_date}.csv
        ///     where:
        ///     {symbol} is the symbol name
        ///     {scale} is the bar scale (e.g. 1 day, 1 hour, etc.)
        ///     {ticks} is the current date and time in ticks
        ///     {start_date} is the start date of the bar history
        ///     {end_date} is the end date of the bar history
        ///     Call this method from method BacktestComplete like this example:
        ///     OutputBarHistory(Backtester.Symbols, ",", true, false);
        /// </summary>
        /// <param name="histories">The bar histories for which the file output should be generated</param>
        /// <param name="separator">
        ///     In the output file(s), each component (i.e. date, open, etc.) is separated by this string. Defaults to a single
        ///     comma.
        /// </param>
        /// <param name="includeHeader">
        ///     Set to true to include a heading line indicating each bar history component's name (i.e.
        ///     Open, Close, etc.). Defaults to true.
        /// </param>
        /// <param name="oneFilePerSymbol">
        ///     Set to true to export to one file per symbol. Use false to export data for all symobls to one file.
        ///     Defaults to false.
        /// </param>
        private static void OutputBarHistory(List<BarHistory> histories, string separator = ",", bool includeHeader = true,
            bool oneFilePerSymbol = false)
        {
            if (histories.Count == 0)
            {
                return;
            }

            var outputFolder = $@"C:\Users\{Environment.UserName}\Downloads\";

            var firstSym = histories[0];

            var fileName =
                $"{outputFolder}ManySymbols_{firstSym.Scale.ToString().Replace(" ", "_")}_{DateTime.Now.Ticks}.csv";

            StreamWriter outputFile = null;
            if (!oneFilePerSymbol)
            {
                outputFile = File.CreateText(fileName);
                WriteHeader();
            }

            foreach (var barHistory in histories)
            {
                var symbol = barHistory.Symbol;
                var startDate = barHistory.DateTimes[0];
                var endDate = barHistory.DateTimes[barHistory.Count - 1];
                if (oneFilePerSymbol)
                {
                    fileName =
                        $"{outputFolder}{barHistory.Symbol}_{barHistory.Scale.ToString().Replace(" ", "_")}_{startDate:yyyyMMddhhmm}_{endDate:yyyyMMddhhmm}.csv";
                    outputFile = File.CreateText(fileName);
                    WriteHeader();
                }

                var dates = barHistory.DateTimes;
                var opens = barHistory.Open;
                var highs = barHistory.High;
                var lows = barHistory.Low;
                var closes = barHistory.Close;
                var volumes = barHistory.Volume;

                for (var i = 0; i < barHistory.Count; i++)
                {
                    var outputDate = $"{dates[i]:s}".Replace("T", " ");
                    var open = opens[i];
                    var high = highs[i];
                    var low = lows[i];
                    var close = closes[i];
                    var volume = volumes[i];
                    outputFile.WriteLine(
                        $"{symbol}{separator}{outputDate}{separator}{open}{separator}{high}{separator}{low}{separator}{close}{separator}{volume}");
                }

                if (oneFilePerSymbol)
                {
                    outputFile.Close();
                }
            }

            if (!oneFilePerSymbol)
            {
                outputFile.Close();
            }

            return;

            void WriteHeader()
            {
                if (includeHeader)
                {
                    outputFile.WriteLine("Symbol,Date,Open,High,Low,Close,Volume");
                }
            }
        }
    }
}

@paul1986

Thank you for this.
Appreciated.

Just to add that I updated this code from @paul1986 to include an indicator if anyone needs it.
The added couple of lines includes the 20 bar LogReturn of the close, but easy to add any indicator or time period and have multiple output in the same run.

Pivoting the output in excel allows the shift of the symbols and values by date to a column. I'll figure out how to export a row by date and symbols/variables as columns to avoid the excel step

CODE:
using System;
using System.Collections.Generic;
using System.IO;
using WealthLab.Backtest;
using WealthLab.Core;
using WealthLab.Indicators;

namespace WealthScript8 
{
   public class ExportBarHistories : UserStrategyBase
   {
      public ExportBarHistories()
      {
         ParamOneFilePerSymbol = AddParameter("Generate one file per symbol", ParameterType.Int32, 0, 0, 1);
      }

      private Parameter ParamOneFilePerSymbol { get; }

      public override void Execute(BarHistory bars, int idx)
      {
      }

      public override void BacktestComplete()
      {
         OutputBarHistory(Backtester.Symbols, ",", true, ParamOneFilePerSymbol.AsInt != 0);
      }

      /// <summary>
      ///     For the given bar histories, output the bar data to one or more CSV files.
      ///     The output folder is the current user's Downloads folder.
      ///     If output is to a single file then the file name is of the form ManySymbols_{scale}_{ticks}.csv
      ///     If output is to multiple files then the file name is of the form {symbol}_{scale}_{start_date}_{end_date}.csv
      ///     where:
      ///     {symbol} is the symbol name
      ///     {scale} is the bar scale (e.g. 1 day, 1 hour, etc.)
      ///     {ticks} is the current date and time in ticks
      ///     {start_date} is the start date of the bar history
      ///     {end_date} is the end date of the bar history
      ///     Call this method from method BacktestComplete like this example:
      ///     OutputBarHistory(Backtester.Symbols, ",", true, false);
      /// </summary>
      /// <param name="histories">The bar histories for which the file output should be generated</param>
      /// <param name="separator">
      ///     In the output file(s), each component (i.e. date, open, etc.) is separated by this string. Defaults to a single
      ///     comma.
      /// </param>
      /// <param name="includeHeader">
      ///     Set to true to include a heading line indicating each bar history component's name (i.e.
      ///     Open, Close, etc.). Defaults to true.
      /// </param>
      /// <param name="oneFilePerSymbol">
      ///     Set to true to export to one file per symbol. Use false to export data for all symobls to one file.
      ///     Defaults to false.
      /// </param>
      private static void OutputBarHistory(List<BarHistory> histories, string separator = ",", bool includeHeader = true,
         bool oneFilePerSymbol = false)
      {
         if (histories.Count == 0)
         {
            return;
         }

         var outputFolder = $@"C:\Users\{Environment.UserName}\Downloads\";

         var firstSym = histories[0];

         var fileName =
            $"{outputFolder}ManySymbols_{firstSym.Scale.ToString().Replace(" ", "_")}_{DateTime.Now.Ticks}.csv";

         StreamWriter outputFile = null;
         if (!oneFilePerSymbol)
         {
            outputFile = File.CreateText(fileName);
            WriteHeader();
         }

         foreach (var barHistory in histories)
         {
            var symbol = barHistory.Symbol;
            var startDate = barHistory.DateTimes[0];
            var endDate = barHistory.DateTimes[barHistory.Count - 1];
            if (oneFilePerSymbol)
            {
               fileName =
                  $"{outputFolder}{barHistory.Symbol}_{barHistory.Scale.ToString().Replace(" ", "_")}_{startDate:yyyyMMddhhmm}_{endDate:yyyyMMddhhmm}.csv";
               outputFile = File.CreateText(fileName);
               WriteHeader();
            }

            var dates = barHistory.DateTimes;
            var closes = barHistory.Close;
            // Create indicator series
            var logret = LogReturn.Series(barHistory.Close, 20);
            
            for (var i = 0; i < barHistory.Count; i++)
            {
               var outputDate = $"{dates[i]:s}".Replace("T", " ");
               var close = closes[i];
               var logret20 = logret[i];
               outputFile.WriteLine(
                  $"{symbol}{separator}{outputDate}{separator}{logret20}");
            }

            if (oneFilePerSymbol)
            {
               outputFile.Close();
            }
         }

         if (!oneFilePerSymbol)
         {
            outputFile.Close();
         }

         return;

         void WriteHeader()
         {
            if (includeHeader)
            {
               outputFile.WriteLine("Symbol,Date,LogRet20");
            }
         }
      }
   }
}

Extending jayram's idea for TimeSeries output, the following code allow you to output any number of TimeSeries (and of course Indicators). See the code comments for details...

CODE:
using System;
using System.Collections.Generic;
using System.IO;
using WealthLab.Backtest;
using WealthLab.Core;
using WealthLab.Indicators;

namespace WealthLabStrategies.Analysis
{
    /// <summary>
    ///     Exports one or more TimeSeries to CSV file(s) for each symbol in the strategy's dataset. Each file will be saved
    ///     in the user's Downloads folder.
    ///     See the BackTestComplete method for how to add one or more TimeSeries to the output.
    ///     See the OutputTimeSeriesCaptures method for details on file naming, etc.
    ///     This strategy is strictly for outputting TimeSeries to CSV files. It does not trade. It does not display any
    ///     output. It is NOT used for charting.
    ///     To output to a single file set the Generate one file per symbol parameter to 0. To output to one file per symbol
    ///     set the Generate one file per symbol parameter to 1.
    ///     Note: this code is written for C# version 12 and later.
    /// </summary>
    public class ExportManyTimeSeries : UserStrategyBase
    {
        public ExportManyTimeSeries()
        {
            ParamOneFilePerSymbol = AddParameter("Generate one file per symbol", ParameterType.Int32, 0, 0, 1);
        }

        private Parameter ParamOneFilePerSymbol { get; }

        public override void Execute(BarHistory bars, int idx)
        {
        }

        /// <summary>
        ///     Here, in BacktestComplete, add TimeSeries that you want output. See the foreach loop.
        /// </summary>
        public override void BacktestComplete()
        {
            if (Backtester.Symbols.Count == 0)
            {
                return;
            }

            var captures = new List<TimeSeriesCaptures>(Backtester.Symbols.Count);

            foreach (var bars in Backtester.Symbols)
            {
                // add Indicators and TimeSeries here for eventual output.
                // For the unfamiliar, Indicators are TimeSeries.
                var ts = new List<TimeSeries>
                {
                    bars.Open,
                    bars.High,
                    bars.Low,
                    bars.Close,
                    bars.Volume,
                    RSI.Series(bars.Close, 4),
                    LogReturn.Series(bars.Close, 4)
                };

                captures.Add(new TimeSeriesCaptures(bars, ts));
            }

            // output the TimeSeries
            OutputTimeSeriesCaptures(captures, ",", ParamOneFilePerSymbol.AsInt != 0);
        }

        /// <summary>
        ///     Exports one or more TimeSeries to CSV file(s). The file(s) will be saved in the user's Downloads folder. When
        ///     output is to a single file, the filename will be in the format "ManySymbols_{scale}_{ticks}.csv" where scale is
        ///     the scale of the first symbol in the list and ticks is the current date and time in ticks. If oneFilePerSymbol is
        ///     true, the file name will be in the format "{symbol}_{scale}_{startDate}_{endDate}.csv" where symbol is the symbol
        ///     name, scale is the scale of the symbol, startDate is the start date of the bar history, and endDate is the end date
        ///     of the bar history. Scales are supported down to 1 minute.
        ///     Each TimeSeries that is to be output must be synchronized with the bar history. Thus, each TimeSeries must have the
        ///     same number of items as the bar history. Each TimeSeries must be in the same date order as the bar history. The
        ///     TimeSeries must be in the same scale as the bar history.
        ///     The file will contain the following columns:
        ///     Symbol, Date, ts1, ts2, ts3, etc. where ts1, ts2, ts3, etc. are the time series in the TimeSeriesSet.
        ///     Each file will contain a header row with the column names.
        ///     Each row will contain the symbol, the date, and the values of the time series in the TimeSeriesSet.
        ///     double.NaN values will be output as empty strings.
        /// </summary>
        /// <param name="timeSeriesCaptures">The set of TimeSeries to output</param>
        /// <param name="separator">
        ///     In the output file(s), each component (i.e. date, open, etc.) is separated by this string. Defaults to a single
        ///     comma. Do not use semicolon as it is used to replace commas in the header row for the TimeSeries representations.
        /// </param>
        /// <param name="oneFilePerSymbol">
        ///     Set to true to export to one file per symbol. Use false to export data for all symbols to one file.
        ///     Defaults to false.
        /// </param>
        private static void OutputTimeSeriesCaptures(List<TimeSeriesCaptures> timeSeriesCaptures, string separator = ",",
            bool oneFilePerSymbol = false)
        {
            if (timeSeriesCaptures == null || timeSeriesCaptures.Count == 0)
            {
                return;
            }

            var outputFolder = $@"C:\Users\{Environment.UserName}\Downloads\";
            var firstSym = timeSeriesCaptures[0].Bars;

            var fileName =
                $"{outputFolder}ManySymbols_{firstSym.Scale.ToString().Replace(" ", "_")}_{DateTime.Now.Ticks}.csv";

            StreamWriter outputFile = null;
            if (!oneFilePerSymbol)
            {
                outputFile = File.CreateText(fileName);
                WriteHeader();
            }

            foreach (var timeSeriesCapture in timeSeriesCaptures)
            {
                var barHistory = timeSeriesCapture.Bars;
                var symbol = barHistory.Symbol;
                var startDate = barHistory.DateTimes[0];
                var endDate = barHistory.DateTimes[^1];
                if (oneFilePerSymbol)
                {
                    fileName =
                        $"{outputFolder}{barHistory.Symbol}_{barHistory.Scale.ToString().Replace(" ", "_")}_{startDate:yyyyMMddhhmm}_{endDate:yyyyMMddhhmm}.csv";
                    outputFile = File.CreateText(fileName);
                    WriteHeader();
                }

                for (var rowIndex = 0; rowIndex < barHistory.Count; rowIndex++)
                {
                    outputFile.Write(symbol);
                    outputFile.Write(separator);
                    outputFile.Write(barHistory.DateTimes[rowIndex].ToString("s").Replace("T", " "));
                    outputFile.Write(separator);

                    var count = timeSeriesCapture.TimeSeriesSet.Count;
                    for (var columnIndex = 0; columnIndex < count; columnIndex++)
                    {
                        var value = timeSeriesCapture.TimeSeriesSet[columnIndex][rowIndex];
                        if (!double.IsNaN(value))
                        {
                            outputFile.Write(value);
                        }

                        if (columnIndex < count - 1)
                        {
                            outputFile.Write(separator);
                        }
                        else
                        {
                            outputFile.WriteLine("");
                        }
                    }
                }

                if (oneFilePerSymbol)
                {
                    outputFile.Close();
                }
            }

            if (!oneFilePerSymbol)
            {
                outputFile.Close();
            }

            return;

            void WriteHeader()
            {
                outputFile.Write($"Symbol{separator}Date{separator}");
                var tsc = timeSeriesCaptures[0].TimeSeriesSet;
                var count = tsc.Count;
                for (var i = 0; i < count; i++)
                {
                    var tsHeading = tsc[i].ToString();
                    tsHeading = tsHeading[..tsHeading.LastIndexOf('(')].Replace(",", ";");
                    outputFile.Write(tsHeading);
                    if (i < count - 1)
                    {
                        outputFile.Write(separator);
                    }
                    else
                    {
                        outputFile.WriteLine();
                    }
                }
            }
        }

        private class TimeSeriesCaptures(BarHistory bars, List<TimeSeries> timeSeriesSet)
        {
            /// <summary>
            ///     We need the BarHistory because we need at least the symbol. A TimeSeries does not contain the symbol.
            /// </summary>
            public BarHistory Bars { get; } = bars;

            public List<TimeSeries> TimeSeriesSet { get; } = timeSeriesSet;
        }
    }
}

@paul1986, this is great. Much cleaner way to add indicators!

@paul1986 - is it possible to add the symbol name each timeseries in the header?
i.e. LogReturn(close;20)_symbolname

Would be useful for when merging individual file outputs to get each symbols' time series values as a column organised by date (rows)

@jayram - that's not a good idea for a number of reasons.

The output of the strategy is a file, organized as a table, where the primary key is the combination of the Symbol and Date columns (the scale is implied for the entire file). That's just basic data modeling. The data is organized by that key so that it can be queried and manipulated with SQL, R, Excel, etc.

Making a new column name the combination of the derived time series column name plus part of an existing column's value is not how you want to achieve a merge. That just destroys the original purpose of the output and makes a mess of columns when you have many symbols in one original file (which is possible with the code). Merges are achieved in other much better ways.

If you're trying to combine the files then simply append them together, leaving only one header. This of course assumes that each file has the same time series sets in them in the same order.

If that's a hassle to get rid of the extra column header rows then another approach is to create a WL DataSet with the symbols you need. Then run the strategy and output the data to one file.

Then you have a file organized by a Symbol + Date key for subsequent manipulation by downstream processing.

I don't think I explained what I was trying to do very well.
Not trying to add symbol to value, just the column label
The purpose is an analysis set with time series as columns for automated correlation analysis over thousands of time series - easy to get what I want in excel (or Orange Data Platform) from the CSV the code outputs, so it's not a major issue. Was just trying to avoid a post processing step