Export to Excel Very Slow  [SOLVED]

Questions about MultiCharts and user contributed studies.
dwitkin
Posts: 36
Joined: 18 Apr 2013
Has thanked: 41 times
Been thanked: 11 times

Export to Excel Very Slow

Postby dwitkin » 22 Jun 2013

Hi. I searched the forum and didn't see another post about this.

When I export my Portfolio Backtester reports to Excel, the process often takes more than 2 minutes to complete, even when there are a relatively small number of trades (e.g., 100 - 200) in the output. My computer is fairly fast - it only takes 5 - 10 seconds to evaluate a sample portfolio, but that same portfolio takes 2+ minutes to export to excel. This seems way too slow, given that all of the data has already been produced.

Does it take everyone this long to export to Excel? If this is a known issue, are there any configuration changes or troubleshooting steps to help resolve the issue?

Related question: Exporting to Excel actually starts Excel in the foreground and I can see MC populating into excel worksheets as it does the export. This seems like an unusual way to save to Excel format, but maybe it is necessary for some reason (e.g., proper handling of the graphics)?

Thanks for your suggestions / help.
Dave

User avatar
Henry MultiСharts
Posts: 9165
Joined: 25 Aug 2011
Has thanked: 1264 times
Been thanked: 2957 times

Re: Export to Excel Very Slow

Postby Henry MultiСharts » 25 Jun 2013

Hello Dave,

What is the resolution of the data and data range you are using in your porfolio?
(ex. 2 min bars, 50 days range).

dwitkin
Posts: 36
Joined: 18 Apr 2013
Has thanked: 41 times
Been thanked: 11 times

Re: Export to Excel Very Slow

Postby dwitkin » 25 Jun 2013

Roughly 12 years of daily bars was used for the test.

User avatar
Henry MultiСharts
Posts: 9165
Joined: 25 Aug 2011
Has thanked: 1264 times
Been thanked: 2957 times

Re: Export to Excel Very Slow

Postby Henry MultiСharts » 26 Jun 2013

Hello Dave,

We have tested this case in our environment. Unfortunately that is not technically possible to speed up the export process.
One thing that can be helpful is optional data sheet export of the report - you can vote for this feature here.

dwitkin
Posts: 36
Joined: 18 Apr 2013
Has thanked: 41 times
Been thanked: 11 times

Re: Export to Excel Very Slow

Postby dwitkin » 26 Jun 2013

Thanks Henry. I appreciate you guys looking at it.

Not to quibble, but "not technically possible" seems a bit over the top. If you guys simply did a text-only CSV export of the performance report, there is no way it should take more than 10 - 15 seconds, tops.

I've voted for the PM ticket you mentioned, but please consider / mention to the developers it should be relatively easy to add an Export Backtest Results to CSV format (text only) since all of the metrics have already been generated at the time the export is initiated. A CSV export method should be 10X faster than what the current export design does.

Hello Dave,

We have tested this case in our environment. Unfortunately that is not technically possible to speed up the export process.
One thing that can be helpful is optional data sheet export of the report - you can vote for this feature here.

enalmada
Posts: 4
Joined: 02 Aug 2010
Been thanked: 10 times

Re: Export to Excel Very Slow

Postby enalmada » 14 Dec 2014

Has anyone compared if openoffice or excel exports faster?

Using ods format, it takes over a half hour on "Writing list of trades sheet..." for around 50k trades (12 years of daily bar portfolio strategy). Openoffice never goes above 10% cpu, multicharts never goes above 3% during export and the total file size is under 10 megs which made me think that something isn't running as efficiently as it could. I need the trades data so an option of choosing not to export list of trades wouldn't be useful but I can see it helpful for people who don't need trades.

If excel/openoffice are the bottleneck, perhaps the performance report exported to xml could be considered in Portfolio Trader?

User avatar
Henry MultiСharts
Posts: 9165
Joined: 25 Aug 2011
Has thanked: 1264 times
Been thanked: 2957 times

Re: Export to Excel Very Slow

Postby Henry MultiСharts » 15 Dec 2014

enalmada, that is possible to export report in XML format and then convert it into csv. Here is how to do that.

enalmada
Posts: 4
Joined: 02 Aug 2010
Been thanked: 10 times

Re: Export to Excel Very Slow

Postby enalmada » 15 Dec 2014

Unless I am crazy, Portfolio Backtest sadly has no such XML export option. I created an issue for this:
https://www.multicharts.com/pm/viewissu ... no=MC-1695

XML export is desperately needed in Portfolio Backtest because lots of assets at the same time can generate huge amounts of trades. I have spent so many hours of my life waiting for exports to save. Is there anything you can do to bring an end to this madness and get us xml export on Portfolio Backtest?
Attachments
portfolioTraderSaveTypes.png
portfolio has ods/xls only
(13.25 KiB) Downloaded 1201 times

User avatar
Henry MultiСharts
Posts: 9165
Joined: 25 Aug 2011
Has thanked: 1264 times
Been thanked: 2957 times

Re: Export to Excel Very Slow

Postby Henry MultiСharts » 16 Dec 2014

enalmada, the XML export file is created for the RINA application. It supports only individual report files, it is not designed for portfolio metrics. I have forwarded your suggestion to improve the report export speed to the management of the company.

User avatar
Henry MultiСharts
Posts: 9165
Joined: 25 Aug 2011
Has thanked: 1264 times
Been thanked: 2957 times

Re: Export to Excel Very Slow  [SOLVED]

Postby Henry MultiСharts » 03 Aug 2017

Dear users,

We had a few improvements in the platform after this topic was active:
1. Introduced new feature for exporting segments of the Performance Report - once you click "Save" in the report window, you can choose which sections of the report to export. This allows skipping export of the information you do not need.
2. List of Trades from the Portfolio Performance Report can now be saved into csv / txt. Select the “Text File with the List of Trades only (*csv; *txt)” in the “Save as type” selector upon export.


Return to “MultiCharts”