Some Company Data with Tidy Quant’s get = key.ratios

Getting Started

This document will review the different elements of the key.ratios call to the tq_get() function from the tidyquant package in R Studio. To begin, download R Studio and Base R.

Let’s get started by loading some libraries!

library(tidyverse)
library(tidyquant)

Then we will import Boeing’s financial data so we can perform some analysis on the key.ratios data. We will start off writing the tq_get() function to bring this data into memory, and decide on some time parameters we wish to perform the analysis on.

stock <- tq_get(params$stock, 
       get = "key.ratios") 


print(stock)

## # A tibble: 7 x 2
##   section           data              
##   <chr>             <list>            
## 1 Financials        <tibble [150 × 5]>
## 2 Profitability     <tibble [170 × 5]>
## 3 Growth            <tibble [160 × 5]>
## 4 Cash Flow         <tibble [50 × 5]> 
## 5 Financial Health  <tibble [240 × 5]>
## 6 Efficiency Ratios <tibble [80 × 5]> 
## 7 Valuation Ratios  <tibble [40 × 5]>

The output is a list of tibbles containing various financial informaiton about Boeing’s performance over the period we’re interested in performing the analysis on. In order to access specific data, we need to call a function that will unnest the data from the list into workable tables so we can begin some exploratory data analysis.

stock.financials <- stock %>% 
  filter(section == "Financials") %>% 
  unnest()

str(stock.financials, max.level = 1)

## Classes 'tbl_df', 'tbl' and 'data.frame':    150 obs. of  6 variables:
##  $ section    : chr  "Financials" "Financials" "Financials" "Financials" ...
##  $ sub.section: chr  "Financials" "Financials" "Financials" "Financials" ...
##  $ group      : num  1 1 1 1 1 1 1 1 1 1 ...
##  $ category   : chr  "Revenue USD Mil" "Revenue USD Mil" "Revenue USD Mil" "Revenue USD Mil" ...
##  $ date       : Date, format: "2008-12-01" "2009-12-01" ...
##  $ value      : num  60909 68281 64306 68735 81698 ...

unique(stock.financials$category)

##  [1] "Revenue USD Mil"                "Gross Margin %"                
##  [3] "Operating Income USD Mil"       "Operating Margin %"            
##  [5] "Net Income USD Mil"             "Earnings Per Share USD"        
##  [7] "Dividends USD"                  "Payout Ratio % *"              
##  [9] "Shares Mil"                     "Book Value Per Share * USD"    
## [11] "Operating Cash Flow USD Mil"    "Cap Spending USD Mil"          
## [13] "Free Cash Flow USD Mil"         "Free Cash Flow Per Share * USD"
## [15] "Working Capital USD Mil"

The output above tells us a few things:

*What the first few rows of financial information looks like in the stock.financial tibble. A tibble is just an organized dataset of vectors that allows us to perform repeatable analysis and data wrangling via the tidyverse() package.

How the tibble is structured and metadata about the different vectors.
ie: whether the data is a numeric, string, or date.
The unique items that are included in the Financials portion of the `key.ratios’ list for BA.

Next, let’s perfrom some visualizations so we can see what some of our data looks like. Let’s start with a rediculous graph of what all of our data looks like at the same time.

stock.financials %>% 
  ggplot(aes(x = date, y = value, color = category)) + 
  geom_line()+
  theme_bw()

As you can see, this chart is nonsensical as it combines to many different types of ratios to effectively gain insight into the financial performance of Boeing. Let’s filter through some of the variables to see if we can find any interesting relationships.

stock.financials %>%
  filter(str_detect(category, "%")) %>% 
  ggplot(aes(x = date, y = value, col = category))+ 
  geom_line()+
  theme_bw()

stock.financials %>% 
  filter(str_detect(category, "Mil")) %>% 
  ggplot(aes(x = date, y = value, col = category))+ 
  geom_line()+
  theme_bw()

stock.financials %>% 
  filter(str_detect(category, c("Earnings P", "Shares M"))) %>% 
  ggplot(aes(x = date, y = value)) +
  geom_col()+
  facet_grid(rows = vars(category), cols = NULL, scales = "free_y" )+ 
  theme_bw()

There are many comparisons that could be made across the list of key.ratios. To get a better idea of the data we have to work with, let’s make a list of all the unique categories we can use for analysis.

key.ratio_categories <- stock %>% 
  unnest()

key.ratio_categories <- as_data_frame(key.ratio_categories)  

key.ratio_categories %>% 
  select(category, group) %>% 
  unique() %>% 
  knitr::kable()

category	group
Revenue USD Mil	1
Gross Margin %	2
Operating Income USD Mil	3
Operating Margin %	4
Net Income USD Mil	5
Earnings Per Share USD	6
Dividends USD	7
Payout Ratio % *	8
Shares Mil	9
Book Value Per Share * USD	10
Operating Cash Flow USD Mil	11
Cap Spending USD Mil	12
Free Cash Flow USD Mil	13
Free Cash Flow Per Share * USD	14
Working Capital USD Mil	15
Revenue	16
COGS	17
Gross Margin	18
SG&A	19
R&D	20
Other	21
Operating Margin	22
Net Int Inc & Other	23
EBT Margin	24
Tax Rate %	25
Net Margin %	26
Asset Turnover (Average)	27
Return on Assets %	28
Financial Leverage (Average)	29
Return on Equity %	30
Return on Invested Capital %	31
Interest Coverage	32
Year over Year	33
3-Year Average	34
5-Year Average	35
10-Year Average	36
Year over Year	37
3-Year Average	38
5-Year Average	39
10-Year Average	40
Year over Year	41
3-Year Average	42
5-Year Average	43
10-Year Average	44
Year over Year	45
3-Year Average	46
5-Year Average	47
10-Year Average	48
Operating Cash Flow Growth % YOY	49
Free Cash Flow Growth % YOY	50
Cap Ex as a % of Sales	51
Free Cash Flow/Sales %	52
Free Cash Flow/Net Income	53
Cash & Short-Term Investments	54
Accounts Receivable	55
Inventory	56
Other Current Assets	57
Total Current Assets	58
Net PP&E	59
Intangibles	60
Other Long-Term Assets	61
Total Assets	62
Accounts Payable	63
Short-Term Debt	64
Taxes Payable	65
Accrued Liabilities	66
Other Short-Term Liabilities	67
Total Current Liabilities	68
Long-Term Debt	69
Other Long-Term Liabilities	70
Total Liabilities	71
Total Stockholders’ Equity	72
Total Liabilities & Equity	73
Current Ratio	74
Quick Ratio	75
Financial Leverage	76
Debt/Equity	77
Days Sales Outstanding	78
Days Inventory	79
Payables Period	80
Cash Conversion Cycle	81
Receivables Turnover	82
Inventory Turnover	83
Fixed Assets Turnover	84
Asset Turnover	85
Price to Earnings	86
Price to Sales	87
Price to Book	88
Price to Cash Flow	89

Now that we have a list of available categories with their unique grouping, we have the ability to call on key.ratios by grouping number rather than typing out the full category name. This will make plotting different categories and creating new variables from existing variables much easier than subsetting each group.

Let’s take a look at the following variables of BA to see if we can infer any meangingful relationship between Boeing’s credit standards and inventory management.

ratios <- c(83, 78, 56, 55)

inv_ratios <- key.ratio_categories %>% 
  group_by(category) %>% 
  filter(group %in% ratios)

str(inv_ratios)

## Classes 'grouped_df', 'tbl_df', 'tbl' and 'data.frame':  40 obs. of  6 variables:
##  $ section    : chr  "Financial Health" "Financial Health" "Financial Health" "Financial Health" ...
##  $ sub.section: chr  "Balance Sheet Items (in %)" "Balance Sheet Items (in %)" "Balance Sheet Items (in %)" "Balance Sheet Items (in %)" ...
##  $ group      : num  55 55 55 55 55 55 55 55 55 55 ...
##  $ category   : chr  "Accounts Receivable" "Accounts Receivable" "Accounts Receivable" "Accounts Receivable" ...
##  $ date       : Date, format: "2008-12-01" "2009-12-01" ...
##  $ value      : num  10.42 9.32 7.91 7.24 6.72 ...
##  - attr(*, "vars")= chr "category"
##  - attr(*, "drop")= logi TRUE
##  - attr(*, "indices")=List of 4
##   ..$ : int  0 1 2 3 4 5 6 7 8 9
##   ..$ : int  20 21 22 23 24 25 26 27 28 29
##   ..$ : int  10 11 12 13 14 15 16 17 18 19
##   ..$ : int  30 31 32 33 34 35 36 37 38 39
##  - attr(*, "group_sizes")= int  10 10 10 10
##  - attr(*, "biggest_group_size")= int 10
##  - attr(*, "labels")='data.frame':   4 obs. of  1 variable:
##   ..$ category: chr  "Accounts Receivable" "Days Sales Outstanding" "Inventory" "Inventory Turnover"
##   ..- attr(*, "vars")= chr "category"
##   ..- attr(*, "drop")= logi TRUE

inv_ratios %>% 
  ggplot(aes(x = date, y = value, col = category)) + 
  geom_line()+
  facet_grid(rows = vars(category), cols = NULL, scales = "free_y" )+
  theme_bw()

knitr::kable(inv_ratios)

section	sub.section	group	category	date	value
Financial Health	Balance Sheet Items (in %)	55	Accounts Receivable	2008-12-01	10.42
Financial Health	Balance Sheet Items (in %)	55	Accounts Receivable	2009-12-01	9.32
Financial Health	Balance Sheet Items (in %)	55	Accounts Receivable	2010-12-01	7.91
Financial Health	Balance Sheet Items (in %)	55	Accounts Receivable	2011-12-01	7.24
Financial Health	Balance Sheet Items (in %)	55	Accounts Receivable	2012-12-01	6.72
Financial Health	Balance Sheet Items (in %)	55	Accounts Receivable	2013-12-01	7.44
Financial Health	Balance Sheet Items (in %)	55	Accounts Receivable	2014-12-01	7.98
Financial Health	Balance Sheet Items (in %)	55	Accounts Receivable	2015-12-01	9.45
Financial Health	Balance Sheet Items (in %)	55	Accounts Receivable	2016-12-01	10.29
Financial Health	Balance Sheet Items (in %)	55	Accounts Receivable	2017-12-01	11.72
Financial Health	Balance Sheet Items (in %)	56	Inventory	2008-12-01	29.03
Financial Health	Balance Sheet Items (in %)	56	Inventory	2009-12-01	27.29
Financial Health	Balance Sheet Items (in %)	56	Inventory	2010-12-01	35.47
Financial Health	Balance Sheet Items (in %)	56	Inventory	2011-12-01	40.31
Financial Health	Balance Sheet Items (in %)	56	Inventory	2012-12-01	42.47
Financial Health	Balance Sheet Items (in %)	56	Inventory	2013-12-01	46.31
Financial Health	Balance Sheet Items (in %)	56	Inventory	2014-12-01	47.13
Financial Health	Balance Sheet Items (in %)	56	Inventory	2015-12-01	50.06
Financial Health	Balance Sheet Items (in %)	56	Inventory	2016-12-01	48.00
Financial Health	Balance Sheet Items (in %)	56	Inventory	2017-12-01	48.03
Efficiency Ratios	Efficiency	78	Days Sales Outstanding	2008-12-01	33.98
Efficiency Ratios	Efficiency	78	Days Sales Outstanding	2009-12-01	30.43
Efficiency Ratios	Efficiency	78	Days Sales Outstanding	2010-12-01	31.81
Efficiency Ratios	Efficiency	78	Days Sales Outstanding	2011-12-01	29.78
Efficiency Ratios	Efficiency	78	Days Sales Outstanding	2012-12-01	23.86
Efficiency Ratios	Efficiency	78	Days Sales Outstanding	2013-12-01	22.41
Efficiency Ratios	Efficiency	78	Days Sales Outstanding	2014-12-01	25.73
Efficiency Ratios	Efficiency	78	Days Sales Outstanding	2015-12-01	28.58
Efficiency Ratios	Efficiency	78	Days Sales Outstanding	2016-12-01	30.50
Efficiency Ratios	Efficiency	78	Days Sales Outstanding	2017-12-01	34.33
Efficiency Ratios	Efficiency	83	Inventory Turnover	2008-12-01	4.00
Efficiency Ratios	Efficiency	83	Inventory Turnover	2009-12-01	3.47
Efficiency Ratios	Efficiency	83	Inventory Turnover	2010-12-01	2.51
Efficiency Ratios	Efficiency	83	Inventory Turnover	2011-12-01	1.98
Efficiency Ratios	Efficiency	83	Inventory Turnover	2012-12-01	1.96
Efficiency Ratios	Efficiency	83	Inventory Turnover	2013-12-01	1.82
Efficiency Ratios	Efficiency	83	Inventory Turnover	2014-12-01	1.71
Efficiency Ratios	Efficiency	83	Inventory Turnover	2015-12-01	1.75
Efficiency Ratios	Efficiency	83	Inventory Turnover	2016-12-01	1.79
Efficiency Ratios	Efficiency	83	Inventory Turnover	2017-12-01	1.74

The charts reveal a clear build up in inventory over the period of analysis, with an increase in accounts recievables and the amount of time it takes to get paid; measured by the days sales outstanding. What is interesting is inventory turnover appears fall slightly before the large increase in inventories, indicating it may be a leading indicator in this particular instance. If inventory turnover is persistintly low over the next periods, management may have to evaluate the current product line, or increase their marketing budget to attract new customers and increase sales. This could lead to additional SG&A and R&D expenses.

Let’s take a look at one more chart.

topline <- c(2, 33)

revratio <- key.ratio_categories %>% 
  group_by(key.ratio_categories$category) %>%
  filter(group %in% topline)

revratio %>%
  ggplot(aes(x = date, y = value, col = category)) +
  geom_line() +
  facet_grid(rows = vars(category), cols = NULL, scales = "free_y" )+
  theme_bw()

With revenues flat over the same period, it’s significant that margins have moved in the opposite direction. Despite the lack of revenue growth and build up in inventory, Boeing has perfromed well by being more efficient in the production cycle which should have a positive impact on their bottom line.

chart <- tq_get(params$stock, get = "stock.price",
       from = "2017-01-01",
       to = Sys.Date())


#to position ma tags 
n <- SMA(chart$close, 50)
d <- SMA(chart$close, 200)

  chart %>%
    ggplot( aes(x = date, y = close)) +
    geom_line()+
    geom_ma(ma_fun = SMA, n = 50, linetype = 5, size = 1, group = "SMA", col = "red") +
    geom_ma(ma_fun = SMA, n = 200, size = 1, group ="SMA", col = "darkblue") +
    labs(title = params$company, 
         subtitle = "50 & 200- Day SMA",
         caption = Sys.Date(),
         y = "Closing Price", x = "")+
    coord_cartesian(xlim = chart$date[252:457],
                    ylim = chart$close[220:450])+
    scale_x_date(date_breaks = "4 weeks", # change to monthly ticks and labels
                 date_labels =c("%b"))+ #To add month and year c("%b", "%y")
    
    geom_label(x = last(chart$date), y = last(chart$close), label= "",label.size = 2, vjust = 200, hjust = 0)+
    
    annotate("label", x = last(chart$date) + 9, y = last(chart$close),  
             label =  last(round(chart$close, 0)), 
             fontface = "bold",
             size = 3)+
   
    annotate("label",x = last(chart$date) + 9, y = last(n) -5,  
             label = last(round(n, 0)), 
             fontface = "bold",
             col = "red", 
             size = 3) +
    
    annotate("label",x = last(chart$date) + 9, y = last(d),  
            label = last(round(d, 0)), 
             fontface = "bold", 
            col = "darkblue", 
            size = 3) +
    
    
    theme_tq()

Some Company Data with Tidy Quant’s `get = key.ratios`

wrash0806@my.mwsu.edu

10/25/2018

R Markdown:

This is an R Mardown .html document file.

Getting Started