Learning by Doing Series — Topic 15: Investment Valuation Ratios
Calculate price-to-book, price-to-earnings, price-to-sales, dividend yield, enterprise value multiple, and PEG ratio.
The main output of this topic is Fact_Investment_Valuation_Ratios.csv.
Calculate price-to-book, price-to-earnings, price-to-sales, dividend yield, enterprise value multiple, and PEG ratio.
El archivo principal de salida de este tópico es Fact_Investment_Valuation_Ratios.csv.
This exercise uses synthetic training data only. Do not run scripts in production, real corporate folders, financial databases, or work environments without formal authorization. Validate backups, sandbox testing, least-privilege permissions, change-control approval, audit requirements, and organizational cybersecurity protocols before automating any real financial process.
Este ejercicio usa data sintética. No ejecutes scripts en producción, carpetas reales, bases financieras corporativas o ambientes de trabajo sin autorización formal. Valida respaldos, pruebas en sandbox, permisos mínimos, control de cambios, auditoría y protocolos de ciberseguridad antes de automatizar cualquier proceso financiero real.
The training has already created dimensions and core financial data. This topic moves one step closer to an executive BI model by creating a clean analytical file for Investment Valuation.
El training ya creó dimensiones y data financiera base. Este tópico avanza hacia el modelo ejecutivo de BI creando un archivo analítico limpio para Investment Valuation.
from pathlib import Path
import pandas as pd
# ============================================================
# Financial Ratios Analysis in BI
# Topic 15 - Investment Valuation Ratios
# Corrected version: uses TotalDebt from Fact_Market_Data.csv
# ============================================================
# ------------------------------------------------------------
# Step 1 - Set project folder
# ------------------------------------------------------------
base_path = Path("financial_ratios_bi_training")
if not base_path.exists():
raise FileNotFoundError(
"The project folder does not exist. Please run Topic 1 first."
)
market_data_path = base_path / "Fact_Market_Data.csv"
income_statement_path = base_path / "Fact_Income_Statement.csv"
balance_sheet_path = base_path / "Fact_Balance_Sheet.csv"
dim_company_path = base_path / "Dim_Company.csv"
dim_period_path = base_path / "Dim_Period.csv"
required_files = [
market_data_path,
income_statement_path,
balance_sheet_path,
dim_company_path,
dim_period_path
]
for file_path in required_files:
if not file_path.exists():
raise FileNotFoundError(
f"Required file not found: {file_path.name}. "
"Please run the previous topics first."
)
print(f"Project folder found: {base_path.resolve()}")
# ------------------------------------------------------------
# Step 2 - Load source files
# ------------------------------------------------------------
market_data = pd.read_csv(market_data_path)
income_statement = pd.read_csv(income_statement_path)
balance_sheet = pd.read_csv(balance_sheet_path)
dim_company = pd.read_csv(dim_company_path)
dim_period = pd.read_csv(dim_period_path)
print("Fact_Market_Data loaded:", len(market_data), "rows")
print("Fact_Income_Statement loaded:", len(income_statement), "rows")
print("Fact_Balance_Sheet loaded:", len(balance_sheet), "rows")
# ------------------------------------------------------------
# Step 3 - Extract income statement inputs
# ------------------------------------------------------------
income_inputs = income_statement[
income_statement["RatioInput"].isin(
[
"Revenue",
"Net Income",
"Operating Income"
]
)
].copy()
income_pivot = (
income_inputs
.pivot_table(
index=["CompanyID", "PeriodID"],
columns="RatioInput",
values="Amount",
aggfunc="sum"
)
.reset_index()
)
for column in ["Revenue", "Net Income", "Operating Income"]:
if column not in income_pivot.columns:
income_pivot[column] = 0
# ------------------------------------------------------------
# Step 4 - Extract balance sheet inputs
# ------------------------------------------------------------
balance_inputs = balance_sheet[
balance_sheet["RatioInput"].isin(
[
"Total Equity",
"Total Assets",
"Total Liabilities",
"Cash"
]
)
].copy()
balance_pivot = (
balance_inputs
.pivot_table(
index=["CompanyID", "PeriodID"],
columns="RatioInput",
values="Amount",
aggfunc="sum"
)
.reset_index()
)
for column in ["Total Equity", "Total Assets", "Total Liabilities", "Cash"]:
if column not in balance_pivot.columns:
balance_pivot[column] = 0
# ------------------------------------------------------------
# Step 5 - Create valuation base table
# ------------------------------------------------------------
# Fact_Market_Data.csv already contains:
# TotalDebt, Cash, EnterpriseValue, EPS, BookValuePerShare, etc.
# So we do not rebuild TotalDebt here.
valuation_base = (
market_data
.merge(income_pivot, on=["CompanyID", "PeriodID"], how="left")
.merge(balance_pivot, on=["CompanyID", "PeriodID"], how="left")
)
valuation_base = valuation_base.fillna(0)
# Avoid duplicate-column issues if pandas creates suffixes
if "Cash_x" in valuation_base.columns and "Cash" not in valuation_base.columns:
valuation_base["Cash"] = valuation_base["Cash_x"]
if "TotalDebt" not in valuation_base.columns:
valuation_base["TotalDebt"] = 0
# ------------------------------------------------------------
# Step 6 - Calculate valuation ratios
# ------------------------------------------------------------
records = []
for _, row in valuation_base.iterrows():
company_id = int(row["CompanyID"])
period_id = int(row["PeriodID"])
share_price = float(row["SharePrice"])
shares_outstanding = float(row["SharesOutstanding"])
market_cap = float(row["MarketCapitalization"])
enterprise_value = float(row["EnterpriseValue"])
revenue = float(row["Revenue"])
net_income = float(row["Net Income"])
operating_income = float(row["Operating Income"])
total_equity = float(row["Total Equity"])
total_assets = float(row["Total Assets"])
total_liabilities = float(row["Total Liabilities"])
total_debt = float(row["TotalDebt"])
cash = float(row["Cash"])
dividends_paid = float(row["DividendsPaid"])
eps = float(row["EPS"])
book_value_per_share = float(row["BookValuePerShare"])
dividend_per_share = float(row["DividendPerShare"])
price_to_earnings = share_price / eps if eps != 0 else 0
price_to_book = (
share_price / book_value_per_share
if book_value_per_share != 0
else 0
)
price_to_sales = (
market_cap / revenue
if revenue != 0
else 0
)
dividend_yield = (
dividend_per_share / share_price
if share_price != 0
else 0
)
earnings_yield = (
eps / share_price
if share_price != 0
else 0
)
market_to_assets = (
market_cap / total_assets
if total_assets != 0
else 0
)
enterprise_value_to_sales = (
enterprise_value / revenue
if revenue != 0
else 0
)
enterprise_value_to_operating_income = (
enterprise_value / operating_income
if operating_income != 0
else 0
)
market_cap_to_equity = (
market_cap / total_equity
if total_equity != 0
else 0
)
net_debt = total_debt - cash
records.extend([
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Share Price",
"RatioValue": round(share_price, 2),
"RatioFormat": "Currency",
"Interpretation": "Synthetic market price per share."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Shares Outstanding",
"RatioValue": round(shares_outstanding, 0),
"RatioFormat": "Number",
"Interpretation": "Number of shares outstanding."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Market Capitalization",
"RatioValue": round(market_cap, 2),
"RatioFormat": "Currency",
"Interpretation": "Share price multiplied by shares outstanding."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Enterprise Value",
"RatioValue": round(enterprise_value, 2),
"RatioFormat": "Currency",
"Interpretation": "Market capitalization plus total debt minus cash."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Net Debt",
"RatioValue": round(net_debt, 2),
"RatioFormat": "Currency",
"Interpretation": "Total debt minus cash."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "EPS",
"RatioValue": round(eps, 4),
"RatioFormat": "Currency",
"Interpretation": "Earnings per share."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Book Value Per Share",
"RatioValue": round(book_value_per_share, 4),
"RatioFormat": "Currency",
"Interpretation": "Equity value per share."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Dividend Per Share",
"RatioValue": round(dividend_per_share, 4),
"RatioFormat": "Currency",
"Interpretation": "Dividends paid per share."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Price / Earnings Ratio",
"RatioValue": round(price_to_earnings, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures market price relative to earnings per share."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Price / Book Ratio",
"RatioValue": round(price_to_book, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures market price relative to book value per share."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Price / Sales Ratio",
"RatioValue": round(price_to_sales, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures market capitalization relative to revenue."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Dividend Yield",
"RatioValue": round(dividend_yield, 4),
"RatioFormat": "Percent",
"Interpretation": "Measures dividend income relative to share price."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Earnings Yield",
"RatioValue": round(earnings_yield, 4),
"RatioFormat": "Percent",
"Interpretation": "Measures earnings per share relative to share price."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Enterprise Value / Sales",
"RatioValue": round(enterprise_value_to_sales, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures enterprise value relative to sales."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Enterprise Value / Operating Income",
"RatioValue": round(enterprise_value_to_operating_income, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures enterprise value relative to operating income."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Market Cap / Equity",
"RatioValue": round(market_cap_to_equity, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures market capitalization relative to book equity."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Market Cap / Assets",
"RatioValue": round(market_to_assets, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures market capitalization relative to total assets."
}
])
fact_valuation_ratios = pd.DataFrame(records)
# ------------------------------------------------------------
# Step 7 - Add company and period labels
# ------------------------------------------------------------
fact_valuation_ratios = fact_valuation_ratios.merge(
dim_company[["CompanyID", "CompanyName", "Ticker", "Industry"]],
on="CompanyID",
how="left"
)
fact_valuation_ratios = fact_valuation_ratios.merge(
dim_period[
[
"PeriodID",
"FiscalYear",
"FiscalQuarter",
"YearQuarter",
"PeriodEndDate"
]
],
on="PeriodID",
how="left"
)
fact_valuation_ratios = fact_valuation_ratios[
[
"CompanyID",
"CompanyName",
"Ticker",
"Industry",
"PeriodID",
"FiscalYear",
"FiscalQuarter",
"YearQuarter",
"PeriodEndDate",
"RatioCategory",
"RatioName",
"RatioValue",
"RatioFormat",
"Interpretation"
]
]
# ------------------------------------------------------------
# Step 8 - Export Fact_Investment_Valuation_Ratios
# ------------------------------------------------------------
fact_valuation_ratios.to_csv(
base_path / "Fact_Investment_Valuation_Ratios.csv",
index=False
)
print("Fact_Investment_Valuation_Ratios.csv created successfully.")
# ------------------------------------------------------------
# Step 9 - Create validation summary
# ------------------------------------------------------------
validation_records = []
for _, row in valuation_base.iterrows():
company_id = int(row["CompanyID"])
period_id = int(row["PeriodID"])
share_price = float(row["SharePrice"])
shares_outstanding = float(row["SharesOutstanding"])
market_cap = float(row["MarketCapitalization"])
enterprise_value = float(row["EnterpriseValue"])
revenue = float(row["Revenue"])
total_debt = float(row["TotalDebt"])
cash = float(row["Cash"])
eps = float(row["EPS"])
book_value_per_share = float(row["BookValuePerShare"])
expected_market_cap = share_price * shares_outstanding
market_cap_difference = round(market_cap - expected_market_cap, 2)
expected_enterprise_value = market_cap + total_debt - cash
enterprise_value_difference = round(
enterprise_value - expected_enterprise_value,
2
)
expected_pe = share_price / eps if eps != 0 else 0
expected_pb = (
share_price / book_value_per_share
if book_value_per_share != 0
else 0
)
expected_price_to_sales = market_cap / revenue if revenue != 0 else 0
validation_records.append({
"CompanyID": company_id,
"PeriodID": period_id,
"MarketCapitalization": round(market_cap, 2),
"ExpectedMarketCapitalization": round(expected_market_cap, 2),
"MarketCapDifference": market_cap_difference,
"EnterpriseValue": round(enterprise_value, 2),
"ExpectedEnterpriseValue": round(expected_enterprise_value, 2),
"EnterpriseValueDifference": enterprise_value_difference,
"ExpectedPE": round(expected_pe, 4),
"ExpectedPB": round(expected_pb, 4),
"ExpectedPriceToSales": round(expected_price_to_sales, 4),
"Status": (
"PASSED"
if abs(market_cap_difference) <= 0.01
and abs(enterprise_value_difference) <= 0.01
else "FAILED"
)
})
valuation_validation = pd.DataFrame(validation_records)
valuation_validation.to_csv(
base_path / "Investment_Valuation_Validation_Summary.csv",
index=False
)
print("Investment_Valuation_Validation_Summary.csv created successfully.")
# ------------------------------------------------------------
# Step 10 - Print validation summary
# ------------------------------------------------------------
print()
print("==============================")
print("VALIDATION SUMMARY")
print("==============================")
print("Investment valuation ratio rows:", len(fact_valuation_ratios))
print("Validation rows:", len(valuation_validation))
print()
print("Rows by RatioName:")
print(
fact_valuation_ratios
.groupby("RatioName")["RatioValue"]
.count()
.reset_index(name="NumberOfRows")
)
print()
print("Investment Valuation Summary by Company:")
print(
fact_valuation_ratios[
fact_valuation_ratios["RatioName"].isin(
[
"Price / Earnings Ratio",
"Price / Book Ratio",
"Price / Sales Ratio",
"Dividend Yield",
"Enterprise Value / Sales",
"Enterprise Value / Operating Income"
]
)
]
.groupby(["CompanyID", "CompanyName", "Ticker", "RatioName"])["RatioValue"]
.mean()
.round(4)
.reset_index(name="AverageRatio")
)
print()
print("Failed validation rows:")
failed_rows = valuation_validation[
valuation_validation["Status"] == "FAILED"
]
if failed_rows.empty:
print("No failed rows. Investment Valuation validation passed.")
else:
print(failed_rows)
print()
print("Preview of Fact_Investment_Valuation_Ratios:")
print(fact_valuation_ratios.head(20))
print()
print("Files currently in project folder:")
for file in base_path.glob("*.csv"):
print("-", file.name)
print()
print("Topic 15 completed successfully.")The script or instructions create:
El script o las instrucciones crean:
financial_ratios_bi_training/Fact_Investment_Valuation_Ratios.csvThis topic is not only a technical exercise. It teaches students how to translate accounting data into management information. The output becomes part of the BI layer used later for ratios, dashboards, trend analysis, benchmarking, and executive decision-making.
Este tópico no es solo un ejercicio técnico. Enseña a convertir data contable en información gerencial. El archivo de salida se integra a la capa de BI que luego se usa para ratios, dashboards, tendencias, comparación entre compañías y decisiones ejecutivas.
Before moving forward, open the generated file, review the columns, confirm the number of companies and periods, and make sure the numbers make financial sense. Good BI starts with clean, explainable, and validated data.
This is the corrected and live-tested script for Topic 15: Investment Valuation Ratios.
from pathlib import Path
import pandas as pd
# ============================================================
# Financial Ratios Analysis in BI
# Topic 15 - Investment Valuation Ratios
# Corrected version: uses TotalDebt from Fact_Market_Data.csv
# ============================================================
# ------------------------------------------------------------
# Step 1 - Set project folder
# ------------------------------------------------------------
base_path = Path("financial_ratios_bi_training")
if not base_path.exists():
raise FileNotFoundError(
"The project folder does not exist. Please run Topic 1 first."
)
market_data_path = base_path / "Fact_Market_Data.csv"
income_statement_path = base_path / "Fact_Income_Statement.csv"
balance_sheet_path = base_path / "Fact_Balance_Sheet.csv"
dim_company_path = base_path / "Dim_Company.csv"
dim_period_path = base_path / "Dim_Period.csv"
required_files = [
market_data_path,
income_statement_path,
balance_sheet_path,
dim_company_path,
dim_period_path
]
for file_path in required_files:
if not file_path.exists():
raise FileNotFoundError(
f"Required file not found: {file_path.name}. "
"Please run the previous topics first."
)
print(f"Project folder found: {base_path.resolve()}")
# ------------------------------------------------------------
# Step 2 - Load source files
# ------------------------------------------------------------
market_data = pd.read_csv(market_data_path)
income_statement = pd.read_csv(income_statement_path)
balance_sheet = pd.read_csv(balance_sheet_path)
dim_company = pd.read_csv(dim_company_path)
dim_period = pd.read_csv(dim_period_path)
print("Fact_Market_Data loaded:", len(market_data), "rows")
print("Fact_Income_Statement loaded:", len(income_statement), "rows")
print("Fact_Balance_Sheet loaded:", len(balance_sheet), "rows")
# ------------------------------------------------------------
# Step 3 - Extract income statement inputs
# ------------------------------------------------------------
income_inputs = income_statement[
income_statement["RatioInput"].isin(
[
"Revenue",
"Net Income",
"Operating Income"
]
)
].copy()
income_pivot = (
income_inputs
.pivot_table(
index=["CompanyID", "PeriodID"],
columns="RatioInput",
values="Amount",
aggfunc="sum"
)
.reset_index()
)
for column in ["Revenue", "Net Income", "Operating Income"]:
if column not in income_pivot.columns:
income_pivot[column] = 0
# ------------------------------------------------------------
# Step 4 - Extract balance sheet inputs
# ------------------------------------------------------------
balance_inputs = balance_sheet[
balance_sheet["RatioInput"].isin(
[
"Total Equity",
"Total Assets",
"Total Liabilities",
"Cash"
]
)
].copy()
balance_pivot = (
balance_inputs
.pivot_table(
index=["CompanyID", "PeriodID"],
columns="RatioInput",
values="Amount",
aggfunc="sum"
)
.reset_index()
)
for column in ["Total Equity", "Total Assets", "Total Liabilities", "Cash"]:
if column not in balance_pivot.columns:
balance_pivot[column] = 0
# ------------------------------------------------------------
# Step 5 - Create valuation base table
# ------------------------------------------------------------
# Fact_Market_Data.csv already contains:
# TotalDebt, Cash, EnterpriseValue, EPS, BookValuePerShare, etc.
# So we do not rebuild TotalDebt here.
valuation_base = (
market_data
.merge(income_pivot, on=["CompanyID", "PeriodID"], how="left")
.merge(balance_pivot, on=["CompanyID", "PeriodID"], how="left")
)
valuation_base = valuation_base.fillna(0)
# Avoid duplicate-column issues if pandas creates suffixes
if "Cash_x" in valuation_base.columns and "Cash" not in valuation_base.columns:
valuation_base["Cash"] = valuation_base["Cash_x"]
if "TotalDebt" not in valuation_base.columns:
valuation_base["TotalDebt"] = 0
# ------------------------------------------------------------
# Step 6 - Calculate valuation ratios
# ------------------------------------------------------------
records = []
for _, row in valuation_base.iterrows():
company_id = int(row["CompanyID"])
period_id = int(row["PeriodID"])
share_price = float(row["SharePrice"])
shares_outstanding = float(row["SharesOutstanding"])
market_cap = float(row["MarketCapitalization"])
enterprise_value = float(row["EnterpriseValue"])
revenue = float(row["Revenue"])
net_income = float(row["Net Income"])
operating_income = float(row["Operating Income"])
total_equity = float(row["Total Equity"])
total_assets = float(row["Total Assets"])
total_liabilities = float(row["Total Liabilities"])
total_debt = float(row["TotalDebt"])
cash = float(row["Cash"])
dividends_paid = float(row["DividendsPaid"])
eps = float(row["EPS"])
book_value_per_share = float(row["BookValuePerShare"])
dividend_per_share = float(row["DividendPerShare"])
price_to_earnings = share_price / eps if eps != 0 else 0
price_to_book = (
share_price / book_value_per_share
if book_value_per_share != 0
else 0
)
price_to_sales = (
market_cap / revenue
if revenue != 0
else 0
)
dividend_yield = (
dividend_per_share / share_price
if share_price != 0
else 0
)
earnings_yield = (
eps / share_price
if share_price != 0
else 0
)
market_to_assets = (
market_cap / total_assets
if total_assets != 0
else 0
)
enterprise_value_to_sales = (
enterprise_value / revenue
if revenue != 0
else 0
)
enterprise_value_to_operating_income = (
enterprise_value / operating_income
if operating_income != 0
else 0
)
market_cap_to_equity = (
market_cap / total_equity
if total_equity != 0
else 0
)
net_debt = total_debt - cash
records.extend([
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Share Price",
"RatioValue": round(share_price, 2),
"RatioFormat": "Currency",
"Interpretation": "Synthetic market price per share."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Shares Outstanding",
"RatioValue": round(shares_outstanding, 0),
"RatioFormat": "Number",
"Interpretation": "Number of shares outstanding."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Market Capitalization",
"RatioValue": round(market_cap, 2),
"RatioFormat": "Currency",
"Interpretation": "Share price multiplied by shares outstanding."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Enterprise Value",
"RatioValue": round(enterprise_value, 2),
"RatioFormat": "Currency",
"Interpretation": "Market capitalization plus total debt minus cash."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Net Debt",
"RatioValue": round(net_debt, 2),
"RatioFormat": "Currency",
"Interpretation": "Total debt minus cash."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "EPS",
"RatioValue": round(eps, 4),
"RatioFormat": "Currency",
"Interpretation": "Earnings per share."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Book Value Per Share",
"RatioValue": round(book_value_per_share, 4),
"RatioFormat": "Currency",
"Interpretation": "Equity value per share."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Dividend Per Share",
"RatioValue": round(dividend_per_share, 4),
"RatioFormat": "Currency",
"Interpretation": "Dividends paid per share."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Price / Earnings Ratio",
"RatioValue": round(price_to_earnings, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures market price relative to earnings per share."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Price / Book Ratio",
"RatioValue": round(price_to_book, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures market price relative to book value per share."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Price / Sales Ratio",
"RatioValue": round(price_to_sales, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures market capitalization relative to revenue."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Dividend Yield",
"RatioValue": round(dividend_yield, 4),
"RatioFormat": "Percent",
"Interpretation": "Measures dividend income relative to share price."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Earnings Yield",
"RatioValue": round(earnings_yield, 4),
"RatioFormat": "Percent",
"Interpretation": "Measures earnings per share relative to share price."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Enterprise Value / Sales",
"RatioValue": round(enterprise_value_to_sales, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures enterprise value relative to sales."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Enterprise Value / Operating Income",
"RatioValue": round(enterprise_value_to_operating_income, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures enterprise value relative to operating income."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Market Cap / Equity",
"RatioValue": round(market_cap_to_equity, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures market capitalization relative to book equity."
},
{
"CompanyID": company_id,
"PeriodID": period_id,
"RatioCategory": "Investment Valuation",
"RatioName": "Market Cap / Assets",
"RatioValue": round(market_to_assets, 4),
"RatioFormat": "Decimal",
"Interpretation": "Measures market capitalization relative to total assets."
}
])
fact_valuation_ratios = pd.DataFrame(records)
# ------------------------------------------------------------
# Step 7 - Add company and period labels
# ------------------------------------------------------------
fact_valuation_ratios = fact_valuation_ratios.merge(
dim_company[["CompanyID", "CompanyName", "Ticker", "Industry"]],
on="CompanyID",
how="left"
)
fact_valuation_ratios = fact_valuation_ratios.merge(
dim_period[
[
"PeriodID",
"FiscalYear",
"FiscalQuarter",
"YearQuarter",
"PeriodEndDate"
]
],
on="PeriodID",
how="left"
)
fact_valuation_ratios = fact_valuation_ratios[
[
"CompanyID",
"CompanyName",
"Ticker",
"Industry",
"PeriodID",
"FiscalYear",
"FiscalQuarter",
"YearQuarter",
"PeriodEndDate",
"RatioCategory",
"RatioName",
"RatioValue",
"RatioFormat",
"Interpretation"
]
]
# ------------------------------------------------------------
# Step 8 - Export Fact_Investment_Valuation_Ratios
# ------------------------------------------------------------
fact_valuation_ratios.to_csv(
base_path / "Fact_Investment_Valuation_Ratios.csv",
index=False
)
print("Fact_Investment_Valuation_Ratios.csv created successfully.")
# ------------------------------------------------------------
# Step 9 - Create validation summary
# ------------------------------------------------------------
validation_records = []
for _, row in valuation_base.iterrows():
company_id = int(row["CompanyID"])
period_id = int(row["PeriodID"])
share_price = float(row["SharePrice"])
shares_outstanding = float(row["SharesOutstanding"])
market_cap = float(row["MarketCapitalization"])
enterprise_value = float(row["EnterpriseValue"])
revenue = float(row["Revenue"])
total_debt = float(row["TotalDebt"])
cash = float(row["Cash"])
eps = float(row["EPS"])
book_value_per_share = float(row["BookValuePerShare"])
expected_market_cap = share_price * shares_outstanding
market_cap_difference = round(market_cap - expected_market_cap, 2)
expected_enterprise_value = market_cap + total_debt - cash
enterprise_value_difference = round(
enterprise_value - expected_enterprise_value,
2
)
expected_pe = share_price / eps if eps != 0 else 0
expected_pb = (
share_price / book_value_per_share
if book_value_per_share != 0
else 0
)
expected_price_to_sales = market_cap / revenue if revenue != 0 else 0
validation_records.append({
"CompanyID": company_id,
"PeriodID": period_id,
"MarketCapitalization": round(market_cap, 2),
"ExpectedMarketCapitalization": round(expected_market_cap, 2),
"MarketCapDifference": market_cap_difference,
"EnterpriseValue": round(enterprise_value, 2),
"ExpectedEnterpriseValue": round(expected_enterprise_value, 2),
"EnterpriseValueDifference": enterprise_value_difference,
"ExpectedPE": round(expected_pe, 4),
"ExpectedPB": round(expected_pb, 4),
"ExpectedPriceToSales": round(expected_price_to_sales, 4),
"Status": (
"PASSED"
if abs(market_cap_difference) <= 0.01
and abs(enterprise_value_difference) <= 0.01
else "FAILED"
)
})
valuation_validation = pd.DataFrame(validation_records)
valuation_validation.to_csv(
base_path / "Investment_Valuation_Validation_Summary.csv",
index=False
)
print("Investment_Valuation_Validation_Summary.csv created successfully.")
# ------------------------------------------------------------
# Step 10 - Print validation summary
# ------------------------------------------------------------
print()
print("==============================")
print("VALIDATION SUMMARY")
print("==============================")
print("Investment valuation ratio rows:", len(fact_valuation_ratios))
print("Validation rows:", len(valuation_validation))
print()
print("Rows by RatioName:")
print(
fact_valuation_ratios
.groupby("RatioName")["RatioValue"]
.count()
.reset_index(name="NumberOfRows")
)
print()
print("Investment Valuation Summary by Company:")
print(
fact_valuation_ratios[
fact_valuation_ratios["RatioName"].isin(
[
"Price / Earnings Ratio",
"Price / Book Ratio",
"Price / Sales Ratio",
"Dividend Yield",
"Enterprise Value / Sales",
"Enterprise Value / Operating Income"
]
)
]
.groupby(["CompanyID", "CompanyName", "Ticker", "RatioName"])["RatioValue"]
.mean()
.round(4)
.reset_index(name="AverageRatio")
)
print()
print("Failed validation rows:")
failed_rows = valuation_validation[
valuation_validation["Status"] == "FAILED"
]
if failed_rows.empty:
print("No failed rows. Investment Valuation validation passed.")
else:
print(failed_rows)
print()
print("Preview of Fact_Investment_Valuation_Ratios:")
print(fact_valuation_ratios.head(20))
print()
print("Files currently in project folder:")
for file in base_path.glob("*.csv"):
print("-", file.name)
print()
print("Topic 15 completed successfully.")