Thread: [Req] Curve Fitting Using Excel (explained better with attachment)

Hi All,

Apologies for a duplication of thread as I needed to explain my query better by use of an attachment.

In the attachment, you’ll see a stock’s price over 3 consecutive days in yellow (C5:E5). In grey (C9:E9) you’ll see formulae for an equation of a curve. In light orange (C7:E7), you’ll see the difference between the above 2 mentioned lines. In blue (K7), you’ll see a formula, based on the orange figures, that can only be zero if all the orange figures are zero. Select box K7, go to Tools, Solver, Set target cell to K7, Equal to Value of 0, By Changing Cells O1:O2. Then hit Solve. This produces a curve that visually looks like a perfect fit (see graph), however the values of A and B produced (in O1:O2) do not look meaningful, as solver always produces a small negative term for A and a value approximately =1 for B, no matter what values of the share price I put in.

Ultimately what I’m trying to do is to calculate a speed at which the stock price is turning over the 3 days. My understanding is that I can do this by curve fitting, calculating meaningful A and B values and then differentiating the curve twice (in short, finding 2A).

Can anyone tell me how to calculate a meaningful speed/rate of turning, either by the above methodology or an alternative one (preferably using excel)

I hope this is clearer than the original explanation. Thanks In Advance for all Responses

An Excel file is more than likely too big. I may be able to help with your Excel issue if you outline it.

Originally Posted by FrCrilly

Hi All,

Apologies for a duplication of thread as I needed to explain my query better by use of an attachment.

In the attachment, you’ll see a stock’s price over 3 consecutive days in yellow (C5:E5). In grey (C9:E9) you’ll see formulae for an equation of a curve. In light orange (C7:E7), you’ll see the difference between the above 2 mentioned lines. In blue (K7), you’ll see a formula, based on the orange figures, that can only be zero if all the orange figures are zero. Select box K7, go to Tools, Solver, Set target cell to K7, Equal to Value of 0, By Changing Cells O1:O2. Then hit Solve. This produces a curve that visually looks like a perfect fit (see graph), however the values of A and B produced (in O1:O2) do not look meaningful, as solver always produces a small negative term for A and a value approximately =1 for B, no matter what values of the share price I put in.

Ultimately what I’m trying to do is to calculate a speed at which the stock price is turning over the 3 days. My understanding is that I can do this by curve fitting, calculating meaningful A and B values and then differentiating the curve twice (in short, finding 2A).

Can anyone tell me how to calculate a meaningful speed/rate of turning, either by the above methodology or an alternative one (preferably using excel)

I hope this is clearer than the original explanation. Thanks In Advance for all Responses

It might help if we could open and read the attachment.

RonL