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 |