Help Please ! Calculations outside of excel and in VBA

[Christos]

Im trying to set up an option pricer where i have unlimted number of calculation which spits out a value. I want to do all the calculations in VBA and then put the single value into excel. From what i hear this is possible but im not sure how to do it. Can anyone help me?

Much appreciated.

 

[pgc01]

Hi Christos

Ok. We've got enough to design the algorithm.

I'm very busy today, but if ffuentes or someone else don't do it before, I will try do this tomorrow.

In the meanwhile please post the values of the parameters you use, so that we can check the results of the code against the values in your post.

I mean the Calcs!B18, Calcs!B19, etc. Also some words about them would be nice (these 2 seem to be up and down probabilities (?)).

Kind regards
PGC

 

[Christos]

Thank you guys, i appreciate this.

Calcs!B18 is 0.6523 and B19 is 0.3477. these are as you say, the former the probability of it going up and the latter it going down.

Once again thank you.

 

[ffuentes]

Ok here goes, I've gotten all the light blue fields to fill in automatically, but i will need all the constants involved in the in the black cells to get them to fill in automatically ... for now give this a try it should insert all the values in the blue cells ....

note that i assumed that there is nothing beyond column Y, also i'm not sure if there are any corners that can be cut here (optimize the code) or if there are other simpler ways to get this done. Also there may be extra variables in here ... i declared a bunch of them in case i needed them and haven't had a chance to see if i could clean up the code.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro2()
<SPAN style="color:#007F00">'</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> const1, const2, const3, a, b, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    const1 = Range("CALCS!B3").Value
    const2 = Range("CALCS!B5").Value
    const3 = Range("CALCS!B7").Value
    a = const1
    
    <SPAN style="color:#00007F">Dim</SPAN> x, y, z <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    x = 25
    y = 1
    z = 1
    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> x > 49
        
        <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> x = -1
            <SPAN style="color:#00007F">If</SPAN> x = 25 And y = 1 <SPAN style="color:#00007F">Then</SPAN>
                Worksheets(1).Cells(x, y).Value = const1
            
            <SPAN style="color:#00007F">ElseIf</SPAN> z = 1 And x <> 25 <SPAN style="color:#00007F">Then</SPAN>
                Worksheets(1).Cells(x, y).Value = Round((Worksheets(1).Cells(x + 2, y - 2).Value + Worksheets(1).Cells(x + 2, y - 2).Value * const2), 2)
                
            <SPAN style="color:#00007F">ElseIf</SPAN> z > 1 And Worksheets(1).Cells(x + 2, y - 2).Value = 0 <SPAN style="color:#00007F">Then</SPAN>
                Worksheets(1).Cells(x, y).Value = Round((Worksheets(1).Cells(x - 2, y - 2).Value - Worksheets(1).Cells(x - 2, y - 2).Value * const3), 2)

            <SPAN style="color:#00007F">ElseIf</SPAN> z > 1 And Worksheets(1).Cells(x + 2, y - 2).Value <> 0 <SPAN style="color:#00007F">Then</SPAN>
                Worksheets(1).Cells(x, y).Value = Round((Worksheets(1).Cells(x - 2, y - 2).Value - Worksheets(1).Cells(x - 2, y - 2).Value * const3), 2)
            
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
            
            x = x - 2
            <SPAN style="color:#00007F">If</SPAN> y < 25 <SPAN style="color:#00007F">Then</SPAN> y = y + 2 <SPAN style="color:#00007F">Else</SPAN> x = -1
    
            
        <SPAN style="color:#00007F">Loop</SPAN>
        
        y = 1 + z * 2
        <SPAN style="color:#00007F">If</SPAN> y < 28 <SPAN style="color:#00007F">Then</SPAN> x = 25 + z * 2
        z = z + 1
        
    <SPAN style="color:#00007F">Loop</SPAN>
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[Christos]

That is brilliant. Thank you very much.

To show my VBA ignorance here, so i can understand for the future, why are the integers x=25, y=1 and z=1?

The constants in the other formula are:

The initial inputted figure so A25 is a constant (this is also the same as const1 in your previous formula). This is 20.

The next constant is Calcs!B4. This is 1.

The third constant is Calcs!B13, this is 0.03

The fourth is Calcs!B18, so 0.6523

and the last is Calcs!B19, so 0.3477.

Once again thank you very much for your help.

 

[ffuentes]

integers x=25, y=1 and z=1

x and y define where you are in the worksheet
since the first entry is in A25 i started off there
column A = 1 and row 25 = 25

z is what i used to help move across and is also used to track how many times the loop has been processed ... this is used in the calculation stages as well (see the if then else statements you will notice that z is in there, used to determine which calculation should be used)

y = 1 + z * 2
If y < 28 Then x = 25 + z * 2
z = z + 1

like i said previously i haven't had the chance to see if it could be optimized in any way so there may be some variables that can be omitted.

I will work on the black cells today

 

[ffuentes]

Question for you:

looking at the formulas in the black squares, and each one is a result on a calculation based on other cells ... i'm guessing here but are the y column values in the black cells also constant? or are they eventually getting a value from somewhere else?

 

[ffuentes]

I'm sorry, i think i had a brain fart, but i'm ok now no worries i figured out the calculations of the black cells

i will post the auto fill solution later today

 

[ffuentes]

Here you go, i wanted to post the final result here ... note that i didn't go past column O so that i could get the exact results that you showed in the forum:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro2()
<SPAN style="color:#007F00">'</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> const1, const2, const3, const4, const5, const6, const7, a, b, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    const1 = Range("CALCS!B3").Value
    const2 = Range("CALCS!B5").Value
    const3 = Range("CALCS!B7").Value
    const4 = Range("Calcs!B13").Value
    const5 = Range("Calcs!B18").Value
    const6 = Range("Calcs!B19").Value
    const7 = Range("Calcs!B4").Value
    a = const1
    b = a + const7
    
    
    <SPAN style="color:#007F00">'light blue cells</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> x, y, z <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    x = 25
    y = 1
    z = 1
    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> x > 39
        
        <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> x = -1
            <SPAN style="color:#00007F">If</SPAN> x = 25 And y = 1 <SPAN style="color:#00007F">Then</SPAN>
                Worksheets(1).Cells(x, y).Value = const1
            
            <SPAN style="color:#00007F">ElseIf</SPAN> z = 1 And x <> 25 <SPAN style="color:#00007F">Then</SPAN>
                Worksheets(1).Cells(x, y).Value = Round((Worksheets(1).Cells(x + 2, y - 2).Value + Worksheets(1).Cells(x + 2, y - 2).Value * const2), 2)
                
            <SPAN style="color:#00007F">ElseIf</SPAN> z > 1 And Worksheets(1).Cells(x + 2, y - 2).Value = 0 <SPAN style="color:#00007F">Then</SPAN>
                Worksheets(1).Cells(x, y).Value = Round((Worksheets(1).Cells(x - 2, y - 2).Value - Worksheets(1).Cells(x - 2, y - 2).Value * const3), 2)

            <SPAN style="color:#00007F">ElseIf</SPAN> z > 1 And Worksheets(1).Cells(x + 2, y - 2).Value <> 0 <SPAN style="color:#00007F">Then</SPAN>
                Worksheets(1).Cells(x, y).Value = Round((Worksheets(1).Cells(x - 2, y - 2).Value - Worksheets(1).Cells(x - 2, y - 2).Value * const3), 2)
            
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
            
            x = x - 2
            <SPAN style="color:#00007F">If</SPAN> y < 15 <SPAN style="color:#00007F">Then</SPAN> y = y + 2 <SPAN style="color:#00007F">Else</SPAN> x = -1
    
            
        <SPAN style="color:#00007F">Loop</SPAN>
        
        y = 1 + z * 2
        <SPAN style="color:#00007F">If</SPAN> y < 28 <SPAN style="color:#00007F">Then</SPAN> x = 25 + z * 2
        z = z + 1
        
    <SPAN style="color:#00007F">Loop</SPAN>
    
    <SPAN style="color:#007F00">'black cells</SPAN>
    x = 12
    y = 15
    z = 0
    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> z = 9
    
        <SPAN style="color:#00007F">If</SPAN> y = 15 <SPAN style="color:#00007F">Then</SPAN>
            x = 12
            <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> x = 44
                <SPAN style="color:#00007F">If</SPAN> Worksheets(1).Cells(x - 1, y).Value < b <SPAN style="color:#00007F">Then</SPAN>
                    Worksheets(1).Cells(x, y).Value = 0
        
                <SPAN style="color:#00007F">Else</SPAN>
                    Worksheets(1).Cells(x, y).Value = Worksheets(1).Cells(x - 1, y).Value - b
    
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
                x = x + 4
            
            <SPAN style="color:#00007F">Loop</SPAN>
        
        <SPAN style="color:#00007F">Else</SPAN>
            x = 12 + z * 2
            <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> x > 40 - z * 2
                Worksheets(1).Cells(x, y).Value = (Exp(-const4)) * ((const5 * Worksheets(1).Cells(x - 2, y + 2)) + (const6 * Worksheets(1).Cells(x + 2, y + 2)))
                x = x + 4
            
            <SPAN style="color:#00007F">Loop</SPAN>
        
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        z = z + 1
        y = y - 2
        
    <SPAN style="color:#00007F">Loop</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

go ahead and post any questions you may have or if something doesn't work right.

 

[Christos]

thank you very much. it works perfectly. very much appreciated.

I just wanted to ask about the equations in between the two 'loop' for both the light blue and black cells. What are they exactly doing?

I doubt this is possible but thought it wont hurt to ask, does VBA have a function to store hardcoded number within it. Could it put all the numbers in the light blue and black cells into VBA. I ask this as this will mean that i am not restrained by the number of cells in excel.

Once again thank you very much for your help!