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
Export to Excel Very Slow [SOLVED]
- Henry MultiСharts
- Posts: 9165
- Joined: 25 Aug 2011
- Has thanked: 1264 times
- Been thanked: 2957 times
Re: Export to Excel Very Slow
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).
What is the resolution of the data and data range you are using in your porfolio?
(ex. 2 min bars, 50 days range).
- Henry MultiСharts
- Posts: 9165
- Joined: 25 Aug 2011
- Has thanked: 1264 times
- Been thanked: 2957 times
Re: Export to Excel Very Slow
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.
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.
Re: Export to Excel Very Slow
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.
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.
Re: Export to Excel Very Slow
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?
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?
- Henry MultiСharts
- Posts: 9165
- Joined: 25 Aug 2011
- Has thanked: 1264 times
- Been thanked: 2957 times
Re: Export to Excel Very Slow
enalmada, that is possible to export report in XML format and then convert it into csv. Here is how to do that.
Re: Export to Excel Very Slow
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?
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
- Henry MultiСharts
- Posts: 9165
- Joined: 25 Aug 2011
- Has thanked: 1264 times
- Been thanked: 2957 times
Re: Export to Excel Very Slow
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.
- Henry MultiСharts
- Posts: 9165
- Joined: 25 Aug 2011
- Has thanked: 1264 times
- Been thanked: 2957 times
Re: Export to Excel Very Slow [SOLVED]
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.
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.