Dataframe with Monte Carlo Simulation calculation next row Problem

Question

I want to build up a Dataframe from scratch with calculations based on the Value before named Barrier option. I know that i can use a Monte Carlo simulation to solve it but it just wont work the way i want it to.

The formula is:

Value in row before * np.exp((r-sigma**2/2)*T/TradingDays+sigma*np.sqrt(T/TradingDays)*z)

The first code I write just calculates the first column. I know that I need a second loop but can't really manage it.

The result should be, that for each simulation it will calculate a new value using the the value before, for 500 Day meaning S_1 should be S_500 with a total of 1000 simulations. (I need to generate new columns based on the value before using the formular.) similar to this: So for the 1. Simulations 500 days, 2. Simulation 500 day and so on...

import numpy as np
import pandas as pd
from scipy.stats import norm
import random as rd
import math 

simulation = 0
S_0 = 42
T = 2
r = 0.02
sigma = 0.20
TradingDays = 500

df = pd.DataFrame()

for i in range (0,TradingDays):
    z = norm.ppf(rd.random())
    simulation = simulation + 1

    S_1 = S_0*np.exp((r-sigma**2/2)*T/TradingDays+sigma*np.sqrt(T/TradingDays)*z)


    df = df.append ({

                    'S_1':S_1,    
                    'S_0':S_0

                     }, ignore_index=True)

    df = df.round  ({'Z':6,
                     'S_T':2
                     })
    df.index += 1
    df.index.name = 'Simulation'


print(df)

I found another possible code which i found here and it does solve the problem but just for one row, the next row is just the same calculation. Generate a Dataframe that follow a mathematical function for each column / row

If i just replace it with my formular i get the same problem.

replacing:

exp(r - q * sqrt(sigma))*T+ (np.random.randn(nrows) * sqrt(deltaT)))

with:

exp((r-sigma**2/2)*T/nrows+sigma*np.sqrt(T/nrows)*z))

import numpy as np
import pandas as pd
from scipy.stats import norm
import random as rd
import math 

S_0 = 42
T = 2
r = 0.02
sigma = 0.20
TradingDays = 50
Simulation = 100

df = pd.DataFrame({'s0': [S_0] * Simulation})

for i in range(1, TradingDays):
    z = norm.ppf(rd.random())

    df[f's{i}'] = df.iloc[:, -1] * np.exp((r-sigma**2/2)*T/TradingDays+sigma*np.sqrt(T/TradingDays)*z)

print(df)

I would work more likely with the last code and solve the problem with it.

Answer 1

How about just overwriting the value of S_0 by the new value of S_1 while you loop and keeping all simulations in a list? Like this:

import numpy as np
import pandas as pd
import random
from scipy.stats import norm


S_0 = 42
T = 2
r = 0.02
sigma = 0.20
trading_days = 50
output = []

for i in range(trading_days):
    z = norm.ppf(random.random())
    value = S_0*np.exp((r - sigma**2 / 2) * T / trading_days + sigma * np.sqrt(T/trading_days) * z)
    output.append(value)
    S_0 = value

df = pd.DataFrame({'simulation': output})

Perhaps I'm missing something, but I don't see the need for a second loop.

Also, this eliminates calling df.append() in a loop, which should be avoided. (See here)

Answer 2

Solution based on the the answer of bartaelterman, thank you very much!

import numpy as np
import pandas as pd
from scipy.stats import norm
import random as rd
import math 


#Dividing the list in chunks to later append it to the dataframe in the right order
def chunk_list(lst, chunk_size):
    for i in range(0, len(lst), chunk_size):
        yield lst[i:i + chunk_size]

def blackscholes():
    d1 = ((math.log(S_0/K)+(r+sigma**2/2)*T)/(sigma*np.sqrt(2)))
    d2 = ((math.log(S_0/K)+(r-sigma**2/2)*T)/(sigma*np.sqrt(2)))
    preis_call_option = S_0*norm.cdf(d1)-K*np.exp(-r*T)*norm.cdf(d2)
    return preis_call_option


K = 40
S_0 = 42
T = 2
r = 0.02
sigma = 0.2
U = 38
simulation = 10000
trading_days = 500
trading_days = trading_days -1

#creating 2 lists for the first and second loop
loop_simulation = []
loop_trading_days = []


#first loop calculates the first column in a list
for j in range (0,simulation):
    print("Progressbar_1_2 {:2.2%}".format(j / simulation), end="\n\r")
    S_Tag_new = 0
    NORM_S_INV = norm.ppf(rd.random())
    S_Tag = S_0*np.exp((r-sigma**2/2)*T/trading_days+sigma*np.sqrt(T/trading_days)*NORM_S_INV)
    S_Tag_new = S_Tag
    loop_simulation.append(S_Tag)




#second loop calculates the the rows for the columns in a list
    for i in range (0,trading_days):
        NORM_S_INV = norm.ppf(rd.random())
        S_Tag = S_Tag_new*np.exp((r-sigma**2/2)*T/trading_days+sigma*np.sqrt(T/trading_days)*NORM_S_INV)
        loop_trading_days.append(S_Tag)
        S_Tag_new = S_Tag 

#values from the second loop will be divided in number of Trading days per Simulation           
loop_trading_days_chunked = list(chunk_list(loop_trading_days,trading_days))

#First dataframe with just the first results from the firstloop for each simulation
df1 = pd.DataFrame({'S_Tag 1': loop_simulation})

#Appending the the chunked list from the second loop to a second dataframe
df2 = pd.DataFrame(loop_trading_days_chunked)

#Merging both dataframe into one
df3 = pd.concat([df1, df2], axis=1)