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

RickTg

#1
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

RickTg

#2
Nevermind.

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

Eugene

#3
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

KGo

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