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 will 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.

And if you’re just here for the code:

Understanding 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 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.

If needed, companies can amend these reports, which opens up the potential for look-ahead bias. We’ll need to add the reporting date for each financial statement report to solve this challenge. 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 to 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 Standardization

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.

Differences also occur for the same report depending upon where you source the data. Please compare Google 2019 results with Yahoo’s version, shown again for convenience. For example, Yahoo breaks out Interest Expenses 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.

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 the Financial Statement Terms:

Valuation Data

NameIntrinio Tag
Revenuestotalrevenue
Research and Development Expenserdexpense
Operating Incometotaloperatingincome
Interest Expensetotalinterestexpense
Book Value of Equitytotalequityandnoncontrollinginterests
Book Value of Debtshortermdebt + longtermdebt
Cash & Marketable Securitiescashandequivalents + shortterminvestments
Cross Holdings & Other Non-Operating Assetslongterminvestments
Minority Interestsnoncontrollinginterests
Effective tax ratenormalized incometaxexpense / totalpretaxincome
Marginal tax ratehttp://pages.stern.nyu.edu/~adamodar/New_Home_Page/datafile/countrytaxrate.html
Non-Cash Working Capitaltotalcurrentassets – totalcurrentassets – cashandequivalents – shortermdebt
Current stock price(In database already)
Shares outstandingweightedavebasicsharesos + RSUs

Pricing Data

NameIntrinio Tag
Revenuestotalrevenue
Research and Development Expenserdexpense
Operating Incometotaloperatingincome
Interest Expensetotalinterestexpense
Book Value of Equitytotalequityandnoncontrollinginterests
Book Value of Debtshortermdebt + longtermdebt
Cash & Marketable Securitiescashandequivalents + shortterminvestments
Cross Holdings & Other Non-Operating Assetslongterminvestments
Minority Interestsnoncontrollinginterests
Effective tax ratenormalized incometaxexpense / totalpretaxincome
Marginal tax ratehttp://pages.stern.nyu.edu/~adamodar/New_Home_Page/datafile/countrytaxrate.html
Non-Cash Working Capitaltotalcurrentassets – totalcurrentassets – cashandequivalents – shortermdebt
Current stock price(In database already)
Shares outstandingweightedavebasicsharesos + RSUs

Pricing Data

MultipleFormula
p/eprice / ttm earnings
ev/ebitdaev (current) / ttm ebitda
p/bprice / ttm book value
p/sprice / ttm sales

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

Database Design

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

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

If you’re interested in the full financials, 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:

FinancialStatementLine is a single line item on a FinancialStatement. An example would be the 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.

TagName
accountspayableAccounts Payable
accountsreceivableAccounts Receivable
accruedexpensesAccrued Expenses
accruedinterestpayableAccrued Interest Payable
accruedinvestmentincomeAccrued Investment Income
accumulateddepreciationAccumulated Depreciation
acquisitionsAcquisitions
allowanceforloanandleaselossesAllowance for Loan and Lease Losses
amortizationexpenseAmortization Expense
amortizationofdeferredpolicyacquisitioncostsAmortization of Deferred Policy Acquisition Costs
aociAccumulated Other Comprehensive Income / (Loss)
assetretirementandlitigationobligationAsset Retirement Reserve & Litigation Obligation
bankersacceptancesBankers Acceptance Outstanding
basicdilutedepsBasic & Diluted Earnings per Share
basicepsBasic Earnings per Share
capitalizedleaseobligationinterestexpenseCapitalized Lease Obligations Interest Expense
capitalleaseobligationsCapital Lease Obligations
cashandequivalentsCash & Equivalents
cashdividendspershareCash Dividends to Common per Share
cashincometaxespaidCash Income Taxes Paid
cashinterestpaidCash Interest Paid
cashinterestreceivedCash Interest Received
claimsandclaimexpensesClaims and Claim Expense
commitmentsandcontingenciesCommitments & Contingencies
commonequityCommon Stock
currentandfuturebenefitsCurrent and Future Benefits
currentdeferredrevenueCurrent Deferred Revenue
currentdeferredtaxassetsCurrent Deferred & Refundable Income Taxes
currentdeferredtaxliabilitiesCurrent Deferred & Payable Income Tax Liabilities
currentemployeebenefitliabilitiesCurrent Employee Benefit Liabilities
customerandotherreceivablesCustomer and Other Receivables
customerdepositsCustomer Deposits
deferredacquisitioncostDeferred Acquisition Cost
depletionexpenseDepletion Expense
depositsinterestexpenseDeposits Interest Expense
depositsinterestincomeDeposits and Money Market Investments Interest Income
depreciationexpenseDepreciation Expense
dilutedepsDiluted Earnings per Share
divestituresDivestitures
dividendspayableDividends Payable
effectofexchangeratechangesEffect of Exchange Rate Changes
employeebenefitassetsEmployee Benefit Assets
explorationexpenseExploration Expense
extraordinaryincomeExtraordinary Income / (Loss), net
fedfundsandrepointerestexpenseFederal Funds Purchased and Securities Sold Interest Expense
fedfundsandrepointerestincomeFederal Funds Sold and Securities Borrowed Interest Income
fedfundspurchasedFederal Funds Purchased and Securities Sold
fedfundssoldFederal Funds Sold
futurepolicybenefitsFuture Policy Benefits
goodwillGoodwill
grossppePlant, Property & Equipment, gross
impairmentexpenseImpairment Charge
incometaxexpenseIncome Tax Expense
increasedecreaseinoperatingcapitalChanges in Operating Assets and Liabilities, net
intangibleassetsIntangible Assets
interestbearingdepositsInterest Bearing Deposits
interestbearingdepositsatotherbanksInterest Bearing Deposits at Other Banks
investmentbankingincomeInvestment Banking Income
investmentsecuritiesinterestincomeInvestment Securities Interest Income
issuanceofcommonequityIssuance of Common Equity
issuanceofdebtIssuance of Debt
issuanceofpreferredequityIssuance of Preferred Equity
loansandleaseinterestincomeLoans and Leases Interest Income
loansandleasesLoans and Leases
loansheldforsaleLoans Held for Sale
loansheldforsalenetLoans Held for Sale, Net
longtermdebtLong-Term Debt
longtermdebtinterestexpenseLong-Term Debt Interest Expense
longterminvestmentsLong-Term Investments
marketingexpenseMarketing Expense
mortgageservicingrightsMortgage Servicing Rights
netcashfromcontinuingfinancingactivitiesNet Cash From Continuing Financing Activities
netcashfromcontinuinginvestingactivitiesNet Cash From Continuing Investing Activities
netcashfromcontinuingoperatingactivitiesNet Cash From Continuing Operating Activities
netcashfromdiscontinuedfinancingactivitiesNet Cash From Discontinued Financing Activities
netcashfromdiscontinuedinvestingactivitiesNet Cash From Discontinued Investing Activities
netcashfromdiscontinuedoperatingactivitiesNet Cash From Discontinued Operating Activities
netcashfromfinancingactivitiesNet Cash From Financing Activities
netcashfrominvestingactivitiesNet Cash From Investing Activities
netcashfromoperatingactivitiesNet Cash From Operating Activities
netchangeincashNet Change in Cash & Equivalents
netchangeindepositsNet Change in Deposits
netincomeConsolidated Net Income / (Loss)
netincomecontinuingNet Income / (Loss) Continuing Operations
netincomediscontinuedNet Income / (Loss) Discontinued Operations
netincometocommonNet Income / (Loss) Attributable to Common Shareholders
netincometononcontrollinginterestNet Income / (Loss) Attributable to Noncontrolling Interest
netincreaseinfedfundssoldNet Increase in Fed Funds Sold
netinterestincomeNet Interest Income / (Expense)
netinventoryInventories, net
netloansandleasesLoans and Leases, Net of Allowance
netoccupancyequipmentexpenseNet Occupancy & Equipment Expense
netppePlant, Property, & Equipment, net
netpremisesandequipmentPremises and Equipment, Net
netrealizedcapitalgainsNet Realized & Unrealized Capital Gains on Investments
noncashadjustmentstonetincomeNon-Cash Adjustments to Reconcile Net Income
noncontrollinginterestsNoncontrolling Interest
noncurrentdeferredrevenueNoncurrent Deferred Revenue
noncurrentdeferredtaxassetsNoncurrent Deferred & Refundable Income Taxes
noncurrentdeferredtaxliabilitiesNoncurrent Deferred & Payable Income Tax Liabilities
noncurrentemployeebenefitliabilitiesNoncurrent Employee Benefit Liabilities
noncurrentnotereceivablesNoncurrent Note & Lease Receivables
noninterestbearingdepositsNon-Interest Bearing Deposits
nonoperatingincomeNonoperating Income / (Expense), net
notereceivableNote & Lease Receivable
operatingcostofrevenueOperating Cost of Revenue
operatingrevenueOperating Revenue
otheradjustmentstoconsolidatednetincomeOther Adjustments to Consolidated Net Income / (Loss)
otheradjustmentstonetincometocommonOther Adjustments to Net Income / (Loss) Attributable to Common Shareholders)
otherassetsOther Assets
othercostofrevenueOther Cost of Revenue
othercurrentassetsOther Current Assets
othercurrentliabilitiesOther Current Liabilities
othercurrentnonoperatingassetsOther Current Nonoperating Assets
othercurrentnonoperatingliabilitiesOther Current Nonoperating Liabilities
otherequityOther Equity Adjustments
otherfinancingactivitiesnetOther Financing Activities, Net
othergainsOther Gains / (Losses), net
otherincomeOther Income / (Expense), net
otherinterestexpenseOther Interest Expense
otherinterestincomeOther Interest Income
otherinvestingactivitiesnetOther Investing Activities, net
otherlongtermliabilitiesOther Long-Term Liabilities
othernetchangesincashOther Net Changes in Cash
othernoncurrentassetsOther Noncurrent Operating Assets
othernoncurrentliabilitiesOther Noncurrent Operating Liabilities
othernoncurrentnonoperatingassetsOther Noncurrent Nonoperating Assets
othernoncurrentnonoperatingliabilitiesOther Noncurrent Nonoperating Liabilities
othernoninterestincomeOther Non-Interest Income
otheroperatingexpensesOther Operating Expenses
otherrevenueOther Revenue
otherservicechargeincomeOther Service Charges
othershorttermpayablesOther Short-Term Payables
otherspecialchargesOther Special Charges
othertaxespayableOther Taxes Payable
participatingpolicyholderequityParticipating Policy Holder Equity
paymentofdividendsPayment of Dividends
policyacquisitioncostsInsurance Policy Acquisition Costs
policyholderfundsPolicy Holder Funds
preferreddividendsPreferred Stock Dividends Declared
premiumsearnedPremiums Earned
prepaidexpensesPrepaid Expenses
propertyliabilityinsuranceclaimsProperty & Liability Insurance Claims
provisionforcreditlossesProvision for Credit Losses
provisionforloanlossesProvision For Loan Losses
purchaseofinvestmentsPurchase of Investments
purchaseofplantpropertyandequipmentPurchase of Property, Plant & Equipment
rdexpenseResearch & Development Expense
redeemablenoncontrollinginterestRedeemable Noncontrolling Interest
repaymentofdebtRepayment of Debt
repurchaseofcommonequityRepurchase of Common Equity
repurchaseofpreferredequityRepurchase of Preferred Equity
restrictedcashRestricted Cash
restructuringchargeRestructuring Charge
retainedearningsRetained Earnings
salariesandemployeebenefitsexpenseSalaries and Employee Benefits
saleofinvestmentsSale and/or Maturity of Investments
saleofplantpropertyandequipmentSale of Property, Plant & Equipment
separateaccountbusinessassetsSeparate Account Business Assets
separateaccountbusinessliabilitiesSeparate Account Business Liabilities
servicechargesondepositsincomeService Charges on Deposit Accounts
sgaexpenseSelling, General & Admin Expense
shorttermborrowinginterestexpenseShort-Term Borrowings Interest Expense
shorttermdebtShort-Term Debt
shortterminvestmentsShort-Term Investments
timedepositsplacedTime Deposits Placed and Other Short-Term Investments
totalassetsTotal Assets
totalcommonequityTotal Common Equity
totalcostofrevenueTotal Cost of Revenue
totalcurrentassetsTotal Current Assets
totalcurrentliabilitiesTotal Current Liabilities
totalequityTotal Preferred & Common Equity
totalequityandnoncontrollinginterestsTotal Equity & Noncontrolling Interests
totalgrossprofitTotal Gross Profit
totalinterestexpenseTotal Interest Expense
totalinterestincomeTotal Interest Income
totalliabilitiesTotal Liabilities
totalliabilitiesandequityTotal Liabilities & Shareholders’ Equity
totalnoninterestexpenseTotal Non-Interest Expense
totalnoninterestincomeTotal Non-Interest Income
totaloperatingexpensesTotal Operating Expenses
totaloperatingincomeTotal Operating Income
totalotherincomeTotal Other Income / (Expense), net
totalpretaxincomeTotal Pre-Tax Income
totalrevenueTotal Revenue
tradingaccountinterestincomeTrading Account Interest Income
trustfeeincomeTrust Fees by Commissions
weightedavebasicdilutedsharesosWeighted Average Basic & Diluted Shares Outstanding
weightedavebasicsharesosWeighted 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

TagSequence
operatingrevenue1
otherrevenue2
totalrevenue3
operatingcostofrevenue4
othercostofrevenue5
totalcostofrevenue6
totalgrossprofit7
sgaexpense8
marketingexpense9
rdexpense10
explorationexpense11
depreciationexpense12
amortizationexpense13
depletionexpense14
otheroperatingexpenses15
impairmentexpense16
restructuringcharge17
otherspecialcharges18
totaloperatingexpenses19
totaloperatingincome20
totalinterestexpense21
totalinterestincome22
otherincome23
totalotherincome24
totalpretaxincome25
incometaxexpense26
othergains27
netincomecontinuing28
netincomediscontinued29
extraordinaryincome30
otheradjustmentstoconsolidatednetincome31
netincome32
preferreddividends33
netincometononcontrollinginterest34
otheradjustmentstonetincometocommon35
netincometocommon36
weightedavebasicsharesos37
basiceps38
dilutedeps40
weightedavebasicdilutedsharesos41
basicdilutedeps42
cashdividendspershare43

Financial Firm Income Statement Line Sequence

TagSequence
loansandleaseinterestincome1
investmentsecuritiesinterestincome2
depositsinterestincome3
fedfundsandrepointerestincome4
tradingaccountinterestincome5
otherinterestincome6
totalinterestincome7
depositsinterestexpense8
shorttermborrowinginterestexpense9
longtermdebtinterestexpense10
fedfundsandrepointerestexpense11
capitalizedleaseobligationinterestexpense12
otherinterestexpense13
totalinterestexpense14
netinterestincome15
trustfeeincome16
servicechargesondepositsincome17
otherservicechargeincome18
netrealizedcapitalgains19
premiumsearned20
investmentbankingincome21
othernoninterestincome22
totalnoninterestincome23
totalrevenue24
provisionforcreditlosses25
salariesandemployeebenefitsexpense26
netoccupancyequipmentexpense27
marketingexpense28
propertyliabilityinsuranceclaims29
policyacquisitioncosts30
amortizationofdeferredpolicyacquisitioncosts31
currentandfuturebenefits32
otheroperatingexpenses33
depreciationexpense34
amortizationexpense35
impairmentexpense36
restructuringcharge37
otherspecialcharges38
totalnoninterestexpense39
nonoperatingincome40
totalpretaxincome41
incometaxexpense42
othergains43
netincomecontinuing44
netincomediscontinued45
extraordinaryincome46
otheradjustmentstoconsolidatednetincome47
netincome48
preferreddividends49
netincometononcontrollinginterest50
otheradjustmentstonetincometocommon51
netincometocommon52
weightedavebasicsharesos53
basiceps54
dilutedeps56
weightedavebasicdilutedsharesos57
basicdilutedeps58
cashdividendspershare59

Commercial Firm Balance Sheet Line Sequence

TagSequence
cashandequivalents1
restrictedcash2
shortterminvestments3
notereceivable4
accountsreceivable5
netinventory6
prepaidexpenses7
currentdeferredtaxassets8
othercurrentassets9
othercurrentnonoperatingassets10
totalcurrentassets11
grossppe12
accumulateddepreciation13
netppe14
longterminvestments15
noncurrentnotereceivables16
goodwill17
intangibleassets18
noncurrentdeferredtaxassets19
employeebenefitassets20
othernoncurrentassets21
othernoncurrentnonoperatingassets22
totalassets24
shorttermdebt25
accountspayable26
accruedexpenses27
customerdeposits28
dividendspayable29
currentdeferredrevenue30
currentdeferredtaxliabilities31
currentemployeebenefitliabilities32
othertaxespayable33
othercurrentliabilities34
othercurrentnonoperatingliabilities35
totalcurrentliabilities36
longtermdebt37
capitalleaseobligations38
assetretirementandlitigationobligation39
noncurrentdeferredrevenue40
noncurrentdeferredtaxliabilities41
noncurrentemployeebenefitliabilities42
othernoncurrentliabilities43
othernoncurrentnonoperatingliabilities44
totalliabilities46
commitmentsandcontingencies47
redeemablenoncontrollinginterest48
commonequity50
retainedearnings51
aoci53
otherequity54
totalcommonequity55
totalequity56
noncontrollinginterests57
totalequityandnoncontrollinginterests58
totalliabilitiesandequity59

Financial Firm Balance Sheet Line Sequence

TagSequence
cashandequivalents1
restrictedcash2
fedfundssold3
interestbearingdepositsatotherbanks4
timedepositsplaced5
loansandleases7
allowanceforloanandleaselosses8
netloansandleases9
loansheldforsale10
accruedinvestmentincome11
customerandotherreceivables12
netpremisesandequipment13
mortgageservicingrights14
deferredacquisitioncost16
separateaccountbusinessassets17
goodwill18
intangibleassets19
otherassets20
totalassets21
noninterestbearingdeposits22
interestbearingdeposits23
fedfundspurchased24
shorttermdebt25
bankersacceptances26
accruedinterestpayable27
othershorttermpayables28
longtermdebt29
capitalleaseobligations30
claimsandclaimexpenses31
futurepolicybenefits32
policyholderfunds34
participatingpolicyholderequity35
separateaccountbusinessliabilities36
otherlongtermliabilities37
totalliabilities38
commitmentsandcontingencies39
redeemablenoncontrollinginterest40
commonequity42
retainedearnings43
aoci45
otherequity46
totalcommonequity47
totalequity48
noncontrollinginterests49
totalequityandnoncontrollinginterests50

Commercial Firm Cash Flow Statement Line Sequence

TagSequence
netincome1
netincomediscontinued2
netincomecontinuing3
depreciationexpense4
amortizationexpense5
noncashadjustmentstonetincome6
increasedecreaseinoperatingcapital7
netcashfromcontinuingoperatingactivities8
netcashfromdiscontinuedoperatingactivities9
netcashfromoperatingactivities10
purchaseofplantpropertyandequipment11
acquisitions12
purchaseofinvestments13
saleofplantpropertyandequipment14
divestitures15
saleofinvestments16
otherinvestingactivitiesnet17
netcashfromcontinuinginvestingactivities18
netcashfromdiscontinuedinvestingactivities19
netcashfrominvestingactivities20
repaymentofdebt21
repurchaseofpreferredequity22
repurchaseofcommonequity23
paymentofdividends24
issuanceofdebt25
issuanceofpreferredequity26
issuanceofcommonequity27
otherfinancingactivitiesnet28
netcashfromcontinuingfinancingactivities29
netcashfromdiscontinuedfinancingactivities30
netcashfromfinancingactivities31
effectofexchangeratechanges32
othernetchangesincash33
netchangeincash34
cashinterestpaid35
cashinterestreceived36
cashincometaxespaid37

Financial Firm Cash Flow Statement Line Sequence

TagSequence
netincome1
netincomediscontinued2
netincomecontinuing3
provisionforloanlosses4
depreciationexpense5
amortizationexpense6
noncashadjustmentstonetincome7
increasedecreaseinoperatingcapital8
netcashfromcontinuingoperatingactivities9
netcashfromdiscontinuedoperatingactivities10
netcashfromoperatingactivities11
purchaseofplantpropertyandequipment12
acquisitions13
purchaseofinvestments14
saleofplantpropertyandequipment15
divestitures16
saleofinvestments17
netincreaseinfedfundssold18
loansheldforsalenet19
otherinvestingactivitiesnet20
netcashfromcontinuinginvestingactivities21
netcashfromdiscontinuedinvestingactivities22
netcashfrominvestingactivities23
netchangeindeposits24
issuanceofdebt25
issuanceofpreferredequity26
issuanceofcommonequity27
repaymentofdebt28
repurchaseofpreferredequity29
repurchaseofcommonequity30
paymentofdividends31
otherfinancingactivitiesnet32
netcashfromcontinuingfinancingactivities33
netcashfromdiscontinuedfinancingactivities34
netcashfromfinancingactivities35
effectofexchangeratechanges36
othernetchangesincash37
netchangeincash38
cashinterestpaid39
cashinterestreceived40
cashincometaxespaid41

The SQLAlchemy Code

The only new thing 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

Leave a Comment