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.

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:

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)

1AAgilent Technologies IncNYSEStock1999-11-18null
2AAAlcoa CorpNYSEStock2016-11-01null
3AAAAAF First Priority CLO Bond ETFNYSE ARCAETF2020-09-09null
4AAAUGoldman Sachs Physical Gold ETFNYSE ARCAETF2018-08-15null
5AACGATA IncNASDAQStock2008-01-29null

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)

1BABoeing CompanyNYSEStock1962-01-02null
2CATCaterpillar IncNYSEStock1962-01-02null
3DDDuPont de Nemours IncNYSEStock1962-01-02null
4DISWalt Disney Co (The)NYSEStock1962-01-02null
5GEGeneral Electric CompanyNYSEStock1962-01-02null

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

1AAPLApple IncNASDAQStock1980-12-12nullActive

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.

Base.KeySet for a Dict{String,Any} with 29 entries. Keys:
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 =
     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…
51-element Array{String,1}:

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…
24-element Array{String,1}:
cashflow = AlphaVantage.cashflowFromFinancing_annuals("TSLA", datatype="json")

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

Tesla Cash Flow


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"
2-element Array{String,1}:
6-element Array{String,1}:
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",
     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.