Optimization results not handled correctly when pasted into Excel [SOLVED]
Author: RickTg
Creation Date: 10/9/2017 10:29 PM
profile picture


I have a need to analyze the optimizer results output in Excel. After I run the optimizer (Particle Swarm GPAC PSO) against my strategy, I right click the results page and select 'copy to clipboard'. I then start Excel and ctrl V the contents of the clipboard into an empty Excel page.

Any field in the optimizer results that has a comma in it (such as net profit 6,278,272.97) uses in Excel as many adjacent cells as there are commas. In other words, it treats the clipboard as a comma delimited file. This is unacceptable for Excel analysis. I have tried reconstituting the results data, but if 2 or more values have a different number of commas, the results use up a differing number of Excel cells thus causing the resulting Excel file to become severely misaligned. For example

Optimizer Results Excel results

Net Profit APR% Net Profit APR%
421,007.49 14.33 421 7.49 14.33
6,278,272.97 33.51 6 278 272.97 33.51

I hope this example makes sense.

Is there any way of causing the optimizer results to not include commas or (I know this is out of the domain of the Wealth-Lab group, but maybe somebody knows) to cause Excel to not treat the clipboard as a comma delimited file when performing a paste operation?

Thank you

profile picture



I just discovered the solution while playing with the Excel 'paste special' option.
profile picture


Another solution is to "Save to File..." via the same right click menu and then have the file imported into Excel or OpenOffice. The import wizard has a choice of delimiter (tab comma space etc).
profile picture


Also note: Pasting data into Excel respects the Excel >>Data>> "Text to Columns" delimiter settings. If comma is selected any paste with commas will populate multiple columns as the OP experienced. This is a global Excel setting so it applies to all workbooks of the Excel instance. I typically leave the Excel Text to Column delimiter as Tab with " as the text qualifier. Comma is not required to open or work with a CSV file or WL paste.