How to Create a Financial Statement Database

I’m a quantamental investor and trader. I believe that you can use financial statement information to separate winners from losers.

In this post, I’m going to discuss how to create a financial statement database in Python using SQLAlchemy. If you’re new to algorithmic trading or want to follow along, make sure you already have an equity database.

Before we get started, we need to discuss how financial statements are reported and what data we’ll want to import into our database.

Financial Statement Reporting & Restatements

The U.S. Securities and Exchange Commission (SEC) is an independent federal government agency. The SEC is responsible for maintaining fair and functioning securities markets, and for protecting investors.

The SEC requires companies to file annual and quarterly reports on an ongoing basis. For more details on reporting requirements, check out Exchange Act Reporting and Registration.

These annual and quarterly reports contain the financial statement information we need as investors to value the company.

Companies can amend these reports if needed, which opens up the potential for look-ahead bias. To solve this challenge, we’ll need to add the reporting date for each financial statement report. Easy right? Not so fast. Many times a company will restate its annual report, but they won’t go back and correct the associated quarterly reports. To solve this, we’ll need to adjust the quarterly reports so we can calculate the trailing twelve months (TTM) financials.

Now that we know the why and when, let’s turn our attention to the what.

Financial Statement Data

While financial statements follow a set of rules, raw financial data is not standardized in a way that makes it easy to store in a database. For instance, let’s look at the 2019 10-K’s for Google and an airline part manufacturer, Transdigm.

Google 2019 10-K Income Statement TransDigm 2019 10-K Income Statement

Differences also occur for the same report depending upon where you source the data. Please compare Google 2019 results, shown again for convenience, with Yahoo’s version. For example, Yahoo breaks out Interest Expense separately while the 10-K does not. Moving in the other direction, Yahoo consolidates Sales and Marketing into Selling General and Administrative, whereas the 10-K shows it independently.

Google 2019 10-K Income Statement Google 2019 10-K Income Statement Yahoo

While it is evident for those with database development experience, what we’ll want to do is determine the data we need first, and then design the database second. With an eye on valuation and desired pricing multiples, below are the required fields needed for DCF valuation and the most common pricing multiples.

For the data I use with the terms defined, please reference Financial Statement Terms:

Valuation Data

Name Intrinio Tag
Revenues totalrevenue
Research and Development Expense rdexpense
Operating Income totaloperatingincome
Interest Expense totalinterestexpense
Book Value of Equity totalequityandnoncontrollinginterests
Book Value of Debt shortermdebt + longtermdebt
Cash & Marketable Securities cashandequivalents + shortterminvestments
Cross Holdings & Other Non-Operating Assets longterminvestments
Minority Interests noncontrollinginterests
Effective tax rate normalized incometaxexpense / totalpretaxincome
Marginal tax rate http://pages.stern.nyu.edu/~adamodar/New_Home_Page/datafile/countrytaxrate.html
Non-Cash Working Capital totalcurrentassets - totalcurrentassets - cashandequivalents - shortermdebt
Current stock price (In database already)
Shares outstanding weightedavebasicsharesos + RSUs

Pricing Data

Multiple Formula
p/e price / ttm earnings
ev/ebitda ev (current) / ttm ebitda
p/b price / ttm book value
p/s price / ttm sales

With the required data out of the way, let’s move onto the database design.

Database Design

I’ve already covered SqlDBM and how to install and manage PostgreSQL in How to Create an Equities Database. Let’s discuss the below financial statement schema.

Financial Statement Schema

If you’re not interested in full financials and just want “indicator” data, you could simplify your design to something similar as Sharadar’s Core US Fundamentals Data while converting it to first normal form (1NF).

If you’re interested in the full financials, then you’ll want to include the FinancialStatement, FinancialStatementLine, and FinancialStatementLineAlias tables.

FinancialStatement is the name of the financial report. The Income Statement is an example of a FinancialStatement. This table gives us the capability to have multiple versions of each financial report. Check out the difference between Bank of America and Facebook’s income statements to see why:

Financial Firm Income Statement

Non-Financial Firm Income Statement

FinancialStatementLine is a single line-item on a FinancialStatement. An example would be Cost of Goods Sold. The sequence field determines the ordering of how the fields will be displayed on the FinancialStatement.

FinancialStatementLineAlias allows us to have multiple names for a FinancialStatementLine. Your fundamentals data source may provide standardized names, but if not, you’ll want to know that “Sales, Revenue, Sales Revenue, etc.” all refer to the same FinancialStatementLine. This is especially true when using multiple data sources.

FinancialStatementFact is the data for a FinancialStatementLine. For instance, what is the latest Cost of Revenue for Google?

Financial Statement Line Items

Knowing what FinancialStatementLines to include can be daunting for new fundamental analysts. Intrinio’s data is a great starting point.

Tag Name
accountspayable Accounts Payable
accountsreceivable Accounts Receivable
accruedexpenses Accrued Expenses
accruedinterestpayable Accrued Interest Payable
accruedinvestmentincome Accrued Investment Income
accumulateddepreciation Accumulated Depreciation
acquisitions Acquisitions
allowanceforloanandleaselosses Allowance for Loan and Lease Losses
amortizationexpense Amortization Expense
amortizationofdeferredpolicyacquisitioncosts Amortization of Deferred Policy Acquisition Costs
aoci Accumulated Other Comprehensive Income / (Loss)
assetretirementandlitigationobligation Asset Retirement Reserve & Litigation Obligation
bankersacceptances Bankers Acceptance Outstanding
basicdilutedeps Basic & Diluted Earnings per Share
basiceps Basic Earnings per Share
capitalizedleaseobligationinterestexpense Capitalized Lease Obligations Interest Expense
capitalleaseobligations Capital Lease Obligations
cashandequivalents Cash & Equivalents
cashdividendspershare Cash Dividends to Common per Share
cashincometaxespaid Cash Income Taxes Paid
cashinterestpaid Cash Interest Paid
cashinterestreceived Cash Interest Received
claimsandclaimexpenses Claims and Claim Expense
commitmentsandcontingencies Commitments & Contingencies
commonequity Common Stock
currentandfuturebenefits Current and Future Benefits
currentdeferredrevenue Current Deferred Revenue
currentdeferredtaxassets Current Deferred & Refundable Income Taxes
currentdeferredtaxliabilities Current Deferred & Payable Income Tax Liabilities
currentemployeebenefitliabilities Current Employee Benefit Liabilities
customerandotherreceivables Customer and Other Receivables
customerdeposits Customer Deposits
deferredacquisitioncost Deferred Acquisition Cost
depletionexpense Depletion Expense
depositsinterestexpense Deposits Interest Expense
depositsinterestincome Deposits and Money Market Investments Interest Income
depreciationexpense Depreciation Expense
dilutedeps Diluted Earnings per Share
divestitures Divestitures
dividendspayable Dividends Payable
effectofexchangeratechanges Effect of Exchange Rate Changes
employeebenefitassets Employee Benefit Assets
explorationexpense Exploration Expense
extraordinaryincome Extraordinary Income / (Loss), net
fedfundsandrepointerestexpense Federal Funds Purchased and Securities Sold Interest Expense
fedfundsandrepointerestincome Federal Funds Sold and Securities Borrowed Interest Income
fedfundspurchased Federal Funds Purchased and Securities Sold
fedfundssold Federal Funds Sold
futurepolicybenefits Future Policy Benefits
goodwill Goodwill
grossppe Plant, Property & Equipment, gross
impairmentexpense Impairment Charge
incometaxexpense Income Tax Expense
increasedecreaseinoperatingcapital Changes in Operating Assets and Liabilities, net
intangibleassets Intangible Assets
interestbearingdeposits Interest Bearing Deposits
interestbearingdepositsatotherbanks Interest Bearing Deposits at Other Banks
investmentbankingincome Investment Banking Income
investmentsecuritiesinterestincome Investment Securities Interest Income
issuanceofcommonequity Issuance of Common Equity
issuanceofdebt Issuance of Debt
issuanceofpreferredequity Issuance of Preferred Equity
loansandleaseinterestincome Loans and Leases Interest Income
loansandleases Loans and Leases
loansheldforsale Loans Held for Sale
loansheldforsalenet Loans Held for Sale, Net
longtermdebt Long-Term Debt
longtermdebtinterestexpense Long-Term Debt Interest Expense
longterminvestments Long-Term Investments
marketingexpense Marketing Expense
mortgageservicingrights Mortgage Servicing Rights
netcashfromcontinuingfinancingactivities Net Cash From Continuing Financing Activities
netcashfromcontinuinginvestingactivities Net Cash From Continuing Investing Activities
netcashfromcontinuingoperatingactivities Net Cash From Continuing Operating Activities
netcashfromdiscontinuedfinancingactivities Net Cash From Discontinued Financing Activities
netcashfromdiscontinuedinvestingactivities Net Cash From Discontinued Investing Activities
netcashfromdiscontinuedoperatingactivities Net Cash From Discontinued Operating Activities
netcashfromfinancingactivities Net Cash From Financing Activities
netcashfrominvestingactivities Net Cash From Investing Activities
netcashfromoperatingactivities Net Cash From Operating Activities
netchangeincash Net Change in Cash & Equivalents
netchangeindeposits Net Change in Deposits
netincome Consolidated Net Income / (Loss)
netincomecontinuing Net Income / (Loss) Continuing Operations
netincomediscontinued Net Income / (Loss) Discontinued Operations
netincometocommon Net Income / (Loss) Attributable to Common Shareholders
netincometononcontrollinginterest Net Income / (Loss) Attributable to Noncontrolling Interest
netincreaseinfedfundssold Net Increase in Fed Funds Sold
netinterestincome Net Interest Income / (Expense)
netinventory Inventories, net
netloansandleases Loans and Leases, Net of Allowance
netoccupancyequipmentexpense Net Occupancy & Equipment Expense
netppe Plant, Property, & Equipment, net
netpremisesandequipment Premises and Equipment, Net
netrealizedcapitalgains Net Realized & Unrealized Capital Gains on Investments
noncashadjustmentstonetincome Non-Cash Adjustments to Reconcile Net Income
noncontrollinginterests Noncontrolling Interest
noncurrentdeferredrevenue Noncurrent Deferred Revenue
noncurrentdeferredtaxassets Noncurrent Deferred & Refundable Income Taxes
noncurrentdeferredtaxliabilities Noncurrent Deferred & Payable Income Tax Liabilities
noncurrentemployeebenefitliabilities Noncurrent Employee Benefit Liabilities
noncurrentnotereceivables Noncurrent Note & Lease Receivables
noninterestbearingdeposits Non-Interest Bearing Deposits
nonoperatingincome Nonoperating Income / (Expense), net
notereceivable Note & Lease Receivable
operatingcostofrevenue Operating Cost of Revenue
operatingrevenue Operating Revenue
otheradjustmentstoconsolidatednetincome Other Adjustments to Consolidated Net Income / (Loss)
otheradjustmentstonetincometocommon Other Adjustments to Net Income / (Loss) Attributable to Common Shareholders)
otherassets Other Assets
othercostofrevenue Other Cost of Revenue
othercurrentassets Other Current Assets
othercurrentliabilities Other Current Liabilities
othercurrentnonoperatingassets Other Current Nonoperating Assets
othercurrentnonoperatingliabilities Other Current Nonoperating Liabilities
otherequity Other Equity Adjustments
otherfinancingactivitiesnet Other Financing Activities, Net
othergains Other Gains / (Losses), net
otherincome Other Income / (Expense), net
otherinterestexpense Other Interest Expense
otherinterestincome Other Interest Income
otherinvestingactivitiesnet Other Investing Activities, net
otherlongtermliabilities Other Long-Term Liabilities
othernetchangesincash Other Net Changes in Cash
othernoncurrentassets Other Noncurrent Operating Assets
othernoncurrentliabilities Other Noncurrent Operating Liabilities
othernoncurrentnonoperatingassets Other Noncurrent Nonoperating Assets
othernoncurrentnonoperatingliabilities Other Noncurrent Nonoperating Liabilities
othernoninterestincome Other Non-Interest Income
otheroperatingexpenses Other Operating Expenses
otherrevenue Other Revenue
otherservicechargeincome Other Service Charges
othershorttermpayables Other Short-Term Payables
otherspecialcharges Other Special Charges
othertaxespayable Other Taxes Payable
participatingpolicyholderequity Participating Policy Holder Equity
paymentofdividends Payment of Dividends
policyacquisitioncosts Insurance Policy Acquisition Costs
policyholderfunds Policy Holder Funds
preferreddividends Preferred Stock Dividends Declared
premiumsearned Premiums Earned
prepaidexpenses Prepaid Expenses
propertyliabilityinsuranceclaims Property & Liability Insurance Claims
provisionforcreditlosses Provision for Credit Losses
provisionforloanlosses Provision For Loan Losses
purchaseofinvestments Purchase of Investments
purchaseofplantpropertyandequipment Purchase of Property, Plant & Equipment
rdexpense Research & Development Expense
redeemablenoncontrollinginterest Redeemable Noncontrolling Interest
repaymentofdebt Repayment of Debt
repurchaseofcommonequity Repurchase of Common Equity
repurchaseofpreferredequity Repurchase of Preferred Equity
restrictedcash Restricted Cash
restructuringcharge Restructuring Charge
retainedearnings Retained Earnings
salariesandemployeebenefitsexpense Salaries and Employee Benefits
saleofinvestments Sale and/or Maturity of Investments
saleofplantpropertyandequipment Sale of Property, Plant & Equipment
separateaccountbusinessassets Separate Account Business Assets
separateaccountbusinessliabilities Separate Account Business Liabilities
servicechargesondepositsincome Service Charges on Deposit Accounts
sgaexpense Selling, General & Admin Expense
shorttermborrowinginterestexpense Short-Term Borrowings Interest Expense
shorttermdebt Short-Term Debt
shortterminvestments Short-Term Investments
timedepositsplaced Time Deposits Placed and Other Short-Term Investments
totalassets Total Assets
totalcommonequity Total Common Equity
totalcostofrevenue Total Cost of Revenue
totalcurrentassets Total Current Assets
totalcurrentliabilities Total Current Liabilities
totalequity Total Preferred & Common Equity
totalequityandnoncontrollinginterests Total Equity & Noncontrolling Interests
totalgrossprofit Total Gross Profit
totalinterestexpense Total Interest Expense
totalinterestincome Total Interest Income
totalliabilities Total Liabilities
totalliabilitiesandequity Total Liabilities & Shareholders’ Equity
totalnoninterestexpense Total Non-Interest Expense
totalnoninterestincome Total Non-Interest Income
totaloperatingexpenses Total Operating Expenses
totaloperatingincome Total Operating Income
totalotherincome Total Other Income / (Expense), net
totalpretaxincome Total Pre-Tax Income
totalrevenue Total Revenue
tradingaccountinterestincome Trading Account Interest Income
trustfeeincome Trust Fees by Commissions
weightedavebasicdilutedsharesos Weighted Average Basic & Diluted Shares Outstanding
weightedavebasicsharesos Weighted Average Basic Shares Outstanding

Financial Statement Line Sequence

With the FinancialStatementLines listed, we can now order them as they would generally appear on FinancialStatements by creating FinancialStatementLineSequences.

Commercial Firm Income Statement Line Sequence

Tag Sequence
operatingrevenue 1
otherrevenue 2
totalrevenue 3
operatingcostofrevenue 4
othercostofrevenue 5
totalcostofrevenue 6
totalgrossprofit 7
sgaexpense 8
marketingexpense 9
rdexpense 10
explorationexpense 11
depreciationexpense 12
amortizationexpense 13
depletionexpense 14
otheroperatingexpenses 15
impairmentexpense 16
restructuringcharge 17
otherspecialcharges 18
totaloperatingexpenses 19
totaloperatingincome 20
totalinterestexpense 21
totalinterestincome 22
otherincome 23
totalotherincome 24
totalpretaxincome 25
incometaxexpense 26
othergains 27
netincomecontinuing 28
netincomediscontinued 29
extraordinaryincome 30
otheradjustmentstoconsolidatednetincome 31
netincome 32
preferreddividends 33
netincometononcontrollinginterest 34
otheradjustmentstonetincometocommon 35
netincometocommon 36
weightedavebasicsharesos 37
basiceps 38
dilutedeps 40
weightedavebasicdilutedsharesos 41
basicdilutedeps 42
cashdividendspershare 43

Financial Firm Income Statement Line Sequence

Tag Sequence
loansandleaseinterestincome 1
investmentsecuritiesinterestincome 2
depositsinterestincome 3
fedfundsandrepointerestincome 4
tradingaccountinterestincome 5
otherinterestincome 6
totalinterestincome 7
depositsinterestexpense 8
shorttermborrowinginterestexpense 9
longtermdebtinterestexpense 10
fedfundsandrepointerestexpense 11
capitalizedleaseobligationinterestexpense 12
otherinterestexpense 13
totalinterestexpense 14
netinterestincome 15
trustfeeincome 16
servicechargesondepositsincome 17
otherservicechargeincome 18
netrealizedcapitalgains 19
premiumsearned 20
investmentbankingincome 21
othernoninterestincome 22
totalnoninterestincome 23
totalrevenue 24
provisionforcreditlosses 25
salariesandemployeebenefitsexpense 26
netoccupancyequipmentexpense 27
marketingexpense 28
propertyliabilityinsuranceclaims 29
policyacquisitioncosts 30
amortizationofdeferredpolicyacquisitioncosts 31
currentandfuturebenefits 32
otheroperatingexpenses 33
depreciationexpense 34
amortizationexpense 35
impairmentexpense 36
restructuringcharge 37
otherspecialcharges 38
totalnoninterestexpense 39
nonoperatingincome 40
totalpretaxincome 41
incometaxexpense 42
othergains 43
netincomecontinuing 44
netincomediscontinued 45
extraordinaryincome 46
otheradjustmentstoconsolidatednetincome 47
netincome 48
preferreddividends 49
netincometononcontrollinginterest 50
otheradjustmentstonetincometocommon 51
netincometocommon 52
weightedavebasicsharesos 53
basiceps 54
dilutedeps 56
weightedavebasicdilutedsharesos 57
basicdilutedeps 58
cashdividendspershare 59

Commercial Firm Balance Sheet Line Sequence

Tag Sequence
cashandequivalents 1
restrictedcash 2
shortterminvestments 3
notereceivable 4
accountsreceivable 5
netinventory 6
prepaidexpenses 7
currentdeferredtaxassets 8
othercurrentassets 9
othercurrentnonoperatingassets 10
totalcurrentassets 11
grossppe 12
accumulateddepreciation 13
netppe 14
longterminvestments 15
noncurrentnotereceivables 16
goodwill 17
intangibleassets 18
noncurrentdeferredtaxassets 19
employeebenefitassets 20
othernoncurrentassets 21
othernoncurrentnonoperatingassets 22
totalassets 24
shorttermdebt 25
accountspayable 26
accruedexpenses 27
customerdeposits 28
dividendspayable 29
currentdeferredrevenue 30
currentdeferredtaxliabilities 31
currentemployeebenefitliabilities 32
othertaxespayable 33
othercurrentliabilities 34
othercurrentnonoperatingliabilities 35
totalcurrentliabilities 36
longtermdebt 37
capitalleaseobligations 38
assetretirementandlitigationobligation 39
noncurrentdeferredrevenue 40
noncurrentdeferredtaxliabilities 41
noncurrentemployeebenefitliabilities 42
othernoncurrentliabilities 43
othernoncurrentnonoperatingliabilities 44
totalliabilities 46
commitmentsandcontingencies 47
redeemablenoncontrollinginterest 48
commonequity 50
retainedearnings 51
aoci 53
otherequity 54
totalcommonequity 55
totalequity 56
noncontrollinginterests 57
totalequityandnoncontrollinginterests 58
totalliabilitiesandequity 59

Financial Firm Balance Sheet Line Sequence

Tag Sequence
cashandequivalents 1
restrictedcash 2
fedfundssold 3
interestbearingdepositsatotherbanks 4
timedepositsplaced 5
loansandleases 7
allowanceforloanandleaselosses 8
netloansandleases 9
loansheldforsale 10
accruedinvestmentincome 11
customerandotherreceivables 12
netpremisesandequipment 13
mortgageservicingrights 14
deferredacquisitioncost 16
separateaccountbusinessassets 17
goodwill 18
intangibleassets 19
otherassets 20
totalassets 21
noninterestbearingdeposits 22
interestbearingdeposits 23
fedfundspurchased 24
shorttermdebt 25
bankersacceptances 26
accruedinterestpayable 27
othershorttermpayables 28
longtermdebt 29
capitalleaseobligations 30
claimsandclaimexpenses 31
futurepolicybenefits 32
policyholderfunds 34
participatingpolicyholderequity 35
separateaccountbusinessliabilities 36
otherlongtermliabilities 37
totalliabilities 38
commitmentsandcontingencies 39
redeemablenoncontrollinginterest 40
commonequity 42
retainedearnings 43
aoci 45
otherequity 46
totalcommonequity 47
totalequity 48
noncontrollinginterests 49
totalequityandnoncontrollinginterests 50

Commercial Firm Cash Flow Statement Line Sequence

Tag Sequence
netincome 1
netincomediscontinued 2
netincomecontinuing 3
depreciationexpense 4
amortizationexpense 5
noncashadjustmentstonetincome 6
increasedecreaseinoperatingcapital 7
netcashfromcontinuingoperatingactivities 8
netcashfromdiscontinuedoperatingactivities 9
netcashfromoperatingactivities 10
purchaseofplantpropertyandequipment 11
acquisitions 12
purchaseofinvestments 13
saleofplantpropertyandequipment 14
divestitures 15
saleofinvestments 16
otherinvestingactivitiesnet 17
netcashfromcontinuinginvestingactivities 18
netcashfromdiscontinuedinvestingactivities 19
netcashfrominvestingactivities 20
repaymentofdebt 21
repurchaseofpreferredequity 22
repurchaseofcommonequity 23
paymentofdividends 24
issuanceofdebt 25
issuanceofpreferredequity 26
issuanceofcommonequity 27
otherfinancingactivitiesnet 28
netcashfromcontinuingfinancingactivities 29
netcashfromdiscontinuedfinancingactivities 30
netcashfromfinancingactivities 31
effectofexchangeratechanges 32
othernetchangesincash 33
netchangeincash 34
cashinterestpaid 35
cashinterestreceived 36
cashincometaxespaid 37

Financial Firm Cash Flow Statement Line Sequence

Tag Sequence
netincome 1
netincomediscontinued 2
netincomecontinuing 3
provisionforloanlosses 4
depreciationexpense 5
amortizationexpense 6
noncashadjustmentstonetincome 7
increasedecreaseinoperatingcapital 8
netcashfromcontinuingoperatingactivities 9
netcashfromdiscontinuedoperatingactivities 10
netcashfromoperatingactivities 11
purchaseofplantpropertyandequipment 12
acquisitions 13
purchaseofinvestments 14
saleofplantpropertyandequipment 15
divestitures 16
saleofinvestments 17
netincreaseinfedfundssold 18
loansheldforsalenet 19
otherinvestingactivitiesnet 20
netcashfromcontinuinginvestingactivities 21
netcashfromdiscontinuedinvestingactivities 22
netcashfrominvestingactivities 23
netchangeindeposits 24
issuanceofdebt 25
issuanceofpreferredequity 26
issuanceofcommonequity 27
repaymentofdebt 28
repurchaseofpreferredequity 29
repurchaseofcommonequity 30
paymentofdividends 31
otherfinancingactivitiesnet 32
netcashfromcontinuingfinancingactivities 33
netcashfromdiscontinuedfinancingactivities 34
netcashfromfinancingactivities 35
effectofexchangeratechanges 36
othernetchangesincash 37
netchangeincash 38
cashinterestpaid 39
cashinterestreceived 40
cashincometaxespaid 41

The SQLAlchemy Code

The only thing that is new that I haven’t addressed in previous posts is the listens_for event using the decorator style. This listens for the after_create event and runs the function we give it.

@listens_for(FinancialStatementLine.__table__, 'after_create')
def insert_initial_values(*args, **kwargs):
    # insert logic here
import pandas as pd
from sqlalchemy.event import listens_for
from sqlalchemy import Column, ForeignKey, String, \
                       Integer, Float, Date
from sqlalchemy.orm import relationship
from sqlalchemy import Enum, UniqueConstraint
import enum
from setup_psql_environment import Base, session, db
from models.security import Security


class Company(Base):
    __tablename__ = 'company'
    id = Column(Integer, primary_key=True)
    name = Column('name', String(100), nullable=False)
    cik = Column('cik', String(10))
    description = Column('description', String(2000))
    company_url = Column('company_url', String(100))
    sic = Column('sic', String(4))
    employees = Column('employees', Integer)
    sector = Column('sector', String(200))
    industry_category = Column('industry_category', String(200))
    industry_group = Column('industry_group', String(200))
    security = relationship('Security', back_populates='company')


class FinancialStatementType(enum.Enum):
    balance_sheet = 'balance sheet'
    income_statement = 'income statement'
    cash_flow_statement = 'cash flow statement'


class FinancialStatementPeriod(enum.Enum):
    fy = 'fy'
    q1 = 'q1'
    q2 = 'q2'
    q3 = 'q3'
    q4 = 'q4'


class FinancialStatement(Base):
    __tablename__ = 'financial_statement'
    id = Column(Integer, primary_key=True)
    name = Column('name', String(100), nullable=False)


class FinancialStatementLine(Base):
    __tablename__ = 'financial_statement_line'
    id = Column(Integer, primary_key=True)
    tag = Column(String(50), nullable=False, unique=True)
    name = Column('name', String(100), nullable=False, unique=True)
    description = Column('description', String(1000))
    sequences = relationship('FinancialStatementLineSequence', backref='line')
    facts = relationship('FinancialStatementFact', backref='line')


class FinancialStatementLineSequence(Base):
    __tablename__ = 'financial_statement_line_sequence'
    id = Column(Integer, primary_key=True)
    sequence = Column('sequence', Integer, nullable=False)
    financial_statement_id = Column(Integer,
                                    ForeignKey('financial_statement.id',
                                               onupdate='CASCADE',
                                               ondelete='CASCADE'),
                                    nullable=False)
    financial_statement_line_id = Column(Integer,
                                         ForeignKey('financial_statement_line.id',
                                                    onupdate='CASCADE',
                                                    ondelete='CASCADE'),
                                         nullable=False)
    UniqueConstraint('financial_statement_id',
                     'financial_statement_line_id')
    financial_statement = relationship('FinancialStatement')
    financial_statement_line = relationship('FinancialStatementLine')


class FinancialStatementLineAlias(Base):
    __tablename__ = 'financial_statement_line_alias'
    id = Column(Integer, primary_key=True)
    alias = Column(String(200), nullable=False, unique=True)
    financial_statement_line_id = Column(Integer,
                                         ForeignKey('financial_statement_line.id',
                                                    onupdate='CASCADE',
                                                    ondelete='CASCADE'),
                                         nullable=False)
    financial_statement_line = relationship('FinancialStatementLine')


class FinancialStatementFact(Base):
    __tablename__ = 'financial_statement_fact'
    __table_args__ = tuple(
        [UniqueConstraint('company_id',
                          'financial_statement_line_id',
                          'fiscal_year',
                          'fiscal_period')])
    id = Column(Integer, primary_key=True)
    fiscal_year = Column('fiscal_year', Integer, nullable=False)
    fiscal_period = Column('fiscal_period',
                           Enum(FinancialStatementPeriod),
                           nullable=False)
    filing_date = Column('filing_date', Date, nullable=False)
    start_date = Column('start_date', Date)
    end_date = Column('end_date', Date, nullable=False)
    amount = Column('amount', Float, nullable=False)
    company_id = Column(Integer,
                        ForeignKey('company.id',
                                   onupdate='CASCADE',
                                   ondelete='CASCADE'),
                        nullable=False)
    financial_statement_line_id = Column(
                                    Integer,
                                    ForeignKey('financial_statement_line.id',
                                               onupdate='CASCADE',
                                               ondelete='CASCADE'),
                                    nullable=False)

    company = relationship('Company')

commercial_income_statement = FinancialStatement(name='Commercial Income Statement')
commercial_balance_sheet = FinancialStatement(name='Commercial Balance Sheet Statement')
commercial_cash_flow_statement = FinancialStatement(name='Commercial Cash Flow Statement')

financial_income_statement = FinancialStatement(name='Financial Income Statement')
financial_balance_sheet = FinancialStatement(name='Financial Balance Sheet Statement')
financial_cash_flow_statement = FinancialStatement(name='Financial Cash Flow Statement')


@listens_for(FinancialStatement.__table__, 'after_create')
def insert_initial_values(*args, **kwargs):
    session.add(commercial_income_statement)
    session.add(commercial_balance_sheet)
    session.add(commercial_cash_flow_statement)
    session.add(financial_income_statement)
    session.add(financial_balance_sheet)
    session.add(financial_cash_flow_statement)
    session.commit()

@listens_for(FinancialStatementLine.__table__, 'after_create')
def insert_initial_values(*args, **kwargs):
    financial_statement_lines = pd.read_csv('financial_statements_lines.csv')
    financial_statement_lines = financial_statement_lines[['tag', 'name']].drop_duplicates('tag')

    for index, line in financial_statement_lines.iterrows():
        session.add(FinancialStatementLine(tag=line['tag'], name=line['name']))
    session.commit()        

@listens_for(FinancialStatementLineSequence.__table__, 'after_create')
def insert_initial_values(*args, **kwargs):
    financial_statement_lines = pd.read_csv('financial_statements_lines.csv')
    statement_types = ['commercial', 'financial']
    statement_codes = ['income_statement', 'balance_sheet_statement', 'cash_flow_statement']
    
    for statement_type in statement_types:
        for statement_code in statement_codes:
            statement_name = (statement_type + ' ' + statement_code.replace('_',' ')).title()
            statement = session.query(FinancialStatement) \
                .filter(FinancialStatement.name == statement_name).one()
            financial_statement_sequence = financial_statement_lines[
                (financial_statement_lines['statement_type'] == statement_type) & \
                (financial_statement_lines['statement_code'] == statement_code)]
            
            for index, row in financial_statement_sequence.iterrows():
                line = session.query(FinancialStatementLine) \
                    .filter(FinancialStatementLine.tag == row['tag']).one()
                session.add(FinancialStatementLineSequence(sequence=row['sequence'],
                                                        financial_statement_id=statement.id,
                                                        financial_statement_line_id=line.id))
    session.commit()

Example SQL Query

SELECT financial_statement_line.name FROM financial_statement_line
JOIN financial_statement_line_sequence
ON financial_statement_line.id = financial_statement_line_sequence.financial_statement_line_id
JOIN financial_statement
ON financial_statement.id = financial_statement_line_sequence.financial_statement_id
WHERE financial_statement.name = 'Commercial Income Statement'
ORDER BY financial_statement_line_sequence.sequence;

Commercial Income Statement

Name
Operating Revenue
Total Revenue
Operating Cost of Revenue
Other Cost of Revenue
Total Cost of Revenue
Total Gross Profit
Selling, General & Admin Expense
Marketing Expense
Research & Development Expense
Exploration Expense
Depreciation Expense
Amortization Expense
Depletion Expense
Other Operating Expenses
Impairment Charge
Restructuring Charge
Other Special Charges
Total Operating Expenses
Total Operating Income
Total Interest Expense
Total Interest Income
Other Income / (Expense), net
Total Other Income / (Expense), net
Total Pre-Tax Income
Income Tax Expense
Other Gains / (Losses), net
Net Income / (Loss) Continuing Operations
Net Income / (Loss) Discontinued Operations
Extraordinary Income / (Loss), net
Other Adjustments to Consolidated Net Income / (Loss)
Consolidated Net Income / (Loss)
Preferred Stock Dividends Declared
Net Income / (Loss) Attributable to Noncontrolling Interest
Other Adjustments to Net Income / (Loss) Attributable to Common Shareholders)
Net Income / (Loss) Attributable to Common Shareholders
Weighted Average Basic Shares Outstanding
Basic Earnings per Share
Diluted Earnings per Share

Additional Resources

leo

Leo Smigel

Based in Pittsburgh, Analyzing Alpha is a blog by Leo Smigel exploring what works in the markets.