Rolling Excel Solver

[joe888]

Hi guys,

I will appreciate if someone provide me the way to use excel solver function for rolling window i.e. automatically calculation (using excel solver) each window 911 times.

Sincerely

fariz

 

[shg]

Welcome to the board.

That's not enough information to help. How is your data arranged? Describe exactly how you would do it manually.

 

[joe888]

Thank you for quick reply,

I believe you will provide me assistance;

I have 665 columns containing 250 observations in each.
i need to estimate one coefficient for each window (thus 665 coefficients) using solver formula below: So As you see I need more time to do it manually. is there any way to write solver function in formula case? or run some macros? Thanks in advance

-- removed inline image ---

 

[joe888]

-- removed inline image ---

 

[shg]

I don't know what it is that isn't posting successfully, but so far I don't get the problem.

 

[joe888]

these my solver inputs

set target cell : $B$18

equal to value of 0

by changing value of $B$15

constraints

$B$15 <= $B$19
$B$15 >= $B$24

 

[shg]

What do the next two look like?

 

[joe888]

I just need to implement these solver for each 665 column and get one variable. But this will take some time if I every time launch solver. Is there any way to run solver or (alternative function) automatically for each column?

 

[shg]

Is there any way to run solver or (alternative function) automatically for each column?

Yes, there is, but showing the code requires knowing how the data is arranged. So ...

What do the next two look like?

 

[joe888]

This is my excel sheet looks like
And left side is just number to show you solver parameters place.
I will estimate omega (B 15 ) by solver. So I have 665 omegas to estimate.
<table border="0" cellpadding="0" cellspacing="0" width="262"><col style="mso-width-source:userset;mso-width-alt:6838;width:140pt" width="187"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <tbody><tr style="height:12.75pt" height="17"> <td class="xl70" style="height:12.75pt;width:140pt" height="17" width="187">
</td> <td class="xl69" style="width:56pt" width="75">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">
</td> <td class="xl72">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">
</td> <td class="xl72">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">
</td> <td class="xl69">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">
</td> <td class="xl69">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">
</td> <td class="xl71"> <table border="0" cellpadding="0" cellspacing="0" width="334"><col style="width:54pt" width="72"> <col style="mso-width-source:userset;mso-width-alt:6838;width:140pt" width="187"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <tbody><tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt;width:54pt" height="17" width="72">1</td> <td class="xl69" style="width:140pt" width="187">COLUMN A</td> <td class="xl69" style="width:56pt" width="75">COLUMNB
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">2</td> <td class="xl70"> Returns Parameters</td> <td class="xl69">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">3</td> <td class="xl69">Annual Mean</td> <td class="xl72">2%</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">4</td> <td class="xl69">Volatility</td> <td class="xl72">25%</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">5</td> <td class="xl69">Mean over holding period</td> <td class="xl69">0.0008</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">6</td> <td class="xl69">Stdev over holding period</td> <td class="xl69">0.05</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">7</td> <td class="xl69">Skewness</td> <td class="xl71">-0.2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">8</td> <td class="xl69">XS Kurtosis</td> <td class="xl71">4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">9</td> <td class="xl70">Johnson Parameters</td> <td class="xl69">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">10</td> <td class="xl69">Psi</td> <td class="xl73">0.00541</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">11</td> <td class="xl69">Lambda</td> <td class="xl73">0.05864</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">12</td> <td class="xl69">Gamma</td> <td class="xl73">0.09389</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">13</td> <td class="xl69">Delta</td> <td class="xl73">1.49502</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">14</td> <td class="xl69">Theta</td> <td class="xl73">0.06280</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">15</td> <td class="xl69">Omega</td> <td class="xl74">1.56425</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">16</td> <td class="xl70">Tuenter's Algorithm</td> <td class="xl73">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">17</td> <td class="xl69">m</td> <td class="xl73">0.56155</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">18</td> <td class="xl69">Solver Condition</td> <td class="xl73">0.00000</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">19</td> <td class="xl69">Upper Bound</td> <td class="xl73">1.56965</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">20</td> <td class="xl69">Omega 1</td> <td class="xl74">1.20657</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">21</td> <td class="xl69">Goal Seek Condition 1</td> <td class="xl73">0.00000</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">22</td> <td class="xl69">Omega 2</td> <td class="xl74">1.00443</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">23</td> <td class="xl69">Goal Seek Condition 2</td> <td class="xl73">0.00000</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">24</td> <td class="xl69">Lower Bound</td> <td class="xl73">1.20667</td> </tr> </tbody></table></td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">
</td> <td class="xl71">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl70" style="height:12.75pt" height="17">
</td> <td class="xl69">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">
</td> <td class="xl73">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">
</td> <td class="xl73">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">
</td> <td class="xl73">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">
</td> <td class="xl73">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">
</td> <td class="xl73">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">
</td> <td class="xl74">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl70" style="height:12.75pt" height="17">
</td> <td class="xl73">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">
</td> <td class="xl73">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">
</td> <td class="xl73">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">
</td> <td class="xl73">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">
</td> <td class="xl74">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">
</td> <td class="xl73">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">
</td> <td class="xl74">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">
</td> <td class="xl73">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl69" style="height:12.75pt" height="17">
</td> <td class="xl73">
</td> </tr> </tbody></table>