Help - Excel Solver giving different values than LSTM book

[sfclimbers]

Hi Ralph, group members,

Can somebody please tell me what settings to use to get the Solver tool of Microsoft Excel to agree with the book, and if I must exclude the first row of data in order to derive the same results as the book?

I'm trying to duplicate the results of Table 5.5 on page 126 of "The Leverage Space Trading Model".

This table was produced using the formula on page 124:

  RX'(b, q) = asymptote - variableA * EXP(-variableB * q)

using the values found on page 125, which are said to have been derived by the Solver tool of Microsoft Excel:

  asymptote: 0.48406

  variable A:  0.37418

  variable B:  0.137892

When I use the given values in my spreadsheet, I do in fact get the table results of page 126. So, I'm relatively confident that my spreadsheet has been constructed correctly (see link below for viewing spreadsheet hosted on Google).

However, when I run the Solver tool, I get different values for all of asymptote, variable A and variable B as follows:

  asymptote: 0.496277925

  variable A:  0.333338622

  variable B:  0.111279749

The book goes on to show the results graphically on page 127 where I notice that the first data point seems to have been excluded (e.g. the graph shows the first two data points at .25 followed by two at .3125 etc. whereas the table clearly starts with three data points at .25 followed by two at .3125 etc.).

When I remove the first data point from the Solver calculations (i.e. remove row for Play # = 2), the numbers get much closer to those published, but still not quite the same:

  asymptote: 0.487071477

  variable A:  0.359372382

  variable B:  0.129900308

I'm using Microsoft Excel, 32 bit version 2003.

I've replicated my spreadsheet on Google Docs for all to see. Though, the Google Solver tool does not work for non-linear problems, so this is only good to show my work, not for solving the problem.

https://docs.google.com/spreadsheet/ccc?key=0Aif-jDtqXw2IdEdrUjhDRGZHaGd3a2trWFk2ckpaV3c&usp=sharing

As an additional sanity check, I've used an online tool for non linear least squares minimization as follows:

http://www.colby.edu/chemistry/PChem/scripts/lsfitpl.html

using the observed data of page 126 for data input pairs:

2 0.250000

3 0.250000

4 0.250000

5 0.312500

6 0.312500

7 0.367188

8 0.367188

9 0.367188

10 0.389648

11 0.389648

12 0.413818

13 0.413818

14 0.436829

15 0.436829

16 0.436829

17 0.447441

18 0.447441

19 0.459791

20 0.459791

21 0.459791

22 0.466089

23 0.466089

24 0.473830

25 0.473830

26 0.482092

Fitness function selection: a exp(-bx) + c

Parameter guesses: a = -.5, b = .5, c = .5 (note value of "a" must be negative due to differences how function is written).

Convergence mode: Strongly Damped

The results more closely match my own than the book:

c=  0.49583 +- 0.0094

a= -0.3332 +- 0.0109

b=  0.1117 +- 0.012

Again, removing the first data point (i.e. 2, 0.25) gets much closer to book:

c=  0.48688 +- 0.007

a= -0.3594 +- 0.0148

b=  0.1302 +- 0.012

Any explanation appreciated. Many thanks in advance,

Mike

[Brady Preston]

Have you tried coding it?

--
You received this message because you are subscribed to the Google Groups "Leverage Space Trading" group.
To unsubscribe from this group and stop receiving emails from it, send an email to leverage-space-tr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[sfclimbers]

Yes, I've converted the javascript Solver of http://www.colby.edu/chemistry/PChem/scripts/lsfitpl.html to Java, and so am getting exactly the same answer as that site. That code uses slightly different stopping rules than Microsoft (e.g. stops after two iterations of minimal improvement vs. Microsoft's documented five iterations). So, I'm not surprised about the difference between it and Microsoft.

The part that I remain concerned about is that my Microsoft results are different than the book's Microsoft results. I want to make sure that I am not doing anything wrong before continuing.

Has anyone else tried to replicate the results published in the book?

Thanks

To unsubscribe from this group and stop receiving emails from it, send an email to leverage-space-trading+unsub...@googlegroups.com.

[sfclimbers]

Anybody?

Ralph, if you're still out there, do you recall which version of Excel you were running and what data set you used?

Josh, have you not tried this for yourself?

Thanks

[Joshua Ulrich]

Mike,

I don't use Excel and I don't trust its precision for calculations
like this. Also, if you're typing in the data as displayed in the
book, you may be missing some precision Ralph actually used when
calculating the probabilities.

Best,
--
Joshua Ulrich | about.me/joshuaulrich
FOSS Trading | www.fosstrading.com

> --
> You received this message because you are subscribed to the Google Groups
> "Leverage Space Trading" group.
> To unsubscribe from this group and stop receiving emails from it, send an

> email to leverage-space-tr...@googlegroups.com.