def get_sum( self, row: int_or_str, first: str, adds: List[str], subtracts: List[str], audit: bool = False, text: str = None, ): col = 1 if audit else self.len_data + 1 for i in range(self.len_data if audit else self.len_pred): sum_formula = f"={dcf_model.letters[col+i]}{self.title_to_row(first)}" for item in adds: sum_formula += f"+{dcf_model.letters[col+i]}{self.title_to_row(item)}" for item in subtracts: sum_formula += f"-{dcf_model.letters[col+i]}{self.title_to_row(item)}" rowI = row if isinstance(row, int) else self.title_to_row(row) dcf_model.set_cell( self.ws1, f"{dcf_model.letters[col+i]}{rowI}", sum_formula, num_form=dcf_model.fmt_acct, ) if text: self.custom_exp(row, text)
def get_sum( self, row: Union[int, str], first: str, adds: List[str], subtracts: List[str], audit: bool = False, text: str = None, ): col = 1 if audit else self.info["len_data"] + 1 for i in range( self.info["len_data"] if audit else self.info["len_pred"]): sum_formula = f"={dcf_static.letters[col+i]}{self.title_to_row(first)}" for item in adds: sum_formula += f"+{dcf_static.letters[col+i]}{self.title_to_row(item)}" for item in subtracts: sum_formula += f"-{dcf_static.letters[col+i]}{self.title_to_row(item)}" rowI = row if isinstance(row, int) else self.title_to_row(row) dcf_model.set_cell( self.ws[1], f"{dcf_static.letters[col+i]}{rowI}", sum_formula, num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) if text: self.custom_exp(row, text)
def get_data(self, statement: str, row: int, header: bool) -> pd.DataFrame: df, rounding, _ = dcf_model.create_dataframe(self.info["ticker"], statement) if df.empty: raise ValueError("Could generate a dataframe for the ticker") self.info["rounding"] = rounding if not self.info["len_data"]: self.info["len_data"] = len(df.columns) self.ws[1][f"A{row}"] = dcf_static.statement_titles[statement] self.ws[1][f"A{row}"].font = dcf_static.bold_font rowI = row + 1 names = df.index.values.tolist() for name in names: self.ws[1][f"A{rowI}"] = name if name in dcf_static.sum_rows: length = self.info["len_data"] + (self.info["len_pred"] if statement != "CF" else 0) for i in range(length): if statement == "CF" and name == "Net Income": pass else: self.ws[1][ f"{dcf_static.letters[i+1]}{rowI}"].font = dcf_static.bold_font self.ws[1][ f"{dcf_static.letters[i+1]}{rowI}"].border = dcf_static.thin_border_top rowI += 1 column = 1 for key, value in df.iteritems(): rowI = row if header: dcf_model.set_cell( self.ws[1], f"{dcf_static.letters[column]}{rowI}", float(key), font=dcf_static.bold_font, ) for item in value: rowI += 1 m = 0 if item is None else float(item.replace(",", "")) dcf_model.set_cell( self.ws[1], f"{dcf_static.letters[column]}{rowI}", m, num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) column += 1 return df
def custom_exp(self, row: Union[int, str], text: str, ws: int = 1, column: str = None): if ws == 1: rowT = row if isinstance(row, int) else self.title_to_row(row) col = self.info["len_pred"] + self.info["len_data"] + 3 dcf_model.set_cell( self.ws[1], f"{dcf_static.letters[col+2]}{rowT}", dcf_static.letters[self.letter], font=dcf_static.red, ) if ws == 2: dcf_model.set_cell( self.ws[2], f"{column}{row}", dcf_static.letters[self.letter], font=dcf_static.red, ) dcf_model.set_cell( self.ws[3], f"A{self.letter+4}", dcf_static.letters[self.letter], font=dcf_static.red, ) dcf_model.set_cell(self.ws[3], f"B{self.letter+4}", text) self.letter += 1
def custom_exp(self, row: int_or_str, text: str, ws: int = 1, column: str = None): if ws == 1: rowT = row if isinstance(row, int) else self.title_to_row(row) col = self.len_pred + self.len_data + 3 dcf_model.set_cell( self.ws1, f"{dcf_model.letters[col+2]}{rowT}", dcf_model.letters[self.letter], font=dcf_model.red, ) if ws == 2: dcf_model.set_cell( self.ws2, f"{column}{row}", dcf_model.letters[self.letter], font=dcf_model.red, ) dcf_model.set_cell( self.ws3, f"A{self.letter+4}", dcf_model.letters[self.letter], font=dcf_model.red, ) dcf_model.set_cell(self.ws3, f"B{self.letter+4}", text) self.letter += 1
def test_set_cell(): workbook = openpyxl.Workbook() worksheet = workbook.active dcf_model.set_cell( ws=worksheet, cell="A1", text="some content", font=dcf_static.bold_font, fill=dcf_static.green_bg, border=dcf_static.thin_border_nr, alignment=dcf_static.center, num_form=dcf_static.fmt_acct, ) assert worksheet["A1"].value == "some content"
def create_header(self, ws: Workbook): for i in range(10): dcf_model.set_cell( ws, f"{dcf_model.letters[i]}1", border=dcf_model.thin_border ) ws.merge_cells("A1:J1") dcf_model.set_cell( ws, "A1", f"Gamestonk Terminal Analysis: {self.ticker.upper()}", font=Font(color="04cca8", size=20), border=dcf_model.thin_border, alignment=dcf_model.center, ) dcf_model.set_cell(ws, "A2", f"DCF for {self.ticker} generated on {self.now}")
def add_ratios(self): self.ws[4] = self.wb.create_sheet("Ratios") self.create_header(self.ws[4]) self.ws[4].column_dimensions["A"].width = 27 dcf_model.set_cell(self.ws[4], "B4", "Sector:") dcf_model.set_cell(self.ws[4], "C4", self.data["info"]["sector"]) similar_data = dcf_model.get_similar_dfs( self.info["ticker"], self.data["info"], self.info["max_similars"], self.info["no_filter"], ) similar_data.insert(0, [ self.info["ticker"], [self.df["BS"], self.df["IS"], self.df["CF"]] ]) row = 6 for val in similar_data: self.ws[4].merge_cells(f"A{row}:J{row}") # Header columns formatted as row index, text, format type titles = [ [0, val[0], 1], [1, "Liquidity Ratios", 2], [2, "Current Ratio", 0], [3, "Quick Ratio", 0], [5, "Activity Ratios", 2], [6, "AR Turnover", 0], [7, "Days Sales in AR", 0], [8, "Inventory Turnover", 0], [9, "Days in Inventory", 0], [10, "Average Payable Turnover", 0], [11, "Days of Payables Outstanding", 0], [12, "Cash Conversion Cycle", 0], [13, "Asset Turnover", 0], [15, "Profitability Ratios", 2], [16, "Profit Margin", 0], [17, "Return on Assets", 0], [18, "Return on Equity", 0], [19, "Return on Sales", 0], [20, "Gross Margin", 0], [21, "Operating Cash Flow Ratio", 0], [23, "Coverage Ratios", 2], [24, "Debt-to-Equity", 0], [25, "Total Debt Ratio", 0], [26, "Equity Multiplier", 0], [27, "Times Interest Earned", 0], [29, "Investor Ratios", 2], [30, "Earnings Per Share", 0], [31, "Price Earnings Ratio", 0], ] for j in titles: if j[2] == 1: align = dcf_static.center dcf_model.set_cell(self.ws[4], f"A{row+j[0]}", j[1], alignment=align) elif j[2] == 2: border = dcf_static.thin_border font = dcf_static.bold_font dcf_model.set_cell(self.ws[4], f"A{row+j[0]}", j[1], border=border, font=font) else: dcf_model.set_cell(self.ws[4], f"A{row+j[0]}", j[1]) for j in range(val[1][0].shape[1] - 1): lt = dcf_static.letters[j + 1] cace1 = dcf_model.get_value(val[1][0], "Cash & Cash Equivalents", j)[1] ar0, ar1 = dcf_model.get_value(val[1][0], "Receivables", j) inv0, inv1 = dcf_model.get_value(val[1][0], "Inventory", j) ca1 = dcf_model.get_value(val[1][0], "Total Current Assets", j)[1] ta0, ta1 = dcf_model.get_value(val[1][0], "Total Assets", j) ap0, ap1 = dcf_model.get_value(val[1][0], "Accounts Payable", j) cl1 = dcf_model.get_value(val[1][0], "Total Current Liabilities", j)[1] tl1 = dcf_model.get_value(val[1][0], "Total Liabilities", j)[1] te0, te1 = dcf_model.get_value(val[1][0], "Shareholders' Equity", j) sls1 = dcf_model.get_value(val[1][1], "Revenue", j)[1] cogs1 = dcf_model.get_value(val[1][1], "Cost of Revenue", j)[1] inte1 = dcf_model.get_value(val[1][1], "Interest Expense / Income", j)[1] tax1 = dcf_model.get_value(val[1][1], "Income Tax", j)[1] ni1 = dcf_model.get_value(val[1][1], "Net Income", j)[1] pdiv1 = dcf_model.get_value(val[1][1], "Preferred Dividends", j)[1] opcf1 = dcf_model.get_value(val[1][2], "Operating Cash Flow", j)[1] info, outstand = self.data["info"], float( self.data["info"]["sharesOutstanding"]) # Enter row offset, number to display, and format number rows = [ [1, int(val[1][0].columns[j + 1]), 1], [2, dcf_model.frac(ca1, cl1), 0], [3, dcf_model.frac(cace1 + ar1, cl1), 0], [6, dcf_model.frac(sls1, (ar0 + ar1) / 2), 0], [7, dcf_model.frac(ar1, sls1 / 365), 0], [8, dcf_model.frac(cogs1, (inv0 + inv1) / 2), 0], [9, dcf_model.frac(inv1, cogs1 / 365), 0], [10, dcf_model.frac(cogs1, (ap0 + ap1) / 2), 0], [11, dcf_model.frac(ap1, cogs1 / 365), 0], [ 12, "N/A" if sls1 == 0 or cogs1 == 0 else dcf_model.frac(ar1, sls1 / 365) + dcf_model.frac(inv1, cogs1 / 365) - dcf_model.frac(ap1, cogs1 / 365), 0, ], [13, dcf_model.frac(sls1, (ta0 + ta1) / 2), 0], [16, dcf_model.frac(ni1, sls1), 0], [17, dcf_model.frac(ni1, (ar0 + ar1) / 2), 0], [18, dcf_model.frac(ni1, (te0 + te1) / 2), 0], [19, dcf_model.frac(ni1 + inte1 + tax1, sls1), 0], [20, dcf_model.frac(sls1 - cogs1, sls1), 0], [21, dcf_model.frac(opcf1, cl1), 0], [24, dcf_model.frac(tl1, te1), 0], [25, dcf_model.frac(tl1, ta1), 0], [26, dcf_model.frac(ta1, te1), 0], [27, dcf_model.frac(ni1 + inte1 + tax1, inte1), 0], [ 30, dcf_model.frac((ni1 - pdiv1) * self.info["rounding"], outstand), 0, ], [ 31, dcf_model.frac( float(info["previousClose"]) * outstand, (ni1 - pdiv1) * self.info["rounding"], ), 0, ], ] for k in rows: if k[2] == 1: font = dcf_static.bold_font dcf_model.set_cell(self.ws[4], f"{lt}{row+k[0]}", k[1], font=font) else: dcf_model.set_cell(self.ws[4], f"{lt}{row+k[0]}", k[1]) row += 35
def get_linear(self, x_ind: str, y_ind: str, no_neg: bool = False): x_type = "IS" if x_ind in self.df["IS"].index else "BS" y_type = "IS" if y_ind in self.df["IS"].index else "BS" x_df = self.df["IS"] if x_type == "IS" else self.df["BS"] y_df = self.df["IS"] if y_type == "IS" else self.df["BS"] pre_x = (x_df.columns.to_numpy() if x_ind == "Date" else x_df.loc[x_ind].to_numpy()) vfunc = np.vectorize(dcf_model.string_float) pre_x = vfunc(pre_x) if x_ind == "Date": pre_x = pre_x - np.min(pre_x) x = pre_x.reshape((-1, 1)) pre_y = y_df.loc[y_ind].to_numpy() y = vfunc(pre_y) model = LinearRegression().fit(x, y) r_sq = model.score(x, y) r = abs(r_sq**0.5) if r > 0.9: strength = "very strong" elif r > 0.7: strength = "strong" elif r > 0.5: strength = "moderate" elif r > 0.3: strength = "weak" else: strength = "very weak" row1 = (y_df.index.get_loc(y_ind) + 1 + (self.starts["IS"] if y_type == "IS" else self.starts["BS"])) col = self.info["len_pred"] + self.info["len_data"] + 3 dcf_model.set_cell(self.ws[1], f"{dcf_static.letters[col]}{row1}", float(model.coef_)) dcf_model.set_cell(self.ws[1], f"{dcf_static.letters[col+1]}{row1}", float(model.intercept_)) dcf_model.set_cell( self.ws[1], f"{dcf_static.letters[col+2]}{row1}", dcf_static.letters[self.letter], font=dcf_static.red, ) dcf_model.set_cell( self.ws[3], f"A{self.letter+4}", dcf_static.letters[self.letter], font=dcf_static.red, ) dcf_model.set_cell( self.ws[3], f"B{self.letter+4}", (f"The correlation between {x_ind.lower()} and {y_ind.lower()}" f" is {strength} with a correlation coefficient of {r:.4f}."), ) col = self.info["len_data"] + 1 for i in range(self.info["len_pred"]): if x_ind == "Date": base = ( f"(({dcf_static.letters[col+i]}4-B4)*{dcf_static.letters[col+self.info['len_pred']+2]}" f"{row1})+{dcf_static.letters[col+self.info['len_pred']+3]}{row1}" ) else: row_n = (x_df.index.get_loc(x_ind) + 1 + self.starts["IS"] if x_type == "IS" else self.starts["BS"]) base = ( f"({dcf_static.letters[col+i]}{row_n}*{dcf_static.letters[col+self.info['len_pred']+2]}{row1})" f"+{dcf_static.letters[col+self.info['len_pred']+3]}{row1}" ) dcf_model.set_cell( self.ws[1], f"{dcf_static.letters[col+i]}{row1}", f"=max({base},0)" if no_neg else f"={base}", num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) self.letter += 1
def run_audit(self): start = 67 for i, value in enumerate(dcf_static.sum_rows): dcf_model.set_cell(self.ws[1], f"A{start + i}", value) self.ws[1].merge_cells(f"A{start-2}:K{start-2}") dcf_model.set_cell( self.ws[1], f"A{start - 2}", "Financial Statement Audit", font=Font(color="FF0000"), alignment=dcf_static.center, ) dcf_model.set_cell( self.ws[1], f"A{start - 1}", "Audit ensures data integrity. Numbers should be 0 (with slight rounding difference).", ) self.get_sum(start, "Revenue", [], ["Cost of Revenue", "Gross Profit"], True) self.get_sum( start + 1, "Gross Profit", [], [ "Selling, General & Admin", "Research & Development", "Other Operating Expenses", "Operating Income", ], True, ) self.get_sum( start + 2, "Operating Income", [], [ "Interest Expense / Income", "Other Expense / Income", "Income Tax", "Net Income", ], True, ) self.get_sum( start + 3, "Cash & Equivalents", ["Short-Term Investments"], ["Cash & Cash Equivalents"], True, ) self.get_sum( start + 4, "Cash & Cash Equivalents", ["Receivables", "Inventory", "Other Current Assets"], ["Total Current Assets"], True, ) self.get_sum( start + 5, "Property, Plant & Equipment", [ "Long-Term Investments", "Goodwill and Intangibles", "Other Long-Term Assets", ], ["Total Long-Term Assets"], True, ) self.get_sum( start + 6, "Total Current Assets", ["Total Long-Term Assets"], ["Total Assets"], True, ) self.get_sum( start + 7, "Accounts Payable", ["Deferred Revenue", "Current Debt", "Other Current Liabilities"], ["Total Current Liabilities"], True, ) self.get_sum( start + 8, "Long-Term Debt", ["Other Long-Term Liabilities"], ["Total Long-Term Liabilities"], True, ) self.get_sum( start + 9, "Total Current Liabilities", ["Total Long-Term Liabilities"], ["Total Liabilities"], True, ) self.get_sum( start + 10, "Common Stock", ["Retained Earnings", "Comprehensive Income"], ["Shareholders' Equity"], True, ) self.get_sum( start + 11, "Total Liabilities", ["Shareholders' Equity"], ["Total Liabilities and Equity"], True, ) self.get_sum( start + 12, "Net Income", [ "Depreciation & Amortization", "Share-Based Compensation", "Other Operating Activities", ], ["Operating Cash Flow"], True, ) self.get_sum( start + 13, "Capital Expenditures", [ "Acquisitions", "Change in Investments", "Other Investing Activities" ], ["Investing Cash Flow"], True, ) self.get_sum( start + 14, "Dividends Paid", [ "Share Issuance / Repurchase", "Debt Issued / Paid", "Other Financing Activities", ], ["Financing Cash Flow"], True, ) self.get_sum( start + 15, "Operating Cash Flow", ["Investing Cash Flow", "Financing Cash Flow"], ["Net Cash Flow"], True, )
def create_dcf(self): self.ws[2]["A5"] = "Net Income" self.ws[2]["A6"] = "Change in NWC" self.ws[2]["A7"] = "Change in Capex" self.ws[2]["A8"] = "Preferred Dividends" self.ws[2]["A9"] = "Free Cash Flows" r = 4 c1 = dcf_static.letters[self.info["len_pred"] + 3] c2 = dcf_static.letters[self.info["len_pred"] + 4] c3 = dcf_static.letters[self.info["len_pred"] + 5] for i in range(self.info["len_pred"]): j = 1 + i + self.info["len_data"] cols = dcf_static.letters dcf_model.set_cell( self.ws[2], f"{cols[1+i]}4", f"=Financials!{cols[j]}4", font=dcf_static.bold_font, ) dcf_model.set_cell( self.ws[2], f"{cols[1+i]}5", f"=Financials!{cols[j]}{self.title_to_row('Net Income')}", num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell( self.ws[2], f"{dcf_static.letters[1+i]}6", (f"=Financials!{cols[j]}{self.title_to_row('Total Current Assets')}" f"-Financials!{cols[j-1]}{self.title_to_row('Total Current Assets')}" f"-Financials!{cols[j]}{self.title_to_row('Total Current Liabilities')}" f"+Financials!{cols[j-1]}{self.title_to_row('Total Current Liabilities')}" ), num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell( self.ws[2], f"{dcf_static.letters[1+i]}7", (f"=Financials!{cols[j]}{self.title_to_row('Total Long-Term Assets')}" f"-Financials!{cols[j-1]}{self.title_to_row('Total Long-Term Assets')}" ), num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell( self.ws[2], f"{dcf_static.letters[1+i]}8", f"=Financials!{cols[j]}{self.title_to_row('Preferred Dividends')}", num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell( self.ws[2], f"{cols[1+i]}9", f"={cols[1+i]}5-{cols[1+i]}6-{cols[1+i]}7-{cols[1+i]}8", num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", font=dcf_static.bold_font, border=dcf_static.thin_border_top, ) dcf_model.set_cell( self.ws[2], f"{cols[1+self.info['len_pred']]}9", f"=({cols[self.info['len_pred']]}9*(1+{c2}" f"{r+15}))/({c2}{r+11}-{c2}{r+15})", num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell( self.ws[2], f"{c1}{r-2}", "Note: We do not allow r values to go below 0.5%.", font=dcf_static.red, ) self.ws[2].merge_cells(f"{c1}{r}:{c2}{r}") for x in [c1, c2]: dcf_model.set_cell(self.ws[2], f"{x}{r}", border=dcf_static.thin_border_bottom) dcf_model.set_cell( self.ws[2], f"{c1}{r}", "Discount Rate", alignment=dcf_static.center, border=dcf_static.thin_border_bottom, ) # CAPM dcf_model.set_cell(self.ws[2], f"{c1}{r+1}", "Risk Free Rate") dcf_model.set_cell( self.ws[2], f"{c2}{r+1}", float(self.data["t_bill"]) / 100, num_form=FORMAT_PERCENTAGE_00, ) self.custom_exp(r + 1, "Pulled from US Treasurey.", 2, f"{c3}") dcf_model.set_cell(self.ws[2], f"{c1}{r+2}", "Market Rate") dcf_model.set_cell(self.ws[2], f"{c2}{r+2}", 0.08, num_form=FORMAT_PERCENTAGE_00) self.custom_exp(r + 2, "Average return of the S&P 500 is 8% [Investopedia]", 2, f"{c3}") dcf_model.set_cell(self.ws[2], f"{c1}{r+3}", "Beta") if self.data["info"]["beta"] is None: dcf_model.set_cell(self.ws[2], f"{c2}{r+3}", float(1)) self.custom_exp(r + 3, "Warning: Beta not found. Assumed a beta of one.", 2, f"{c3}") self.data["info"]["beta"] = 1 else: dcf_model.set_cell(self.ws[2], f"{c2}{r+3}", float(self.data["info"]["beta"])) self.custom_exp(r + 3, "Beta from yahoo finance", 2, f"{c3}") dcf_model.set_cell(self.ws[2], f"{c1}{r+4}", "r (CAPM)") dcf_model.set_cell( self.ws[2], f"{c2}{r+4}", f"=max((({c2}{r+2}-{c2}{r+1})*{c2}{r+3})+{c2}{r+1},0.005)", num_form=FORMAT_PERCENTAGE_00, border=dcf_static.thin_border_top, font=dcf_static.bold_font, ) # Fama French dcf_model.set_cell(self.ws[2], f"{c1}{r+7}", "Fama French") dcf_model.set_cell( self.ws[2], f"{c2}{r+7}", f"=max({self.data['r_ff']},0.005)", num_form=FORMAT_PERCENTAGE_00, ) self.custom_exp( r + 7, ("Calculated using the Fama and French Three-Factor model. For more" "information visit https://www.investopedia.com/terms/f/famaandfrenchthreefactormodel.asp." ), 2, f"{c3}", ) # Decide for x in [c1, c2]: dcf_model.set_cell(self.ws[2], f"{x}{r+9}", border=dcf_static.thin_border_bottom) self.ws[2].merge_cells(f"{c1}{r+9}:{c2}{r+9}") dcf_model.set_cell( self.ws[2], f"{c1}{r+9}", "Choose model", border=dcf_static.thin_border_bottom, alignment=dcf_static.center, num_form=FORMAT_PERCENTAGE_00, ) dcf_model.set_cell(self.ws[2], f"{c1}{r+10}", "Model") dcf_model.set_cell(self.ws[2], f"{c2}{r+10}", "Fama French") dcf_model.set_cell(self.ws[2], f"{c3}{r+10}", "Type 'Fama French' or 'CAPM'") dcf_model.set_cell(self.ws[2], f"{c1}{r+11}", "r") dcf_model.set_cell( self.ws[2], f"{c2}{r+11}", f'=if({c2}{r+10}="Fama French",{c2}{r+7},if({c2}{r+10}="CAPM",{c2}{r+4},"Invalid Selection"))', num_form=FORMAT_PERCENTAGE_00, ) dcf_model.set_cell(self.ws[2], f"{c1}{r+15}", "Long Term Growth") dcf_model.set_cell( self.ws[2], f"{c2}{r+15}", f"=min(0.04,{c2}{r+11}*0.9)", num_form=FORMAT_PERCENTAGE_00, ) dcf_model.set_cell(self.ws[2], "A11", "Value from Operations") dcf_model.set_cell( self.ws[2], "B11", f"=NPV({c2}{r+11},B9:{dcf_static.letters[self.info['len_pred']+1]}9)", num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell(self.ws[2], "A12", "Cash and Cash Equivalents") dcf_model.set_cell( self.ws[2], "B12", f"=financials!{dcf_static.letters[self.info['len_data']]}{self.title_to_row('Cash & Cash Equivalents')}", num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell(self.ws[2], "A13", "Intrinsic Value (sum)") dcf_model.set_cell( self.ws[2], "B13", "=B11+B12", num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell(self.ws[2], "A14", "Debt Obligations") dcf_model.set_cell( self.ws[2], "B14", f"=financials!{dcf_static.letters[self.info['len_data']]}{self.title_to_row('Total Long-Term Liabilities')}", num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell(self.ws[2], "A15", "Firm value without debt") dcf_model.set_cell( self.ws[2], "B15", (f"=max(B13-B14," f"Financials!{dcf_static.letters[self.info['len_data']]}{self.title_to_row('Total Assets')}" f"-Financials!{dcf_static.letters[self.info['len_data']]}{self.title_to_row('Total Liabilities')})" ), num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell( self.ws[2], "C15", (f"=if((B13-B14)>" f"(Financials!{dcf_static.letters[self.info['len_data']]}{self.title_to_row('Total Assets')}" f"-Financials!{dcf_static.letters[self.info['len_data']]}{self.title_to_row('Total Liabilities')})," '"","Note: Total assets minus total liabilities exceeds projected firm value without debt.' ' Value shown is total assets minus total liabilities.")'), font=dcf_static.red, ) dcf_model.set_cell(self.ws[2], "A16", "Shares Outstanding") dcf_model.set_cell(self.ws[2], "B16", int(self.data["info"]["sharesOutstanding"])) dcf_model.set_cell(self.ws[2], "A17", "Shares Price") dcf_model.set_cell( self.ws[2], "B17", f"=(B15*{self.info['rounding']})/B16", num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell(self.ws[2], "A18", "Actual Price") dcf_model.set_cell(self.ws[2], "B18", float(self.data["info"]["regularMarketPrice"]))
def add_estimates(self): last_year = self.df["BS"].columns[-1] # Replace with columns in DF col = self.info["len_data"] + 1 for i in range(self.info["len_pred"]): dcf_model.set_cell( self.ws[1], f"{dcf_static.letters[col+i]}4", int(last_year) + 1 + i, font=dcf_static.bold_font, ) for i in range(41): col = self.info["len_pred"] + self.info["len_data"] + 3 dcf_model.set_cell( self.ws[1], f"{dcf_static.letters[col]}{3+i}", fill=dcf_static.green_bg, border=dcf_static.thin_border_nr, ) dcf_model.set_cell( self.ws[1], f"{dcf_static.letters[col+1]}{3+i}", fill=dcf_static.green_bg, border=dcf_static.thin_border_nl, ) dcf_model.set_cell( self.ws[1], f"{dcf_static.letters[col]}3", "Linear model", alignment=dcf_static.center, ) self.ws[1].merge_cells( f"{dcf_static.letters[col]}3:{dcf_static.letters[col+1]}3") dcf_model.set_cell(self.ws[1], f"{dcf_static.letters[col]}4", "m") dcf_model.set_cell(self.ws[1], f"{dcf_static.letters[col+1]}4", "b") self.get_linear("Date", "Revenue") self.get_linear("Revenue", "Cost of Revenue") self.get_sum("Gross Profit", "Revenue", [], ["Cost of Revenue"]) self.get_linear("Revenue", "Selling, General & Admin", True) self.get_linear("Revenue", "Research & Development", True) self.get_linear("Revenue", "Other Operating Expenses") self.get_sum( "Operating Income", "Gross Profit", [], [ "Selling, General & Admin", "Research & Development", "Other Operating Expenses", ], ) self.get_linear("Revenue", "Preferred Dividends") self.get_linear("Revenue", "Interest Expense / Income") self.get_linear("Revenue", "Other Expense / Income") self.get_linear("Operating Income", "Income Tax") self.get_sum( "Net Income", "Operating Income", [], [ "Interest Expense / Income", "Other Expense / Income", "Income Tax" ], ) self.custom_exp( "Preferred Dividends", "Preferred Dividends are not important in a DCF so we do not attempt to predict them.", ) self.get_linear("Revenue", "Cash & Equivalents", True) self.get_linear("Revenue", "Short-Term Investments", True) self.get_sum( "Cash & Cash Equivalents", "Cash & Equivalents", ["Short-Term Investments"], [], ) self.get_linear("Revenue", "Receivables", True) self.get_linear("Revenue", "Inventory", True) self.get_linear("Revenue", "Other Current Assets") self.get_sum( "Total Current Assets", "Cash & Cash Equivalents", ["Receivables", "Inventory", "Other Current Assets"], [], ) self.get_linear("Revenue", "Property, Plant & Equipment", True) self.get_linear("Revenue", "Long-Term Investments", True) self.get_linear("Revenue", "Goodwill and Intangibles", True) self.get_linear("Revenue", "Other Long-Term Assets") self.get_sum( "Total Long-Term Assets", "Property, Plant & Equipment", [ "Long-Term Investments", "Goodwill and Intangibles", "Other Long-Term Assets", ], [], ) self.get_sum("Total Assets", "Total Current Assets", ["Total Long-Term Assets"], []) self.get_linear("Revenue", "Accounts Payable") self.get_linear("Revenue", "Deferred Revenue") self.get_linear("Revenue", "Current Debt") self.get_linear("Revenue", "Other Current Liabilities") self.get_sum( "Total Current Liabilities", "Accounts Payable", ["Deferred Revenue", "Current Debt", "Other Current Liabilities"], [], ) self.get_sum( "Long-Term Debt", "Total Assets", [], [ "Total Current Liabilities", "Other Long-Term Liabilities", "Shareholders' Equity", ], text= ("This is the plug. For more information on plugs visit https://corporatefina" "nceinstitute.com/resources/questions/model-questions/financial-modeling-plug/" ), ) # This is the plug self.get_linear("Revenue", "Other Long-Term Liabilities") self.get_sum( "Total Long-Term Liabilities", "Long-Term Debt", ["Other Long-Term Liabilities"], [], ) self.get_sum( "Total Liabilities", "Total Current Liabilities", ["Total Long-Term Liabilities"], [], ) self.get_linear("Revenue", "Common Stock") col = self.info["len_data"] + 1 rer = self.title_to_row("Retained Earnings") nir = self.title_to_row("Net Income") for i in range(self.info["len_pred"]): dcf_model.set_cell( self.ws[1], f"{dcf_static.letters[col+i]}{rer}", f"={dcf_static.letters[col+i]}{nir}+{dcf_static.letters[col+i-1]}{rer}", num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) self.get_linear("Revenue", "Comprehensive Income") self.get_sum( "Shareholders' Equity", "Common Stock", ["Retained Earnings", "Comprehensive Income"], [], ) self.get_sum( "Total Liabilities and Equity", "Total Liabilities", ["Shareholders' Equity"], [], ) dcf_model.set_cell( self.ws[1], "A65", ("Warning: Stock Analysis does not have all of the cash flow items included. Operating" ", Financing, and Investing Cash Flows may not add up to total cash flows." ), font=dcf_static.red, )
def get_data(self, statement: str, row: int, header: bool): URL = f"https://stockanalysis.com/stocks/{self.ticker}/financials/" if statement == "BS": URL += "balance-sheet/" title = "Balance Sheet" ignores = dcf_model.non_gaap_bs if statement == "CF": URL += "cash-flow-statement/" title = "Cash Flows" ignores = dcf_model.non_gaap_cf if statement == "IS": title = "Income Statement" ignores = dcf_model.non_gaap_is r = requests.get(URL, headers=dcf_model.headers) if "404 - Page Not Found" in r.text: raise ValueError( "The ticker given is not in the stock analysis website.") soup = BeautifulSoup(r.content, "html.parser") table = soup.find( "table", attrs={"class": "FinancialTable_table_financial__1RhYq"}) head = table.find("thead") columns = head.find_all("th") if self.years == []: self.years = [x.get_text().strip() for x in columns] self.len_data = len(self.years) - 1 if self.rounding == 0: phrase = soup.find("div", attrs={ "class": "text-sm pb-1 text-gray-600" }).get_text() if "thousand" in phrase: self.rounding = 1_000 elif "millions" in phrase: self.rounding = 1_000_000 elif "billions" in phrase: self.rounding = 1_000_000_000 else: raise ValueError( "Stock Analysis did not specify a proper rounding amount") body = table.find("tbody") rows = body.find_all("tr") all_data = [[x.get_text().strip() for x in y.find_all("td")] for y in rows] df = pd.DataFrame(data=all_data) df = df.set_index(0) n = df.shape[1] - self.len_data if n > 0: df = df.iloc[:, :-n] df.columns = self.years[1:] for ignore in ignores: if ignore in df.index: df = df.drop([ignore]) df = df[df.columns[::-1]] self.ws1[f"A{row}"] = title self.ws1[f"A{row}"].font = dcf_model.bold_font # Refactor in the future if statement == "IS": if "Revenue" in df.index: blank_list = ["0" for x in df.loc["Revenue"].to_list()] else: raise ValueError("Dataframe does not have key information.") for i, value in enumerate(dcf_model.gaap_is[1:]): df = dcf_model.insert_row(dcf_model.gaap_is[i + 1], dcf_model.gaap_is[i], df, blank_list) if statement == "BS": if "Cash & Equivalents" in df.index: blank_list = [ "0" for x in df.loc["Cash & Equivalents"].to_list() ] else: raise ValueError("Dataframe does not have key information.") for i, value in enumerate(dcf_model.gaap_bs[1:]): df = dcf_model.insert_row(dcf_model.gaap_bs[i + 1], dcf_model.gaap_bs[i], df, blank_list) if statement == "CF": if "Net Income" in df.index: blank_list = ["0" for x in df.loc["Net Income"].to_list()] else: raise ValueError("Dataframe does not have key information.") for i, value in enumerate(dcf_model.gaap_cf[1:]): df = dcf_model.insert_row(dcf_model.gaap_cf[i + 1], dcf_model.gaap_cf[i], df, blank_list) rowI = row + 1 names = df.index.values.tolist() for name in names: self.ws1[f"A{rowI}"] = name if name in dcf_model.sum_rows: length = self.len_data + (self.len_pred if statement != "CF" else 0) for i in range(length): if statement == "CF" and name == "Net Income": pass else: self.ws1[ f"{dcf_model.letters[i+1]}{rowI}"].font = dcf_model.bold_font self.ws1[ f"{dcf_model.letters[i+1]}{rowI}"].border = dcf_model.thin_border_top rowI += 1 column = 1 for key, value in df.iteritems(): rowI = row if header: dcf_model.set_cell( self.ws1, f"{dcf_model.letters[column]}{rowI}", float(key), font=dcf_model.bold_font, ) for item in value: rowI += 1 m = 0 if item is None else float(item.replace(",", "")) dcf_model.set_cell( self.ws1, f"{dcf_model.letters[column]}{rowI}", m, num_form=dcf_model.fmt_acct, ) column += 1 return df
def create_dcf(self): self.ws2["A5"] = "Net Income" self.ws2["A6"] = "Change in NWC" self.ws2["A7"] = "Change in Capex" self.ws2["A8"] = "Preferred Dividends" self.ws2["A9"] = "Free Cash Flows" r = 4 c1 = dcf_model.letters[self.len_pred + 3] c2 = dcf_model.letters[self.len_pred + 4] c3 = dcf_model.letters[self.len_pred + 5] for i in range(self.len_pred): j = 1 + i + self.len_data cols = dcf_model.letters dcf_model.set_cell( self.ws2, f"{cols[1+i]}4", f"=Financials!{cols[j]}4", font=dcf_model.bold_font, ) dcf_model.set_cell( self.ws2, f"{cols[1+i]}5", f"=Financials!{cols[j]}{self.title_to_row('Net Income')}", num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell( self.ws2, f"{dcf_model.letters[1+i]}6", ( f"=Financials!{cols[j]}{self.title_to_row('Total Current Assets')}" f"-Financials!{cols[j-1]}{self.title_to_row('Total Current Assets')}" f"-Financials!{cols[j]}{self.title_to_row('Total Current Liabilities')}" f"+Financials!{cols[j-1]}{self.title_to_row('Total Current Liabilities')}" ), num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell( self.ws2, f"{dcf_model.letters[1+i]}7", ( f"=Financials!{cols[j]}{self.title_to_row('Total Long-Term Assets')}" f"-Financials!{cols[j-1]}{self.title_to_row('Total Long-Term Assets')}" ), num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell( self.ws2, f"{dcf_model.letters[1+i]}8", f"=Financials!{cols[j]}{self.title_to_row('Preferred Dividends')}", num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell( self.ws2, f"{cols[1+i]}9", f"={cols[1+i]}5-{cols[1+i]}6-{cols[1+i]}7-{cols[1+i]}8", num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", font=dcf_model.bold_font, border=dcf_model.thin_border_top, ) dcf_model.set_cell( self.ws2, f"{cols[1+self.len_pred]}9", f"=({cols[self.len_pred]}9*(1+{c2}" f"{r+15}))/({c2}{r+11}-{c2}{r+15})", num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell( self.ws2, f"{c1}{r-2}", "Note: We do not allow r values to go below 0.5%.", font=dcf_model.red, ) self.ws2.merge_cells(f"{c1}{r}:{c2}{r}") for x in [c1, c2]: dcf_model.set_cell(self.ws2, f"{x}{r}", border=dcf_model.thin_border_bottom) dcf_model.set_cell( self.ws2, f"{c1}{r}", "Discount Rate", alignment=dcf_model.center, border=dcf_model.thin_border_bottom, ) # CAPM dcf_model.set_cell(self.ws2, f"{c1}{r+1}", "Risk Free Rate") dcf_model.set_cell( self.ws2, f"{c2}{r+1}", float(self.t_bill) / 100, num_form=FORMAT_PERCENTAGE_00, ) self.custom_exp(r + 1, "Pulled from US Treasurey.", 2, f"{c3}") dcf_model.set_cell(self.ws2, f"{c1}{r+2}", "Market Rate") dcf_model.set_cell(self.ws2, f"{c2}{r+2}", 0.08, num_form=FORMAT_PERCENTAGE_00) self.custom_exp( r + 2, "Average return of the S&P 500 is 8% [Investopedia]", 2, f"{c3}" ) dcf_model.set_cell(self.ws2, f"{c1}{r+3}", "Beta") if self.info["beta"] is None: dcf_model.set_cell(self.ws2, f"{c2}{r+3}", float(1)) self.custom_exp( r + 3, "Warning: Beta not found. Assumed a beta of one.", 2, f"{c3}" ) self.info["beta"] = 1 else: dcf_model.set_cell(self.ws2, f"{c2}{r+3}", float(self.info["beta"])) self.custom_exp(r + 3, "Beta from yahoo finance", 2, f"{c3}") dcf_model.set_cell(self.ws2, f"{c1}{r+4}", "r (CAPM)") dcf_model.set_cell( self.ws2, f"{c2}{r+4}", f"=max((({c2}{r+2}-{c2}{r+1})*{c2}{r+3})+{c2}{r+1},0.005)", num_form=FORMAT_PERCENTAGE_00, border=dcf_model.thin_border_top, font=dcf_model.bold_font, ) # Fama French dcf_model.set_cell(self.ws2, f"{c1}{r+7}", "Fama French") dcf_model.set_cell( self.ws2, f"{c2}{r+7}", f"=max({self.r_ff},0.005)", num_form=FORMAT_PERCENTAGE_00, ) self.custom_exp( r + 7, ( "Calculated using the Fama and French Three-Factor model. For more" "information visit https://www.investopedia.com/terms/f/famaandfrenchthreefactormodel.asp." ), 2, f"{c3}", ) # Decide for x in [c1, c2]: dcf_model.set_cell( self.ws2, f"{x}{r+9}", border=dcf_model.thin_border_bottom ) self.ws2.merge_cells(f"{c1}{r+9}:{c2}{r+9}") dcf_model.set_cell( self.ws2, f"{c1}{r+9}", "Choose model", border=dcf_model.thin_border_bottom, alignment=dcf_model.center, num_form=FORMAT_PERCENTAGE_00, ) dcf_model.set_cell(self.ws2, f"{c1}{r+10}", "Model") dcf_model.set_cell(self.ws2, f"{c2}{r+10}", "Fama French") dcf_model.set_cell(self.ws2, f"{c3}{r+10}", "Type 'Fama French' or 'CAPM'") dcf_model.set_cell(self.ws2, f"{c1}{r+11}", "r") dcf_model.set_cell( self.ws2, f"{c2}{r+11}", f'=if({c2}{r+10}="Fama French",{c2}{r+7},if({c2}{r+10}="CAPM",{c2}{r+4},"Invalid Selection"))', num_form=FORMAT_PERCENTAGE_00, ) dcf_model.set_cell(self.ws2, f"{c1}{r+15}", "Long Term Growth") dcf_model.set_cell( self.ws2, f"{c2}{r+15}", f"=min(0.04,{c2}{r+11}*0.9)", num_form=FORMAT_PERCENTAGE_00, ) dcf_model.set_cell(self.ws2, "A11", "Value from Operations") dcf_model.set_cell( self.ws2, "B11", f"=NPV({c2}{r+11},B9:{dcf_model.letters[self.len_pred+1]}9)", num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell(self.ws2, "A12", "Cash and Cash Equivalents") dcf_model.set_cell( self.ws2, "B12", f"=financials!{dcf_model.letters[self.len_data]}{self.title_to_row('Cash & Cash Equivalents')}", num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell(self.ws2, "A13", "Intrinsic Value (sum)") dcf_model.set_cell( self.ws2, "B13", "=B11+B12", num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell(self.ws2, "A14", "Debt Obligations") dcf_model.set_cell( self.ws2, "B14", f"=financials!{dcf_model.letters[self.len_data]}{self.title_to_row('Total Long-Term Liabilities')}", num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell(self.ws2, "A15", "Firm value without debt") dcf_model.set_cell( self.ws2, "B15", ( f"=max(B13-B14," f"Financials!{dcf_model.letters[self.len_data]}{self.title_to_row('Total Assets')}" f"-Financials!{dcf_model.letters[self.len_data]}{self.title_to_row('Total Liabilities')})" ), num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell( self.ws2, "C15", ( f"=if((B13-B14)>" f"(Financials!{dcf_model.letters[self.len_data]}{self.title_to_row('Total Assets')}" f"-Financials!{dcf_model.letters[self.len_data]}{self.title_to_row('Total Liabilities')})," '"","Note: Total assets minus total liabilities exceeds projected firm value without debt.' ' Value shown is total assets minus total liabilities.")' ), font=dcf_model.red, ) dcf_model.set_cell(self.ws2, "A16", "Shares Outstanding") dcf_model.set_cell(self.ws2, "B16", int(self.info["sharesOutstanding"])) dcf_model.set_cell(self.ws2, "A17", "Shares Price") dcf_model.set_cell( self.ws2, "B17", f"=(B15*{self.rounding})/B16", num_form="[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00", ) dcf_model.set_cell(self.ws2, "A18", "Actual Price") dcf_model.set_cell(self.ws2, "B18", float(self.info["regularMarketPrice"])) # Create ratios page self.ws4.column_dimensions["A"].width = 27 dcf_model.set_cell(self.ws4, "B4", "Sector:") dcf_model.set_cell(self.ws4, "C4", self.info["sector"]) self.get_sister_dfs() self.sister_data.insert(0, [self.ticker, [self.df_bs, self.df_is, self.df_cf]]) row = 6 for i in self.sister_data: self.ws4.merge_cells(f"A{row}:J{row}") dcf_model.set_cell(self.ws4, f"A{row}", i[0], alignment=dcf_model.center) dcf_model.set_cell( self.ws4, f"A{row+1}", "Liquidity Ratios", border=dcf_model.thin_border, font=dcf_model.bold_font, ) dcf_model.set_cell(self.ws4, f"A{row+2}", "Current Ratio") dcf_model.set_cell(self.ws4, f"A{row+3}", "Quick Ratio") dcf_model.set_cell( self.ws4, f"A{row+5}", "Activity Ratios", border=dcf_model.thin_border, font=dcf_model.bold_font, ) dcf_model.set_cell(self.ws4, f"A{row+6}", "AR Turnover") dcf_model.set_cell(self.ws4, f"A{row+7}", "Days Sales in AR") dcf_model.set_cell(self.ws4, f"A{row+8}", "Inventory Turnover") dcf_model.set_cell(self.ws4, f"A{row+9}", "Days in Inventory") dcf_model.set_cell(self.ws4, f"A{row+10}", "Average Payable Turnover") dcf_model.set_cell(self.ws4, f"A{row+11}", "Days of Payables Outstanding") dcf_model.set_cell(self.ws4, f"A{row+12}", "Cash Conversion Cycle") dcf_model.set_cell(self.ws4, f"A{row+13}", "Asset Turnover") dcf_model.set_cell( self.ws4, f"A{row+15}", "Profitability Ratios", border=dcf_model.thin_border, font=dcf_model.bold_font, ) dcf_model.set_cell(self.ws4, f"A{row+16}", "Profit Margin") dcf_model.set_cell(self.ws4, f"A{row+17}", "Return on Assets") dcf_model.set_cell(self.ws4, f"A{row+18}", "Return on Equity") dcf_model.set_cell(self.ws4, f"A{row+19}", "Return on Sales") dcf_model.set_cell(self.ws4, f"A{row+20}", "Gross Margin") dcf_model.set_cell(self.ws4, f"A{row+21}", "Operating Cash Flow Ratio") dcf_model.set_cell( self.ws4, f"A{row+23}", "Coverage Ratios", border=dcf_model.thin_border, font=dcf_model.bold_font, ) dcf_model.set_cell(self.ws4, f"A{row+24}", "Debt-to-Equity") dcf_model.set_cell(self.ws4, f"A{row+25}", "Total Debt Ratio") dcf_model.set_cell(self.ws4, f"A{row+26}", "Equity Multiplier") dcf_model.set_cell(self.ws4, f"A{row+27}", "Times Interest Earned") dcf_model.set_cell( self.ws4, f"A{row+29}", "Investor Ratios", border=dcf_model.thin_border, font=dcf_model.bold_font, ) dcf_model.set_cell(self.ws4, f"A{row+30}", "Earnings Per Share") dcf_model.set_cell(self.ws4, f"A{row+31}", "Price Earnings Ratio") for j in range(len(self.df_bs.columns) - 1): lt = dcf_model.letters[j + 1] cace1 = float( i[1][0] .at["Cash & Cash Equivalents", i[1][0].columns[j + 1]] .replace(",", "") .replace("-", "-0") ) ar0 = float( i[1][0] .at["Receivables", i[1][0].columns[j]] .replace(",", "") .replace("-", "-0") ) ar1 = float( i[1][0] .at["Receivables", i[1][0].columns[j + 1]] .replace(",", "") .replace("-", "-0") ) inv0 = float( i[1][0] .at["Inventory", i[1][0].columns[j]] .replace(",", "") .replace("-", "-0") ) inv1 = float( i[1][0] .at["Inventory", i[1][0].columns[j + 1]] .replace(",", "") .replace("-", "-0") ) ca1 = float( i[1][0] .at["Total Current Assets", i[1][0].columns[j + 1]] .replace(",", "") .replace("-", "-0") ) ta0 = float( i[1][0] .at["Total Assets", i[1][0].columns[j]] .replace(",", "") .replace("-", "-0") ) ta1 = float( i[1][0] .at["Total Assets", i[1][0].columns[j + 1]] .replace(",", "") .replace("-", "-0") ) ap0 = float( i[1][0] .at["Accounts Payable", i[1][0].columns[j]] .replace(",", "") .replace("-", "-0") ) ap1 = float( i[1][0] .at["Accounts Payable", i[1][0].columns[j + 1]] .replace(",", "") .replace("-", "-0") ) cl1 = float( i[1][0] .at["Total Current Liabilities", i[1][0].columns[j + 1]] .replace(",", "") .replace("-", "-0") ) tl1 = float( i[1][0] .at["Total Liabilities", i[1][0].columns[j + 1]] .replace(",", "") .replace("-", "-0") ) te0 = float( i[1][0] .at["Shareholders' Equity", i[1][0].columns[j]] .replace(",", "") .replace("-", "-0") ) te1 = float( i[1][0] .at["Shareholders' Equity", i[1][0].columns[j + 1]] .replace(",", "") .replace("-", "-0") ) sls1 = float( i[1][1] .at["Revenue", i[1][1].columns[j + 1]] .replace(",", "") .replace("-", "-0") ) cogs1 = float( i[1][1] .at["Cost of Revenue", i[1][1].columns[j + 1]] .replace(",", "") .replace("-", "-0") ) inte1 = float( i[1][1] .at["Interest Expense / Income", i[1][1].columns[j + 1]] .replace(",", "") .replace("-", "-0") ) tax1 = float( i[1][1] .at["Income Tax", i[1][1].columns[j + 1]] .replace(",", "") .replace("-", "-0") ) ni1 = float( i[1][1] .at["Net Income", i[1][1].columns[j + 1]] .replace(",", "") .replace("-", "-0") ) pdiv1 = float( i[1][1] .at["Preferred Dividends", i[1][0].columns[j + 1]] .replace(",", "") .replace("-", "-0") ) opcf1 = float( i[1][2] .at["Operating Cash Flow", i[1][2].columns[j + 1]] .replace(",", "") .replace("-", "-0") ) dcf_model.set_cell( self.ws4, f"{lt}{row+1}", int(i[1][0].columns[j + 1]), font=dcf_model.bold_font, ) dcf_model.set_cell( self.ws4, f"{lt}{row+2}", "N/A" if cl1 == 0 else ca1 / cl1 ) dcf_model.set_cell( self.ws4, f"{lt}{row+3}", "N/A" if cl1 == 0 else (cace1 + ar1) / cl1 ) dcf_model.set_cell( self.ws4, f"{lt}{row+6}", "N/A" if ar0 + ar1 == 0 else sls1 / ((ar0 + ar1) / 2), ) dcf_model.set_cell( self.ws4, f"{lt}{row+7}", "N/A" if sls1 == 0 else ar1 / (sls1 / 365) ) dcf_model.set_cell( self.ws4, f"{lt}{row+8}", "N/A" if inv0 + inv1 == 0 else cogs1 / ((inv0 + inv1) / 2), ) dcf_model.set_cell( self.ws4, f"{lt}{row+9}", "N/A" if cogs1 == 0 else inv1 / (cogs1 / 365), ) dcf_model.set_cell( self.ws4, f"{lt}{row+10}", "N/A" if ap0 + ap1 == 0 else cogs1 / ((ap0 + ap1) / 2), ) dcf_model.set_cell( self.ws4, f"{lt}{row+11}", "N/A" if cogs1 == 0 else ap1 / (cogs1 / 365), ) dcf_model.set_cell( self.ws4, f"{lt}{row+12}", "N/A" if cogs1 == 0 else (ar1 / (sls1 / 365)) + (inv1 / (cogs1 / 365)) - (ap1 / (cogs1 / 365)), ) dcf_model.set_cell( self.ws4, f"{lt}{row+13}", "N/A" if ta0 + ta1 == 0 else sls1 / ((ta0 + ta1) / 2), ) dcf_model.set_cell( self.ws4, f"{lt}{row+16}", "N/A" if sls1 == 0 else ni1 / sls1 ) dcf_model.set_cell( self.ws4, f"{lt}{row+17}", "N/A" if ar0 + ar1 == 0 else ni1 / ((ar0 + ar1) / 2), ) dcf_model.set_cell( self.ws4, f"{lt}{row+18}", "N/A" if te0 + te1 == 0 else ni1 / ((te0 + te1) / 2), ) dcf_model.set_cell( self.ws4, f"{lt}{row+19}", "N/A" if sls1 == 0 else (ni1 + inte1 + tax1) / sls1, ) dcf_model.set_cell( self.ws4, f"{lt}{row+20}", "N/A" if sls1 == 0 else (sls1 - cogs1) / sls1, ) dcf_model.set_cell( self.ws4, f"{lt}{row+21}", "N/A" if cl1 == 0 else opcf1 / cl1 ) dcf_model.set_cell( self.ws4, f"{lt}{row+24}", "N/A" if te1 == 0 else tl1 / te1 ) dcf_model.set_cell( self.ws4, f"{lt}{row+25}", "N/A" if ta1 == 0 else tl1 / ta1 ) dcf_model.set_cell( self.ws4, f"{lt}{row+26}", "N/A" if te1 == 0 else ta1 / te1 ) dcf_model.set_cell( self.ws4, f"{lt}{row+27}", "N/A" if inte1 == 0 else (ni1 + inte1 + tax1) / inte1, ) dcf_model.set_cell( self.ws4, f"{lt}{row+30}", (ni1 - pdiv1) / float(self.info["sharesOutstanding"]), ) dcf_model.set_cell( self.ws4, f"{lt}{row+31}", float(self.info["previousClose"]) / ((ni1 - pdiv1) / float(self.info["sharesOutstanding"])), ) row += 35