Topic 1 - Create Synthetic Financial Companies Dataset

Topic 1 — Create Synthetic Financial Companies Dataset

This first topic creates the foundation of the training package. We will generate two clean CSV files that will later connect to trial balance, financial statements, market data, and ratio fact tables.

Dim_Company→Dim_Period→Future Fact Tables→Power BI Model

Production / Cybersecurity Warning

This exercise uses synthetic data and must be executed only in a practice environment. Do not run scripts in production, real folders, corporate databases, or accounting systems without formal authorization.

Before automating any real financial or accounting process, validate backups, permissions, least-privilege access, change-control approval, sandbox testing, audit requirements, and organizational cybersecurity protocols.

Objective

Create the first two dimension tables of the BI financial model:

Dim_Company.csv
Company, ticker, industry, business profile, risk profile, country, and currency.

Dim_Period.csv
Fiscal years, quarters, period start months, end months, and period end dates.

Business Scenario

A technical school wants to create a practical BI training where students learn financial ratio analysis from zero. Instead of starting with messy real-world statements, we create a controlled synthetic dataset with realistic business behavior.

Company	Industry	Financial Behavior	Risk Profile
Alpha Retail Corp	Retail	High revenue, low margin, high inventory	Medium
Beta Health Services	Healthcare	Stable revenue, medium margin, low debt	Low
Gamma Manufacturing Inc	Manufacturing	High fixed assets, high debt, cyclical revenue	High
Delta Tech Solutions	Technology	High margin, low inventory, high valuation	Medium
Omega Logistics Group	Logistics	High operating cost, high fixed assets	Medium-High

Step-by-Step Practice

Step 1 — Create the Working Folder

Python

from pathlib import Path

# Create project folder
base_path = Path("financial_ratios_bi_training")
base_path.mkdir(exist_ok=True)

print(f"Project folder created: {base_path.resolve()}")

Step 2 — Import Libraries

Python

import pandas as pd
from pathlib import Path

Step 3 — Create Dim_Company

Python

companies_data = [
    {
        "CompanyID": 1,
        "CompanyName": "Alpha Retail Corp",
        "Ticker": "ARC",
        "Industry": "Retail",
        "BusinessProfile": "High revenue, low margin, high inventory",
        "RiskProfile": "Medium",
        "Country": "United States",
        "Currency": "USD"
    },
    {
        "CompanyID": 2,
        "CompanyName": "Beta Health Services",
        "Ticker": "BHS",
        "Industry": "Healthcare",
        "BusinessProfile": "Stable revenue, medium margin, low debt",
        "RiskProfile": "Low",
        "Country": "United States",
        "Currency": "USD"
    },
    {
        "CompanyID": 3,
        "CompanyName": "Gamma Manufacturing Inc",
        "Ticker": "GMI",
        "Industry": "Manufacturing",
        "BusinessProfile": "High fixed assets, high debt, cyclical revenue",
        "RiskProfile": "High",
        "Country": "United States",
        "Currency": "USD"
    },
    {
        "CompanyID": 4,
        "CompanyName": "Delta Tech Solutions",
        "Ticker": "DTS",
        "Industry": "Technology",
        "BusinessProfile": "High margin, low inventory, high valuation",
        "RiskProfile": "Medium",
        "Country": "United States",
        "Currency": "USD"
    },
    {
        "CompanyID": 5,
        "CompanyName": "Omega Logistics Group",
        "Ticker": "OLG",
        "Industry": "Logistics",
        "BusinessProfile": "High operating cost, high fixed assets",
        "RiskProfile": "Medium-High",
        "Country": "United States",
        "Currency": "USD"
    }
]

dim_company = pd.DataFrame(companies_data)

dim_company

Step 4 — Export Dim_Company to CSV

Python

output_path = Path("financial_ratios_bi_training")

dim_company.to_csv(output_path / "Dim_Company.csv", index=False)

print("Dim_Company.csv created successfully.")

Step 5 — Create Dim_Period

We create fiscal periods from 2021 Q1 through 2025 Q4. That gives us 5 years, 4 quarters per year, and 20 fiscal periods.

Python

periods = []
period_id = 1

for year in range(2021, 2026):
    for quarter in range(1, 5):
        if quarter == 1:
            start_month = 1
            end_month = 3
            period_end = f"{year}-03-31"
        elif quarter == 2:
            start_month = 4
            end_month = 6
            period_end = f"{year}-06-30"
        elif quarter == 3:
            start_month = 7
            end_month = 9
            period_end = f"{year}-09-30"
        else:
            start_month = 10
            end_month = 12
            period_end = f"{year}-12-31"

        periods.append({
            "PeriodID": period_id,
            "FiscalYear": year,
            "FiscalQuarter": f"Q{quarter}",
            "YearQuarter": f"{year} Q{quarter}",
            "QuarterNumber": quarter,
            "PeriodStartMonth": start_month,
            "PeriodEndMonth": end_month,
            "PeriodEndDate": period_end
        })

        period_id += 1

dim_period = pd.DataFrame(periods)

dim_period

Step 6 — Export Dim_Period to CSV

Python

dim_period.to_csv(output_path / "Dim_Period.csv", index=False)

print("Dim_Period.csv created successfully.")

Step 7 — Validate the Output

Python

print("Dim_Company rows:", len(dim_company))
print("Dim_Period rows:", len(dim_period))

print("\nCompanies:")
print(dim_company[["CompanyID", "CompanyName", "Industry", "RiskProfile"]])

print("\nPeriods:")
print(dim_period[["PeriodID", "YearQuarter", "PeriodEndDate"]].head())
print(dim_period[["PeriodID", "YearQuarter", "PeriodEndDate"]].tail())

Step 8 — Check the Created Files

Python

for file in output_path.glob("*.csv"):
    print(file.name)

Expected Output

Expected result

Dim_Company rows: 5
Dim_Period rows: 20

Created files:
Dim_Company.csv
Dim_Period.csv

Business Interpretation

At this point, we already have the foundation of the BI model. Dim_Company tells us who we are analyzing. Dim_Period tells us when we are analyzing.

Later, every fact table will connect to these two dimensions: Trial Balance, Income Statement, Balance Sheet, Cash Flow, Market Data, and Financial Ratios.

Final Result of Topic 1

Folder structure

financial_ratios_bi_training/
    Dim_Company.csv
    Dim_Period.csv

Next topic: Topic 2 — Create the Chart of Accounts.

Topic 1 — Crear Dataset Sintético de Compañías Financieras

Este primer tópico crea la base del paquete de entrenamiento. Vamos a generar dos archivos CSV limpios que más adelante se conectarán con trial balance, estados financieros, datos de mercado y tablas de ratios.

Dim_Company→Dim_Period→Tablas Facturas Futuras→Modelo Power BI

Advertencia de Producción / Ciberseguridad

Este ejercicio usa data sintética y debe ejecutarse solamente en un ambiente de práctica. No ejecutes scripts en producción, carpetas reales, bases de datos corporativas ni sistemas contables sin autorización formal.

Antes de automatizar cualquier proceso financiero o contable real, valida respaldos, permisos, acceso de mínimo privilegio, aprobación de control de cambios, pruebas en sandbox, requisitos de auditoría y protocolos de ciberseguridad de la organización.

Objetivo

Crear las primeras dos tablas dimensión del modelo financiero en BI:

Dim_Company.csv
Compañía, ticker, industria, perfil de negocio, perfil de riesgo, país y moneda.

Dim_Period.csv
Años fiscales, trimestres, meses de inicio, meses de cierre y fecha final del período.

Escenario de Negocio

Una escuela técnica quiere crear un entrenamiento práctico de BI donde los estudiantes aprendan análisis de ratios financieros desde cero. En vez de comenzar con estados financieros reales y desordenados, creamos un dataset sintético controlado con comportamiento financiero realista.

Compañía	Industria	Comportamiento Financiero	Perfil de Riesgo
Alpha Retail Corp	Retail	Ventas altas, margen bajo, inventario alto	Medio
Beta Health Services	Healthcare	Ventas estables, margen medio, deuda baja	Bajo
Gamma Manufacturing Inc	Manufacturing	Activos fijos altos, deuda alta, ventas cíclicas	Alto
Delta Tech Solutions	Technology	Margen alto, inventario bajo, valoración alta	Medio
Omega Logistics Group	Logistics	Costo operativo alto, activos fijos altos	Medio-Alto

Práctica Paso a Paso

El código es el mismo en ambos idiomas para que el estudiante pueda copiar, ejecutar y validar el resultado directamente.

Paso 1 — Crear la Carpeta de Trabajo

Python

from pathlib import Path

# Create project folder
base_path = Path("financial_ratios_bi_training")
base_path.mkdir(exist_ok=True)

print(f"Project folder created: {base_path.resolve()}")

Paso 2 — Importar Librerías

Python

import pandas as pd
from pathlib import Path

Paso 3 — Crear Dim_Company

Python

companies_data = [
    {
        "CompanyID": 1,
        "CompanyName": "Alpha Retail Corp",
        "Ticker": "ARC",
        "Industry": "Retail",
        "BusinessProfile": "High revenue, low margin, high inventory",
        "RiskProfile": "Medium",
        "Country": "United States",
        "Currency": "USD"
    },
    {
        "CompanyID": 2,
        "CompanyName": "Beta Health Services",
        "Ticker": "BHS",
        "Industry": "Healthcare",
        "BusinessProfile": "Stable revenue, medium margin, low debt",
        "RiskProfile": "Low",
        "Country": "United States",
        "Currency": "USD"
    },
    {
        "CompanyID": 3,
        "CompanyName": "Gamma Manufacturing Inc",
        "Ticker": "GMI",
        "Industry": "Manufacturing",
        "BusinessProfile": "High fixed assets, high debt, cyclical revenue",
        "RiskProfile": "High",
        "Country": "United States",
        "Currency": "USD"
    },
    {
        "CompanyID": 4,
        "CompanyName": "Delta Tech Solutions",
        "Ticker": "DTS",
        "Industry": "Technology",
        "BusinessProfile": "High margin, low inventory, high valuation",
        "RiskProfile": "Medium",
        "Country": "United States",
        "Currency": "USD"
    },
    {
        "CompanyID": 5,
        "CompanyName": "Omega Logistics Group",
        "Ticker": "OLG",
        "Industry": "Logistics",
        "BusinessProfile": "High operating cost, high fixed assets",
        "RiskProfile": "Medium-High",
        "Country": "United States",
        "Currency": "USD"
    }
]

dim_company = pd.DataFrame(companies_data)

dim_company

Paso 4 — Exportar Dim_Company a CSV

Python

output_path = Path("financial_ratios_bi_training")

dim_company.to_csv(output_path / "Dim_Company.csv", index=False)

print("Dim_Company.csv created successfully.")

Paso 5 — Crear Dim_Period

Crearemos períodos desde 2021 Q1 hasta 2025 Q4. Eso nos da 5 años, 4 trimestres por año y 20 períodos fiscales.

Python

periods = []
period_id = 1

for year in range(2021, 2026):
    for quarter in range(1, 5):
        if quarter == 1:
            start_month = 1
            end_month = 3
            period_end = f"{year}-03-31"
        elif quarter == 2:
            start_month = 4
            end_month = 6
            period_end = f"{year}-06-30"
        elif quarter == 3:
            start_month = 7
            end_month = 9
            period_end = f"{year}-09-30"
        else:
            start_month = 10
            end_month = 12
            period_end = f"{year}-12-31"

        periods.append({
            "PeriodID": period_id,
            "FiscalYear": year,
            "FiscalQuarter": f"Q{quarter}",
            "YearQuarter": f"{year} Q{quarter}",
            "QuarterNumber": quarter,
            "PeriodStartMonth": start_month,
            "PeriodEndMonth": end_month,
            "PeriodEndDate": period_end
        })

        period_id += 1

dim_period = pd.DataFrame(periods)

dim_period

Paso 6 — Exportar Dim_Period a CSV

Python

dim_period.to_csv(output_path / "Dim_Period.csv", index=False)

print("Dim_Period.csv created successfully.")

Paso 7 — Validar el Resultado

Python

print("Dim_Company rows:", len(dim_company))
print("Dim_Period rows:", len(dim_period))

print("\nCompanies:")
print(dim_company[["CompanyID", "CompanyName", "Industry", "RiskProfile"]])

print("\nPeriods:")
print(dim_period[["PeriodID", "YearQuarter", "PeriodEndDate"]].head())
print(dim_period[["PeriodID", "YearQuarter", "PeriodEndDate"]].tail())

Paso 8 — Verificar los Archivos Creados

Python

for file in output_path.glob("*.csv"):
    print(file.name)

Resultado Esperado

Expected result

Dim_Company rows: 5
Dim_Period rows: 20

Created files:
Dim_Company.csv
Dim_Period.csv

Interpretación de Negocio

En este punto ya tenemos la base del modelo BI. Dim_Company nos dice a quién estamos analizando. Dim_Period nos dice cuándo estamos analizando.

Más adelante, cada tabla de hechos se conectará con estas dos dimensiones: Trial Balance, Income Statement, Balance Sheet, Cash Flow, Market Data y Financial Ratios.

Resultado Final del Topic 1

Estructura de carpeta

financial_ratios_bi_training/
    Dim_Company.csv
    Dim_Period.csv

Próximo tópico: Topic 2 — Create the Chart of Accounts.

Financial Ratios Analysis in BI