import xlwings as xw
from xlwings import constants
import pandas as pd
import random
import numpy as np
from numpy.random import uniform
from numpy.random import triangular
from numpy.random import lognormal
import matplotlib.pyplot as plt
import seaborn as sns
from scipy import stats

#### Connect and conf Excel
book = xw.Book("C:\\Sim\VWsim.xlsx")
DCF = book.sheets("DCF")
SIM = book.sheets("Sim")

### Parameters
num_sim = 30000

#Revenue - uniform distribution, manual mode
rev_low = 0.01
rev_high = 0.07

#Operating margin - triangular distribution, 8,5% in Base case
oper_low = 0.07
oper_mode = 0.085
oper_high = 0.10

#Capex reinvestments - triangular distribution, 10% in Base case
capex_low = 0.09
capex_mode = 0.10
capex_high = 0.11

### Sims
def dcf_sim(num_sim, rev_high, rev_low, oper_low, oper_mode, oper_high, capex_low, capex_mode, capex_high):
    outcome = []
    book.sheets("Sim").clear
    for i in range(num_sim):
        revenue=np.random.uniform(rev_low, rev_high)
        DCF.range("D25").value=revenue #Base scenario start modifier, exponential reduction towards 2030 target 
        
        opmarg=np.random.triangular(oper_low, oper_mode, oper_high)
        DCF.range("D34").value=opmarg

        capex=np.random.triangular(capex_low, capex_mode, capex_high)
        DCF.range("D46").value=capex

        priceC=DCF.range("R104").value
        priceC=float(priceC)
        
        priceP=DCF.range("R105").value
        priceP=float(priceP)
        
        IR=DCF.range("R111").value
        IR=float(IR)
        
        ROIC=DCF.range("R112").value
        ROIC=float(ROIC)
        
        growth=DCF.range("R113").value
        growth=float(growth)
        
        
        outcome.append((revenue, opmarg, capex, priceC, priceP, IR, ROIC, growth))
    df = pd.DataFrame(outcome, columns=["Revenue","Operating Margin","Capex","PriceC", "PriceP", "IR", "ROIC", "growth"])
    return df

#run sim func - CAREFUL! Running this will usually take some time.
DCF.range("H3").value = "No" #Disable scenario, switch to manual mode.
df = dcf_sim(num_sim, rev_low, rev_high, oper_low, oper_mode, oper_high, capex_low, capex_mode, capex_high)

# Revert to default input assumption values
DCF.range("D25").value = 0.08 #Revenue 
DCF.range("D34").value = 0.085 #Operating margin
DCF.range("D46").value = 0.10 #Capex

### Copy dataframe to Excel
SIM.range("A1").options(pd.DataFrame, index= False).value = df

### Plotting
#Definitions
rev = df["Revenue"]
opmarg = df["Operating Margin"]
capex = df["Capex"]
stockpriceC = df["PriceC"]
stockpriceP = df["PriceP"]
IR = df["IR"]
ROIC = df["ROIC"]
growth = df["growth"]

#plot style
plt.style.use("seaborn")
sns.set_theme(style="darkgrid")

#Figure
sim_fig = plt.figure(figsize=(12,14))

plt.subplot(4,2,1)
plt.hist(stockpriceC, bins = 100)
plt.xlabel("€ Value per common share")
plt.ylabel("Frequency")
plt.title("Distribution of common stockprice", fontsize = 14)
plt.axvline(stockpriceC.mean(), label = "Average shareprice", color = "red")
plt.legend()

plt.subplot(4,2,3)
plt.hist(stockpriceP, bins = 100)
plt.xlabel("€ Value per preferred share")
plt.ylabel("Frequency")
plt.title("Distribution of preferred stockprice", fontsize = 14)
plt.axvline(stockpriceP.mean(), label = "Average shareprice", color = "red")
plt.legend()

# DCF
plt.subplot(4,2,2)
x = np.sort(df["PriceC"])
y = np.arange(1,len(x)+1)/len(x)
plt.plot(x,y, marker = ".", linestyle = "none")
plt.xlabel("€ Value per common share")
plt.ylabel("Cumulative probability")
plt.title("Cumulative distribution value per common share", fontsize = 13)

plt.subplot(4,2,4)
x = np.sort(df["PriceP"])
y = np.arange(1,len(x)+1)/len(x)
plt.plot(x,y, marker = ".", linestyle = "none")
plt.xlabel("€ Value per pref. share")
plt.ylabel("Cumulative probability")
plt.title("Cumulative distribution value per preferred share", fontsize = 13)

#Revenue growth
plt.subplot(4,2,5)
plt.hist(rev, bins= 100, color = "green")
plt.xlabel("Revenue growth")
plt.ylabel("Frequency")
plt.title("Growth in revenue", fontsize = 14)

#Operating margins
plt.subplot(4,2,6)
plt.hist(opmarg, bins= 100, color = "green")
plt.xlabel("% Operating margin")
plt.ylabel("Frequency")
plt.title("Distribution of operating margins", fontsize = 14)

#Capex investments
plt.subplot(4,2,7)
plt.hist(opmarg, bins = 100, color = "green")
plt.xlabel("Capex")
plt.ylabel("Frequency")
plt.title("Distribution of Capex investments", fontsize = 14)

# Spacing
sim_fig.subplots_adjust(wspace = 0.4, hspace = 0.7)

# Copy to Excel
rng = book.sheets["Sim"].range("J28")
SIM.pictures.add(sim_fig, name = "sim_fig", update = True, top = rng.top, left = rng.left)

#Figure value drivers
sim_figVAL = plt.figure(figsize=(12,18))

# ROIC
plt.subplot(6,2,1)
plt.hist(ROIC, bins = 100, color = "red")
plt.xlabel("Company ROIC rate")
plt.ylabel("Frequency")
plt.title("Distribution of ROIC", fontsize = 14)
plt.axvline(ROIC.mean(), label = "Average ROIC", color = "blue")
plt.legend()

plt.subplot(6,2,2)
x = np.sort(df["ROIC"])
y = np.arange(1,len(x)+1)/len(x)
plt.plot(x,y, marker = ".", linestyle = "none", color = "red")
plt.xlabel("% ROIC")
plt.ylabel("Cumulative probability")
plt.title("Cumulative distribution, ROIC", fontsize = 13)

# Growth
plt.subplot(6,2,3)
plt.hist(growth, bins = 100, color = "orange")
plt.xlabel("Company growth rate")
plt.ylabel("Frequency")
plt.title("Distribution of company growth", fontsize = 14)
plt.axvline(growth.mean(), label = "Average growth", color = "blue")
plt.legend()

plt.subplot(6,2,4)
x = np.sort(df["growth"])
y = np.arange(1,len(x)+1)/len(x)
plt.plot(x,y, marker = ".", linestyle = "none", color = "orange")
plt.xlabel("% Growth")
plt.ylabel("Cumulative probability")
plt.title("Cumulative distribution, company growth", fontsize = 13)

# Investment rate
plt.subplot(6,2,5)
plt.hist(IR, bins = 100, color = "teal")
plt.xlabel("Company investment rate")
plt.ylabel("Frequency")
plt.title("Distribution of investment rate", fontsize = 14)
plt.axvline(IR.mean(), label = "Avg. investment rate", color = "red")
plt.legend()

plt.subplot(6,2,6)
x = np.sort(df["IR"])
y = np.arange(1,len(x)+1)/len(x)
plt.plot(x,y, marker = ".", linestyle = "none", color = "teal")
plt.xlabel("% Investment rate")
plt.ylabel("Cumulative probability")
plt.title("Cumulative distribution, IR", fontsize = 13)

# Spacing
sim_figVAL.subplots_adjust(wspace = 0.4, hspace = 0.9)

# Copy to Excel
rng = book.sheets["Sim"].range("J88")
SIM.pictures.add(sim_figVAL, name = "sim_figVAL", update = True, top = rng.top, left = rng.left)

# Statistics
desc_stat = df.describe([0.10, 0.20, 0.30, 0.40, 0.50, 0.60, 0.70, 0.80, 0.90])
SIM.range("J2").value = desc_stat
SIM.range("J2").value = "Descriptives"

corr_stat = df.corr()
SIM.range("J18").value = corr_stat
SIM.range("J18").value = "Correlations"

df.corr()

###Figure 2, scatters
sim_fig2 = plt.figure(figsize=(10,10))

#Revenue vs. Stockprice
plt.subplot(3,1,1)
sns.regplot(x=rev, y=stockpriceC)
plt.xlabel("Revenue")
plt.ylabel("Stockprice")
plt.title("Revenue vs. Common Stockprice", fontsize = 14)

#Operating margins vs. Stockprice
plt.subplot(3,1,2)
sns.regplot(x=opmarg, y=stockpriceC)
plt.xlabel("% Operating margins")
plt.ylabel("Stockprice")
plt.title("Operating margins vs. Common Stockprice", fontsize = 14)

#Capex vs. Stockprice
plt.subplot(3,1,3)
sns.regplot(x=stockpriceC, y=capex)
plt.xlabel("Stockprice")
plt.ylabel("% Capex")
plt.title("Capex vs. Common Stockprice", fontsize = 14)

# Spacing
sim_fig2.subplots_adjust(wspace = 0.4, hspace = 0.7)

# Copy to Excel
rng = book.sheets["Sim"].range("T39")
SIM.pictures.add(sim_fig2, name = "sim_fig2", update = True, top = rng.top, left = rng.left)

### Formatting cleanup
#cell ranges
output_range = SIM.range("A1").expand() #four first columns
output_header_range = SIM.range("A1").expand("right") #headers A, B, C, D
output_data_range = SIM.range("A2").expand() #data range

stats_range = SIM.range("J2").expand()
stats_name_range = SIM.range("J2").expand("down")
stats_header_range = SIM.range("K2").expand("right")
stats_data_range = SIM.range("K3").expand()
corr_range = SIM.range("J18").expand()
corr_name_range = SIM.range("J18").expand("down")
corr_header_range = SIM.range("K18").expand("right")
corr_data_range = SIM.range("K19").expand()

morestats1_range = SIM.range("T2:U5")
morestats1_header = SIM.range("T2:U2")
morestats1_data_range = SIM.range("T3:U5")

morestats2_range = SIM.range("T7:U10")
morestats2_header = SIM.range("T7:U7")
morestats2_data_range = SIM.range("T8:U10")

morestats3_range = SIM.range("T12:U15")
morestats3_header = SIM.range ("T12:U12")
morestats3_data_range = SIM.range("T13:U15")


#Fonts and size, adjustments
header = [output_header_range, stats_header_range, stats_name_range, corr_name_range, corr_header_range, morestats1_header, morestats2_header, morestats3_header]
for cells in header:
    cells.color = 180,198,231
    cells.api.Font.Name = "Calibri"
    cells.api.Font.Bold = True
    cells.api.Font.Size = 11
    cells.column_width = 15
    
SIM.range("S1").column_width = 2

# Colors
output_data_range.color = (217,225,242)
stats_data_range.color = (217,225,242)
corr_data_range.color = (217,225,242)
morestats1_data_range.color = (217,225,242)
morestats2_data_range.color = (217,225,242)
morestats3_data_range.color = (217,225,242)

# Borders
for border_id in range(7,13):
    output_range.api.Borders(border_id).Color = 0xFFFFFF
    output_range.api.Borders(border_id).Weight = 2
    
    stats_range.api.Borders(border_id).Color = 0xFFFFFF
    stats_range.api.Borders(border_id).Weight = 2
    
    corr_range.api.Borders(border_id).Color = 0xFFFFFF
    corr_range.api.Borders(border_id).Weight = 2

    morestats1_range.api.Borders(border_id).Color = 0xFFFFFF
    morestats1_range.api.Borders(border_id).Weight = 2
    
    morestats2_range.api.Borders(border_id).Color = 0xFFFFFF
    morestats2_range.api.Borders(border_id).Weight = 2
    
    morestats3_range.api.Borders(border_id).Color = 0xFFFFFF
    morestats3_range.api.Borders(border_id).Weight = 2
    
# number formatting
output_data_range.number_format = "0,00"
stats_data_range.number_format = "0,00"
corr_data_range.number_format = "0,0000"

# Alignement
corr_name_range.autofit()
output_range.api.HorizontalAlignment = xw.constants.HAlign.xlHAlignCenter
stats_range.api.HorizontalAlignment = xw.constants.HAlign.xlHAlignCenter

## More stats
# Revenue vs. Price
pearson_coef, p_value = stats.pearsonr(df['Revenue'], df['PriceC'])
resRev_Price = stats.linregress(df['Revenue'], df['PriceC'])
rval1 = (f"{resRev_Price.rvalue**2:.6f}")
SIM.range("T2").value = ("Revenue vs. Price")
SIM.range("T3").value = (f"R^2:",)
SIM.range("U3").value = (rval1)
SIM.range("T4").value = ("P-value:", p_value)
SIM.range("T5").value = ("Pearson Corr(r):", pearson_coef)
# Operating margin vs. Price
pearson_coef, p_value = stats.pearsonr(df['Operating Margin'], df['PriceC'])
resOpmarg_Price = stats.linregress(df['Operating Margin'], df['PriceC'])
rval2 = (f"{resOpmarg_Price.rvalue**2:.6f}")
SIM.range("T7").value = ("Op. margin vs. Price")
SIM.range("T8").value = (f"R^2:")
SIM.range("U8").value = (rval2)
SIM.range("T9").value = ("P-value:", p_value)
SIM.range("T10").value = ("Pearson Corr(r):", pearson_coef)
# Capex vs. Price
pearson_coef, p_value = stats.pearsonr(df['Capex'], df['PriceC'])
resCapex_Price = stats.linregress(df['Capex'], df['PriceC'])
rval3 = (f"{resCapex_Price.rvalue**2:.6f}")
SIM.range("T12").value = ("Capex vs. Price")
SIM.range("T13").value = (f"R^2:")
SIM.range("U13").value = (rval3)
SIM.range("T14").value = ("P-value:", p_value)
SIM.range("T15").value = ("Pearson Corr(r):", pearson_coef)

#heatmap
sim_hmap = plt.figure(figsize=(12,4))
sns.heatmap(df.corr(), annot=True, cmap="YlGnBu")

#Figure
rng = book.sheets["Sim"].range("T18")
SIM.pictures.add(sim_hmap, name = "sim_heatmap", update = True, top = rng.top, left = rng.left)

### Save and close
#book.save("Tsims_completed")
#app = xw.apps.active
#app.quit