+1 888 340 6572
MultiCharts Project Management
Go to the previous open issue
Go to the previous issue (open or closed)
Please log in to bookmark issues
Open Feature request MC-1734

.XLSX Export from Portfolio Backtest is Unacceptably slow

Go to the next issue (open or closed)
Go to the next open issue

I just attepted to export to .XLSX a backtest from the PortfolioTrader. Running the backtest took < 30 seconds. 
Saving the performance report to Excel took over 1 hour! Yes, over 1 hour!
The step that was consuming the most time was the Trades List. The Windows Resource Monitor showed that Excel was consuming an entire CPU core during this time. This indicates that you are using the (very slow) Microsoft Excel COM component to generate the file. 
You have many options to make this feature useable. 
1) You can use a 3rd party XLSX file library (there are many of these). The XLSX file format is essentially xml content wrapped in a zip archive. It is an open standard.
2) You can optimize your use of the current libarary. Perhaps you can write larger blocks and then format them once written, etc.
3) You can modify your trades list to avoid time consuming tasks. Perhaps merging cells or using certain styles is taking a long time. 
4) Some combination of all of these.
If we are to use the Portfolio Trader, and to analyze our backtests, we need the ability to export them. The export feature is currently not useable.

Steps to reproduce this issue

Create a backtest with many trades in the PortfolioTrader.  
My example above was generated using the Pairs example code provided by MultiCharts.
It had 6 symbols (3 pairs), 3-minute bars, 1-year history.  I ran the Master signal on 1 set of pairs, and the slave strategy on another set (same symbols, but with reversed data1, data2 order). 
The backtest generated 38000 trades on 6 symbols.
Running the backtest took

Comments (16)
user-offline.png  PatrickSocal (PatrickSocal)
Sep 11, 2014 - 21:18

I forgot to mention... this test was using MultiCharts 9.0 Beta3. Now I'm using 9.0RC and it looks like there has been no improvement in this issue.

user-offline.png  MultiCharts Support (MultiCharts)
Sep 12, 2014 - 11:54

In MultiCharts 9.1 we will add ability of skipping the export of "List of Trades" data for reducing the total export time.

user-offline.png  PatrickSocal (PatrickSocal)
Sep 12, 2014 - 13:15

Thanks Henry. This may be helpful in some cases, but my main reason for exporting the backtest is to use the list of trades. They are the most important part. I have written external code to blend strategies into a portfolio, and to compute metrics not included in the MC performance report, such as average profit/loss per share. I need the list of trades to do this.If your team could look into their implementation of the Excel export, that would be very helpful. By comparison, I can read/write Excel files to/from Python using the Pandas library much quicker. That is open source code, so perhaps your team can adapt the libraries they are using.Thanks for your consideration.

user-offline.png  hilbert (hilbert2100)
Sep 13, 2014 - 15:40

I think you are going in the wrong direction by just adding the ability to skip "List of Trades" to reduce export time. Most of the times, I just need "List of Trades" and settings and I use my own routines to conduct further analysis.
Rather, a much more basic and comprehensive approach is needed, as suggested by PatrickSocial. I suggest modifying and using Pandas Library to export real fast.
Thanks for considering.

user-offline.png  MultiCharts Support (MultiCharts)
Oct 02, 2014 - 07:17

No workarounds are required. In MultiCharts 9.1 you will be able to select which tabs to include in the export file. I.e. that will be possible to export "List of trades" only.

user-offline.png  PatrickSocal (PatrickSocal)
Oct 02, 2014 - 14:46

Hi Andrew,
Thanks for the reply... but the "workaround" I suggested was to use CSV instead of XLSX to export the "List of Trades". The process of generating an excel file for the "List of Trades" is the very very slow part, but if we can choose CSV instead the feature could become useable. Does that make sense?

user-offline.png  MultiCharts Support (MultiCharts)
Oct 03, 2014 - 10:28

What is the benefit of CSV against XLSX? Only the better (hypothetical) speed of export?

user-offline.png  PatrickSocal (PatrickSocal)
Oct 03, 2014 - 11:02

Hi Andrew,
Yes, exactly. 
When doing the "List of Trades" export, I have had ample time to sit and think about solutions :) And during the export, task manager shows that the Excel process is consuming one entire core (not the MultiCharts processes). Exporting the "List of Trades" to CSV would take the Excel process out of the loop. Perhaps it's worth a try?

user-offline.png  MultiCharts Support (MultiCharts)
Oct 03, 2014 - 11:25

At the moment we don't plan to add opportunity to add opportunity to export it into .CSV. Thank you for understanding.

user-offline.png  PatrickSocal (PatrickSocal)
Oct 03, 2014 - 11:28

Thanks for the update. But I'm very sorry to read this. May I ask why not?

user-offline.png  MultiCharts Support (MultiCharts)
Oct 03, 2014 - 11:34

Unfortunately we cannot implement everything what our customers ask. If you are interested in this feature, we can do it for you on paid basis.

user-offline.png  MAtricks (MAtricks)
Oct 03, 2014 - 12:48

Thank you for implementing a feature which allows us to select what we want to export :)
Would enabling another file type (csv) be a time consuming task? The MC developers must be very busy, but if they're already working on these features...
Exporting the Trades List is quite the task on my PC as well.
Thank you for your consideration.

user-offline.png  MultiCharts Support (MultiCharts)
Oct 07, 2014 - 08:27

Why do you need the .csv? What exactly are going to do шер the list of trades in .csv? What is your final goal?

user-offline.png  PatrickSocal (PatrickSocal)
Oct 07, 2014 - 12:57

I'm not sure about the last person, but in my case, I have written tools in Python and MATLAB to do more analysis on sets of trading strategies. I compute a lot of statistics (including margin/equity ratios, custom drawdown statistics, etc.) that are necessary to analyze our trading program, and to select subsets of strategies that we wish to run live. 
Both Python and MATLAB have libraries for reading/writing XLS and XLSX files. They also work easily with CSV files. So either format will work. 
But the problem now is the extremely slow process of generating the "List of Trades" in an Excel file. I would wager that MultiCharts can export the List of Trades to a CSV file in a few seconds (or less!), while it can take over an hour to generate the Excel file. That ties up the MultiCharts during the export and disrupts our workflow.
It would be wonderful to have a fast CSV export as an alternative.

user-offline.png  MC_Prog (MC_Prog)
Oct 15, 2014 - 09:10

FWIW, I think being able to choose a partial export as is targeted for 9.1 is definitely good.
I also think that exporting just the list of trades, quickly, as .csv would be very helpful.  This is core information for further analysis for many people.

user-offline.png  MultiCharts Support (MultiCharts)
Jul 28, 2015 - 10:24

Now it is possible to select which values to export from Performance Report.

Issue basics
  • Type of issue
    Feature request
  • Category
  • Targeted for
    MultiCharts 9.1 (RELEASED)
  • Status
User pain
  • Type of bug
    Not triaged
  • Likelihood
    Not triaged
  • Effect
    Not triaged
Affected by this issue (2)
People involved
Times and dates
  • Posted at
  • Last updated
Issue details
  • Resolution
    Not determined
  • Severity
Attachments (2)
Commits (0)
There are no code checkins for this issue
Duplicate issues (0)
This issue does not have any duplicates