战雄电竞|战雄电竞直播ios下载官方|战雄电竞比赛排名手机版

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

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

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

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