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

.XLSX Export from Portfolio Backtest is Unacceptably slow

action_vote_minus_faded.png
7
Votes
action_vote_plus_faded.png
next_issue.png
Go to the next issue (open or closed)
next_open_issue.png
Go to the next open issue
Description

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 (20)
#0
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.

#0
user-offline.png  Alex MultiCharts (Alex 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.

#0
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.

#0
user-offline.png  Guest (Guest)
Sep 13, 2014 - 02:44

I strongly agree this feature request.
Just add the ability to select export field.
I just need  "list of trades".

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

Andrew,
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.

#0
user-offline.png  Guest (Guest)
Oct 01, 2014 - 15:35

I see that the team at MC have decided to allow selective export rather than speeding up the export of the "List of Trades". Since many of us find this to be an important piece of information, perhaps we can agree on a workaround? 
Can you add a feature to allow us to export only the "List of Trades" as a CSV file? Generating a CSV is already part of MC's functionaliy in other places, and it's much faster than generating an excel file. For those of us who need the "List of Trades", working with a CSV should be no problem, and far preferable to waiting for 1+ hours for the export to occur.
Thanks for your consideration.

#0
user-offline.png  Alex MultiCharts (Alex 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.

#0
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?

#0
user-offline.png  Alex MultiCharts (Alex MultiCharts)
Oct 03, 2014 - 10:28

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

#0
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?

#0
user-offline.png  Alex MultiCharts (Alex 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.

#0
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?

#0
user-offline.png  Alex MultiCharts (Alex 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.

#0
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.

#0
user-offline.png  Guest (Guest)
Oct 06, 2014 - 15:07

Dear Dear Andrew,
I reckon there is a huge misunderstanding on your part. Exporting as csv instead of xlsx is very very simple and speed improvement is not hypothetical rather it is order of magnitude faster to export as csv instead of xlsx.
xlsx is needed when you have formulas, linked charts etc, however MC exports "Trade List", they are just values (no formulas). Implementing export for a .csv file is very easy and can be done in few hours of development time, by a single developer.
Instead of export taking 1 hr, it will take few minutes when it is implemented as .csv.
I request you to** please** discuss this feature with your development team, they will be able to implement it within few hours. This feature is extremely important if we want to use newly developed functionality of portfolio trader properly. Thanks a lot for your understanding.

#0
user-offline.png  Alex MultiCharts (Alex 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?

#0
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.

#0
user-offline.png  Guest (Guest)
Oct 08, 2014 - 00:56

Dear Andrew,
After exporting trade list, I do a lot of analysis on it using excel/vba. I look at trades profitability by weekdays, I compare trades profitability with market volatility, I look for auto-correlation in trades, I look for seasonality effects in trades and many more things. Please note that none of such further analysis is available within MC.
So, I do need to export trade list, and this is why we are requesting you to kindly bring the .csv export format, so that export can happen fast. As things stand now, portfolio trader in MC9 is a great improvement but without this export facility as .csv file, I just cannot use the full power of MC.
So, please request you to enhance this functionality as soon as possible.

#0
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.

#0
user-offline.png  Alex MultiCharts (Alex MultiCharts)
Jul 28, 2015 - 10:24

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

History
Issue basics
  • Type of issue
    Feature request
  • Category
    Performance
  • Targeted for
    MultiCharts 9.1 (RELEASED)
  • Status
    Released
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
    Normal
Attachments (2)
Commits (0)
There are no code checkins for this issue
Duplicate issues (0)
This issue does not have any duplicates