Fundamental Financial Ratios in R

How the Income Statement looks:

How the Balance Sheet looks:

How the Cash Flow Statement looks:

The data still looks a little messy and unreadable and in order to do the calculations we should have the data in a different shape. That is, I find it easier to program calculations column wise instead of rowwise. It’s also easier to combine the data instead of calculating the ratios across different data frames as we have currently.

Clean the data up a little bit

ISBSCF <- rbind(IS, BS, CF)

ISBSCF <- ISBSCF %>%
  t() %>%
  data.frame() %>%
  rownames_to_column('rn') %>%
  separate(rn, into = c("symbol", "year"),
           sep = -4, convert = TRUE)


ISBSCF$symbol <- gsub("(f).*", "", ISBSCF$symbol)
ISBSCF$symbol <- gsub('.$', '', ISBSCF$symbol)
ISBSCF$symbol <- gsub('^X', '', ISBSCF$symbol)

How all the fundamentals look now:

This looks much cleaner. The data we currently have at the moment is just the Income Statement, Balance Sheet, and Cash Flow statements, but in order to make better comparisons across firms other information is needed such as Shares Outstanding which will be used in the Market Capitalisation calculation. We can use the quantmod package to pull some statistics from the Yahoo Finance statistics page.

############################## Get stats data ##################################

metrics <- yahooQF(c("Name", "Enterprise Value", "Enterprise Value/Revenue", "Enterprise Value/EBITDA", "Total Cash Per Share (mrq)", "EBITDA", "Volume", "P/E Ratio", "Dividend Yield", "Shares Outstanding", "Price/Book"))

stats <- getQuote(symbols, what = metrics) %>%
  rownames_to_column("symbol")

colnames(stats) <- gsub(" ", ".", colnames(stats))
colnames(stats) <- gsub("/", ".", colnames(stats))

How the company statistics look:

You can play around with downloading other statistics directly from the Yahoo Statistics pages, for instance, heres the statistics page for GOOG here. Already we have quite a bit of useful information, however not all of the statistics are able to be scraped from the Yahoo Finance statistics pages, therefore we will have to try and compute some ourselves. Moreover, we have some of the important statistics - Shares Outstanding and we can use the PE.Ratio, Dividend.Yield and Price.Book ratios to check the accuracy of our calculations later on.

We also need to collect the stock price data since we need the Closing Price today to compute per share statistics.

############################## Get latest stock price data ######################
stocks <- symbols %>%
  tq_get(get = "stock.prices",
         from =  "2018-01-01",
         to =  Sys.Date()) %>%
  select(symbol, date, adjusted) %>%
  group_by(symbol) %>%
  arrange(desc(date)) %>%
  slice(1)

How the stock price data looks:

We have three different data sets now ISBSCF, which is our combined Income Statement, Balance Sheet and Cash Flow statements, stats which is our Yahoo Finance company statistics data and finally stock which is todays closing price of our companies.

Now I join all the data together (we are only interested in 2018 values but we could extend it to more years).

############################## Join all 3 datasets together ######################
financials <- ISBSCF %>%
  filter(year == 2018) %>%
  inner_join(stats, by = "symbol") %>%
  inner_join(stocks, by = "symbol")

How all the data looks after joining the fundamentals, stats and latest share price data:

Now I think we have enough information to calculate some financial ratios based on company statistics and their respective financial data. The variables we have are:

What variables we have in the data:

colnames(financials) %>%
  data.frame() %>%
  setNames(c("Fundamentals")) %>%
  split(as.integer(gl(nrow(.), 14, nrow(.)))) %>%
  kable(caption = "Company Variables") %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive"), font_size = 12)

Which is quite a lot of information. I like this method of calculating financial data since dplyr and the pipe function makes it intuitive to read and we can see what we are calculating.

Some basic financial ratios:

############################## Create some financial ratios ######################

ratios <- financials %>%
  mutate(BvE = Total.Assets - Total.Liabilities,
         BvE_per_share = BvE / (Shares.Outstanding / 1000),
         EBITDA = Earnings.Before.Interest.and.Taxes + Depreciation,
         Market_Cap = adjusted * (Shares.Outstanding / 1000),                 #¡Cuidado!
         EV = (Market_Cap + Total.Liabilities) - Cash.And.Cash.Equivalents,
         Mkt_Book = EV /(Total.Assets - Cash.And.Cash.Equivalents),
         Price_Book = Market_Cap / Total.stockholders..equity,
         Price_Sales = Market_Cap / Total.Revenue,
         PE_Ratio = Market_Cap / Net.Income,
         EV_EBITDA =  EV / EBITDA,
         NI_REV = Net.Income / Total.Revenue,
         Rev_TA = Total.Revenue / Total.Assets,
         TA_BvE = Total.Assets / BvE,
         ROE = NI_REV * Rev_TA * TA_BvE * 100) %>%
  select(symbol, date, BvE, BvE_per_share, EBITDA, Market_Cap, EV, Mkt_Book, Price_Book, Price_Sales,
         PE_Ratio, EV_EBITDA, NI_REV, Rev_TA, TA_BvE, ROE)

We can check if the calculations are somewhat correct by comparing them to Yahoo Finance statistics. I select only a few ratios which we were able to get from the Yahoo statistics page.

stats %>%
  left_join(ratios, by = c("symbol")) %>%
  select(symbol, Name, P.E.Ratio, PE_Ratio, Price.Book, Price_Book) %>%
  kable(caption = "Price Earnings, Price Book ratios") %>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed", "responsive"), font_size = 12)

The ratios seem somewhat similar. However GOOG, MSFT and BIDU PE Ratio looks a little off. The same for BIDU’s Price_Book ratio. The problem here is that the adjusted stock price for BIDU is in USD however the financials are in CNY, so we have to be careful with foreign companies. I did develop a model which scrapes the currency information also and makes the conversion and we can better compare companies in different markets, which I will leave for another post.

Two things here:

1. Yahoo uses a number of different sources for their statistics, just check out their Footnotes side bar for Google here. They use Morningstar, Thomson Reuters, EDGAR and CapitalIQ.

2. The Shares Outstanding information will not be the same to the one we use. Yahoo published the more recent Shares Outstanding values but their PE ratios might use December 2018 - Net Income along with December 2018 - Market Cap, taking the Shares Outstanding and adjusted prices from December 2018. However we use September 2019 values! Historical Shares Outstanding information can be found here for Google

We can plot the performance of each stock to see how they did over the past year.

symbols %>%
  tq_get(get = "stock.prices",
         from =  "2018-12-31",
         to =  Sys.Date()) %>%
  select(symbol, date, adjusted) %>%
  group_by(symbol) %>%
  arrange(desc(date)) %>%
  ggplot(aes(x = date, y = adjusted))+
  geom_line() +
  facet_wrap(~ symbol, ncol = 2, scales = "free") +
  theme_tq() +
  ggtitle("Stock Prices since December 2018")

Also using fundamental data:

financials %>%
  ggplot(aes(x = symbol)) +
  geom_col(aes(y = Total.Assets, color = "blue", fill = "blue", alpha = 0.5)) +
  geom_col(aes(y = Total.Liabilities, color = "red", fill = "red", alpha = 0.5)) +
  ggtitle("Total Assets & Total Liabilities") +
  xlab("Symbols") +
  ylab("Total Assets & Total Liabilities") +
  theme_tq() +
  theme(legend.position = "none")

Any errors are my own, if you find any please let me know!