Re: combining System Reports for Excel

[Bob Fulks <bfulks@xxxxxxxxxxxx>]

At 2:48 AM -0500 2/2/99, kathy t wrote:

>To date ive had to manually type into Excel the results of my individual
>system reports. is there anyway to send the system reports to an Ascii
>file to be later imported into Excel. i would very much like to scan a
>few hundred stocks a find out quickly how my system faired without
>having to manually tally everything (what a pain). while the fileappend
>do this? if so how?


You can easily cause your program to print a one-line summary for each
scan. Then print this line to a file, which you later open in Excel. As an
example, consider the code below.

-----


if LastbarOnChart then begin

   Return = 100 * (Power((NetValue / StartEq), (253 / TotBars)) - 1);

   Print(
      NetProfit:6:0,                        ",",   {Net Profit-$}
      TotalTrades:4:0,                      ",",   {Trades}
      -100*NetProfit/MaxIDDrawDown:5:0,     "%,",  {ROA-%}
      -GrossProfit/GrossLoss:2:2,           ",",   {Profit Factor}
      PercentProfit:3:0,                    "%,",  {Percent Profitable}
      100*Bars.In.Mkt/TotBars:3:0,          "%,",  {Percent of bars in market}
      IRR:4:0,                              "%,",  {IRR-%}
      Return:4:0,                           "%,",  {Return-%}
      Sharpe:2:2,                           ",",   {Sharpe Ratio}
      MP:2:0,                               ", ",  {Market Position}
      GetSymbolName                         ", "   {Symbol}
      );
end;

-----

You can see that I added commas and % signs to allow me to easily import
the data into Excel.

Most of the names in the print statement are EasyLanguage functions. A few,
such as "Bars.In.Market", "IRR", and "Sharpe" are ones I calculated earlier
in this program, but you get the idea. You can get most of the numbers in
the System Report as EasyLanguage functions or the ratio of two functions
(such as Return on Account).

You need to add to your "print setup" some simple character printer driver.
I use the Epson LQ-200. Then set this to print to a file. I can then tell
TradeStation to "Print" to that printer (using the "Print Setup" dialog, so
that it will send a stream of ASCII characters to a file. It asks you to
name the file. (I got this printing tip from Sam Tennis of Vista Research.)

I added a comma at both the beginning and end of each line because the
print driver adds some control characters at the beginning and end of some
lines. This allows me to just not import these "columns" of junk into
Excel. You need to set you print log to accept as many lines as you expect
under "Tools - Options - General" in TradeStation". The maximum is 750
lines. There is also a maximum number of characters in a line but I don't
recall what it is.

You can write the line to a file instead of the print log but I have found
that this is much slower.

Hope this is helpful.


Bob Fulks