Fundamental Stock Data from AlphaVantage.jl

AlphaVantage recently made fundamental data for stocks available through their API and thanks to some new contributors to the AlphaVantage.jl Julia package you can now easily import this data into your Julia project.

Enjoy these types of posts? Then you should sign up for my newsletter.

This fundamental data describes the underlying business information about a company and is more fluid and open to interpretation than the stock price. I’ll run through each of the new functions and try and explain what data it returns.

The new data comes in through four different categories and functions:

Company Overview
Balance Sheet
Cash Flow
Earnings

plus a listing status to see what stocks are active.

The real value add though (if I do say so myself) comes from the ability to pull out the annual or quarterly time series of a metric of a stock easily and in a programatic manner. Using the metaprogramming capabilities of Julia I was able to generate hundreds of functions with just a for loop. Using these new functions you can now easily pull the quarterly revenue of Apple, cash flow from financing of Tesla or a timeseries of the current liabilities for Ford.

using AlphaVantage
using DataFrames, DataFramesMeta, Dates
using Plots

Listing Status

Firstly, we can get a list of stocks that are actively trading.

listingData = AlphaVantage.listing_status()
stocks = DataFrame(listingData[1], :auto)
rename!(stocks, Symbol.(vec(listingData[2])))
first(stocks, 5)

5 rows × 7 columns (omitted printing of 1 columns)

	symbol	name	exchange	assetType	ipoDate	delistingDate
	Any	Any	Any	Any	Any	Any
1	A	Agilent Technologies Inc	NYSE	Stock	1999-11-18	null
2	AA	Alcoa Corp	NYSE	Stock	2016-11-01	null
3	AAA	AAF First Priority CLO Bond ETF	NYSE ARCA	ETF	2020-09-09	null
4	AAAU	Goldman Sachs Physical Gold ETF	NYSE ARCA	ETF	2018-08-15	null
5	AACG	ATA Inc	NASDAQ	Stock	2008-01-29	null

Over 9000 stocks and ETF’s are listed. Which you can then do some simple sorting to look at the oldest listed stocks.

first(sort!(stocks, :ipoDate), 5)

5 rows × 7 columns (omitted printing of 1 columns)

	symbol	name	exchange	assetType	ipoDate	delistingDate
	Any	Any	Any	Any	Any	Any
1	BA	Boeing Company	NYSE	Stock	1962-01-02	null
2	CAT	Caterpillar Inc	NYSE	Stock	1962-01-02	null
3	DD	DuPont de Nemours Inc	NYSE	Stock	1962-01-02	null
4	DIS	Walt Disney Co (The)	NYSE	Stock	1962-01-02	null
5	GE	General Electric Company	NYSE	Stock	1962-01-02	null

When googling some of these stocks though, the IPO date doesn’t appear to be 100% correct. General Electric became a public company in 1896!

@where(stocks, :symbol .== "AAPL")

1 rows × 7 columns

	symbol	name	exchange	assetType	ipoDate	delistingDate	status
	Any	Any	Any	Any	Any	Any	Any
1	AAPL	Apple Inc	NASDAQ	Stock	1980-12-12	null	Active

They have correctly recorded Apple’s IPO date though, so it might just be something about older stocks, or something else I am missing.

Company Overview

The first new function is company_overview which does what it says on the tin.

co = AlphaVantage.company_overview("AAPL", datatype = "json")

Dict{String,Any} with 59 entries:
  "SharesOutstanding"          => "17102499840"
  "ExDividendDate"             => "2020-11-06"
  "52WeekLow"                  => "52.8225"
  "ReturnOnEquityTTM"          => "0.7369"
  "LatestQuarter"              => "2020-09-30"
  "200DayMovingAverage"        => "111.2946"
  "EVToEBITDA"                 => "27.9399"
  "RevenuePerShareTTM"         => "15.82"
  "Beta"                       => "1.2976"
  "Sector"                     => "Technology"
  "ForwardAnnualDividendYield" => "0.0062"
  "Exchange"                   => "NASDAQ"
  "PercentInsiders"            => "0.066"
  "QuarterlyEarningsGrowthYOY" => "-0.023"
  "Currency"                   => "USD"
  "EBITDA"                     => "77343997952"
  "ShortRatio"                 => "1"
  "DividendYield"              => "0.0062"
  "AnalystTargetPrice"         => "127.11"
  "DilutedEPSTTM"              => "3.28"
  "BookValue"                  => "3.849"
  "LastSplitDate"              => "2020-08-31"
  "SharesFloat"                => "16984460162"
  "PriceToSalesRatioTTM"       => "8.4207"
  "FullTimeEmployees"          => "147000"

Here we get a dictionary with 59 different metrics about the company. There are lots of different quantitate and qualitative values about the company in question and provides a useful overview.

Income Statement

The income statement summarises a companies revenues and expenses. In short it shows where the money was coming in (revenue) and where it was going out (expenses).

is = AlphaVantage.income_statement("AAPL", datatype = "json")

Dict{String,Any} with 3 entries:
  "annualReports"    => Any[Dict{String,Any}("incomeTaxExpense"=>"9680000000","…
  "symbol"           => "AAPL"
  "quarterlyReports" => Any[Dict{String,Any}("incomeTaxExpense"=>"2228000000","…

Both the annual and quarterly results come back. For the annual reports there are the last 5 years. For the quarterly reports, the last 21 quarters.

keys(is["annualReports"][1])

Base.KeySet for a Dict{String,Any} with 29 entries. Keys:
  "incomeTaxExpense"
  "reportedCurrency"
  "otherNonOperatingIncome"
  "minorityInterest"
  "discontinuedOperations"
  "incomeBeforeTax"
  "totalOtherIncomeExpense"
  "interestIncome"
  "researchAndDevelopment"
  "grossProfit"
  "totalRevenue"
  "otherOperatingExpense"
  "taxProvision"
  "extraordinaryItems"
  "ebit"
  "otherItems"
  "netIncomeApplicableToCommonShares"
  "totalOperatingExpense"
  "costOfRevenue"
  "fiscalDateEnding"
  "interestExpense"
  "sellingGeneralAdministrative"
  "operatingIncome"
  "netIncomeFromContinuingOperations"
  "netIncome"
  ⋮

extrema(Date.(get.(is["annualReports"], "fiscalDateEnding", "")))

(Date("2016-09-30"), Date("2020-09-30"))

extrema(Date.(get.(is["quarterlyReports"], "fiscalDateEnding", "")))

(Date("2015-09-30"), Date("2020-09-30"))

Then what I have done is written the functions that allow you to extract any of the fields on a quarterly or annual basis. Which means you can easily plot some graphs and summarise the results.

totalRevenue = AlphaVantage.totalRevenue_quarterlys("AAPL", datatype =
"json")
plot(Date.(totalRevenue[:Date]), 
     parse.(Float64, totalRevenue[:totalRevenue]) ./ 1e9, 
     label = "Revenue (billions)",
     title = "Apple")

Apple Total Revenue

Here we have Apple quarterly total revenue, with a predictable pattern peaking in the first quarter.

Balance Sheet

A balance sheet summarises a companies assets, what it owns and its liabilities, what it owns to other people.

bs = AlphaVantage.balance_sheet("AAPL", datatype = "json")

Dict{String,Any} with 3 entries:
  "annualReports"    => Any[Dict{String,Any}("totalPermanentEquity"=>"None","wa…
  "symbol"           => "AAPL"
  "quarterlyReports" => Any[Dict{String,Any}("totalPermanentEquity"=>"None","wa…

string.(keys(bs["quarterlyReports"][1]))

51-element Array{String,1}:
 "totalPermanentEquity"
 "warrants"
 "negativeGoodwill"
 "preferredStockTotalEquity"
 "accumulatedAmortization"
 "inventory"
 "additionalPaidInCapital"
 "commonStockTotalEquity"
 "longTermInvestments"
 "fiscalDateEnding"
 "netTangibleAssets"
 "cashAndShortTermInvestments"
 "longTermDebt"
 ⋮
 "retainedEarnings"
 "shortTermInvestments"
 "propertyPlantEquipment"
 "goodwill"
 "preferredStockRedeemable"
 "totalLiabilities"
 "otherNonCurrentLiabilities"
 "currentLongTermDebt"
 "intangibleAssets"
 "accumulatedDepreciation"
 "otherCurrentLiabilities"
 "deferredLongTermAssetCharges"

Again, like the income statement, any of these keys can be extracted quarterly or annually.

fCash = AlphaVantage.cashAndShortTermInvestments_quarterlys("F",
datatype = "json")
fLiabilities = AlphaVantage.totalLiabilities_quarterlys("F", datatype
= "json")
cashPlot = plot(Date.(fCash[:Date]), 
                parse.(Float64, fCash[:cashAndShortTermInvestments])/1e9, 
                label="Cash and Short Term Investments (billions)",
                colour = "green")
liabPlot = plot(Date.(fLiabilities[:Date]), 
                parse.(Float64, fLiabilities[:totalLiabilities])/1e9, 
                label="Total Liabilities (billions)")
plot(cashPlot, liabPlot)

Ford Balance Sheet

As per the intro I’ve plotted Fords cash and short term investment balance against something the owe, the total liabilities.

Cash Flow

The cash flow statement shows the changes in the balance sheet. It helps judge a companies ability to meet its cash needs, i.e. pay their employers or service their debt.

cf = AlphaVantage.cash_flow("TSLA", datatype = "json")

Dict{String,Any} with 3 entries:
  "annualReports"    => Any[Dict{String,Any}("cashflowFromInvestment"=>"-428900…
  "symbol"           => "AAPL"
  "quarterlyReports" => Any[Dict{String,Any}("cashflowFromInvestment"=>"5531000…

string.(keys(cf["quarterlyReports"][1]))

24-element Array{String,1}:
 "cashflowFromInvestment"
 "changeInInventory"
 "reportedCurrency"
 "changeInAccountReceivables"
 "changeInCashAndCashEquivalents"
 "otherOperatingCashflow"
 "dividendPayout"
 "changeInReceivables"
 "capitalExpenditures"
 "changeInExchangeRate"
 "operatingCashflow"
 "cashflowFromFinancing"
 "changeInLiabilities"
 "stockSaleAndPurchase"
 "otherCashflowFromFinancing"
 "changeInOperatingActivities"
 "depreciation"
 "fiscalDateEnding"
 "changeInCash"
 "netBorrowings"
 "investments"
 "netIncome"
 "changeInNetIncome"
 "otherCashflowFromInvestment"

cashflow = AlphaVantage.cashflowFromFinancing_annuals("TSLA", datatype="json")

plot(Date.(cashflow[:Date]), 
     parse.(Float64, cashflow[:cashflowFromFinancing]) ./ 1e9, 
     label="Cash Flow from Financing (billions)",
     title = "Tesla")

Tesla Cash Flow

Earnings

Each company reports their earnings each quarter and summarise their performance of the previous quarter. There are more dates available for earnings, but also slightly different fields for the quarterly and annual results.

earnings = AlphaVantage.earnings("AAPL", datatype = "json")

Dict{String,Any} with 3 entries:
  "annualEarnings"    => Any[Dict{String,Any}("fiscalDateEnding"=>"2020-09-30",…
  "quarterlyEarnings" => Any[Dict{String,Any}("reportedDate"=>"2020-10-29","est…
  "symbol"            => "AAPL"

string.(keys(earnings["annualEarnings"][1]))

2-element Array{String,1}:
 "fiscalDateEnding"
 "reportedEPS"

string.(keys(earnings["quarterlyEarnings"][1]))

6-element Array{String,1}:
 "reportedDate"
 "estimatedEPS"
 "surprise"
 "surprisePercentage"
 "fiscalDateEnding"
 "reportedEPS"

extrema(Date.(get.(earnings["quarterlyEarnings"], "reportedDate", "")))

(Date("1996-04-17"), Date("2020-10-29"))

extrema(Date.(get.(earnings["annualEarnings"], "fiscalDateEnding", "")))

(Date("1996-09-30"), Date("2020-09-30"))

reported = AlphaVantage.reportedEPS_quarterlyEarnings("AAPL",
datatype="json")
plot(Date.(reported[:Date]), 
     parse.(Float64, reported[:reportedEPS]), 
     label="Reported EPS",
     title = "Apple")

Apple Earnings

There you go, lots more functions for the package and something different than just looking at stock prices. This fundamental data adds another dimension to any quantitate analysis of different stocks so go grab your free API key from AlphaVantage and get exploring!

If you are new to AlphaVantage you can also check out my previous post on getting market data into Julia.