[R] Visualize long-term financial data of US companies with Shiny dashboard
SimFin for free long-term financial data
Suddenly, are you investing in stocks?
Personally, I think it's easier to find good stocks in US stocks than in Japanese stocks. On sites like Yahoo finance, Gurufocus, and Morningstar, I think the free financial data is for the last five years. (Tidyquant is also powerless when it comes to financial data)
However, with a site called SimFin, you can get past financial data for free. SimFin
For the free version, financial data can only be obtained up to a year ago. Also, the items in the financial statements cannot be seen until the detailed breakdown. However, for Warren Buffett followers like me, it is important to get a rough idea of past performance trends, so there is no particular problem.
Actually, Morningstar can also see the main indicators for the past 10 years, but since it does not have a graphing function, I thought that it would be better if there was a tool that could roughly visualize it, so I made an app with Shiny.
Use the reticulate package and divert the Python code from the official tutorial
There is an R package of SimFin, but unfortunately it is not easy to use if you only have the function to acquire data using the company code as a key and you want to add a screening function in the future.
Click here for a description of the simfinR package
Therefore, we will use the reticulate package, divert the Python code introduced in the official tutorial, and collectively acquire the data of all companies and extract it from it.
app.R
#Package loading
pacman::p_load(shiny,shinydashboard,tidyverse,reticulate,tidyquant,patchwork,wesanderson)
#Pass Python through the system path
Sys.setenv(RETICULATE_PYTHON = "/Users/user/.pyenv/versions/anaconda3-5.2.0/bin")
use_python("/Users/user/.pyenv/versions/anaconda3-5.2.0/bin/python")
#Python module import
sf <- reticulate::import(module = "simfin")
pd <- reticulate::import(module = "pandas")
sn <- reticulate::import(module = "simfin.names")
#Specify API key and directory
sf$set_api_key(api_key='free')
sf$set_data_dir('~/Documents/R/simfin')
#Get financial data
df_income = sf$load(dataset='income', variant='annual', market='us')
df_cashflow = sf$load_cashflow(variant='annual', market='us')
df_balance = sf$load_balance(variant='annual', market='us')
The items stored in each are as follows.
> colnames(df_income)
[1] "Ticker" "SimFinId"
[3] "Currency" "Fiscal Year"
[5] "Fiscal Period" "Report Date"
[7] "Publish Date" "Shares (Basic)"
[9] "Shares (Diluted)" "Revenue"
[11] "Cost of Revenue" "Gross Profit"
[13] "Operating Expenses" "Selling, General & Administrative"
[15] "Research & Development" "Depreciation & Amortization"
[17] "Operating Income (Loss)" "Non-Operating Income (Loss)"
[19] "Interest Expense, Net" "Pretax Income (Loss), Adj."
[21] "Abnormal Gains (Losses)" "Pretax Income (Loss)"
[23] "Income Tax (Expense) Benefit, Net" "Income (Loss) from Continuing Operations"
[25] "Net Extraordinary Gains (Losses)" "Net Income"
[27] "Net Income (Common)"
> colnames(df_balance)
[1] "SimFinId" "Currency"
[3] "Fiscal Year" "Fiscal Period"
[5] "Publish Date" "Shares (Basic)"
[7] "Shares (Diluted)" "Cash, Cash Equivalents & Short Term Investments"
[9] "Accounts & Notes Receivable" "Inventories"
[11] "Total Current Assets" "Property, Plant & Equipment, Net"
[13] "Long Term Investments & Receivables" "Other Long Term Assets"
[15] "Total Noncurrent Assets" "Total Assets"
[17] "Payables & Accruals" "Short Term Debt"
[19] "Total Current Liabilities" "Long Term Debt"
[21] "Total Noncurrent Liabilities" "Total Liabilities"
[23] "Share Capital & Additional Paid-In Capital" "Treasury Stock"
[25] "Retained Earnings" "Total Equity"
[27] "Total Liabilities & Equity"
> colnames(df_cashflow)
[1] "SimFinId" "Currency"
[3] "Fiscal Year" "Fiscal Period"
[5] "Publish Date" "Shares (Basic)"
[7] "Shares (Diluted)" "Net Income/Starting Line"
[9] "Depreciation & Amortization" "Non-Cash Items"
[11] "Change in Working Capital" "Change in Accounts Receivable"
[13] "Change in Inventories" "Change in Accounts Payable"
[15] "Change in Other" "Net Cash from Operating Activities"
[17] "Change in Fixed Assets & Intangibles" "Net Change in Long Term Investment"
[19] "Net Cash from Acquisitions & Divestitures" "Net Cash from Investing Activities"
[21] "Dividends Paid" "Cash from (Repayment of) Debt"
[23] "Cash from (Repurchase of) Equity" "Net Cash from Financing Activities"
[25] "Net Change in Cash"
Calculation of financial indicators and acquisition of stock prices
Integrate the above three tables into one data frame We will calculate the indicators of interest such as growth rate and profit rate.
Below is the code calculated on the server side in app.R.
app.R
df_TICKER <- df %>% filter(Ticker == !!input$text) %>%
left_join(.,p,by="date") %>%
mutate("Sales Growth" = (Revenue / lag(Revenue)-1)*100,
"Gross Margin" = (`Gross Profit`/Revenue)*100,
"Operating Margin" = `Operating Income (Loss)`/Revenue*100,
"Net Income Growth" = (`Net Income (Common)` / lag(`Net Income (Common)`)-1)*100,
"Net Profit Margin" = (`Net Income (Common)`/Revenue)*100,
"Operating Cash Flow Margin" = (`Net Cash from Operating Activities`/Revenue)*100,
"Free Cash Flow" = `Net Cash from Operating Activities` - `Net Cash from Investing Activities`,
"Dividend per Share" = -`Dividends Paid`/`Shares (Diluted)`,
EBIT = `Net Income (Common)` - `Income Tax (Expense) Benefit, Net` - `Interest Expense, Net`,
"EBIT Margin" = (EBIT / Revenue)*100,
"Long Term Debt per Net Income" = `Long Term Debt` / `Net Income (Common)`,
ROE = (`Net Income (Common)` / `Total Equity`)*100,
"PP&E per Total Assets" = (`Property, Plant & Equipment, Net` / `Total Assets`)*100,
EPS = `Net Income (Common)` / `Shares (Diluted)`,
BPS = `Total Equity` / `Shares (Diluted)`)
I have financial data only up to a year ago, but I also use tidyquant to get the latest stock price data.
app.R
output$stock_price <- renderPlot({
#stock price from yahoo finance
p <- tq_get(req(input$text),get="stock.price","2000-01-01") %>% select(date,adjusted)
ggplot(p, aes(x=date,y=adjusted,color="red")) + geom_line() +
theme(legend.position = "",axis.text = element_text(size = rel(1.1))) +
labs(title="",x="",y="")
})
How to make Shiny dashboard
I will explain the important parts with reference to the official page.
Getting started with Shiny Dashboard
First of all, the overall configuration is as follows. Configure the interface with ui, The operation is performed on the server.
This time I did it with only one file called app.R, It is also possible to create ui.R and server.R separately, which is a matter of taste.
ui <- dashboardPage(
dashboardHeader(),
dashboardSidebar(),
dashboardBody()
)
server <- function(input, output) { }
shinyApp(ui, server)
ui creation
The ui part is very simple. First, the "Basic tabs" part of the image below is dashboardHeader (), The black menu on the left is composed of dashboardSidebar (), and the right is composed of dashboardBody ().
You can freely change the color of the header.
ui <- dashboardPage(
dashboardPage(skin = "blue") #Header color
dashboardHeader(),
dashboardSidebar(),
dashboardBody()
)
Creating headers and sidebars
Now that you know the overall structure, it's time to actually create the header and sidebar. The header is very simple, isn't it? Only the title is included at a minimum.
The sidebar uses sidebarMenu () and menuItem (). The arguments of menuItem () are the display name, tabName, and icon. As for the icon, you can specify the free icon that is displayed darkly from the commented out site.
Multiple menus can be displayed by connecting menuItems with commas. At this time, since the display of the body is changed for each menu, the one that clearly indicates each menu is the tabName of the menuItem.
ui <- dashboardPage(
dashboardHeader(title = "My Stock"),
dashboardSidebar(
sidebarMenu(
menuItem("Financials", tabName = "financials", icon = icon("comment-dollar"))
#icon website
#https://fontawesome.com/icons?d=gallery&q=finance
)
)
Creating a body
Next, we will make the body. Below, we have created two forms, a stock price ticker input form and a stock price chart. First, tabItem (tanName = "financials" specifies which menu to display, which was introduced earlier. And we will compose the contents with fruidRow () and box ().
Both boxes have functions called textOutput and plotOutput, but the argument specified here is the data passed from the server side. Others include tableOutput.
See commenting out for other parameters.
dashboardBody(
tabItems(
tabItem(tabName = "financials",
fluidRow(
box(
textInput("text", h6("Please Enter Ticker")),
tableOutput("company_name"),
status = "primary", #Color at the top of the box
width = 3 #The body has a total width of 12
),
box(
title = "Stock Price",
plotOutput("stock_price"),
status = "primary",
solidHeader = TRUE, #Thick box title
width = 9
)
)
)
)
)
Create server
Now that the ui is complete, let's create the other server. This is the part that returns the chart of company name / industry and stock price.
Pass the data to each argument specified by textOutput and plotOutput in the ui part earlier.
input $ text is the ticker code you enter in your app. One thing I'm addicted to is that when you insert an input into dplyr, you need a !! at the beginning.
The req () function that is bitten in input $ text is a function that does not calculate if there is no input in input. Of course, when you open the app, there is no input, but without it, an error code will be displayed.
server <- function(input, output) {
output$company_name <- renderTable({
return(df_companies %>% filter(Ticker==req(input$text)) %>%
select(`Company Name`,`Sector`,`Industry`) %>% t() %>% data.frame() %>% rename(.,`Company Description`=.))
})
output$stock_price <- renderPlot({
#stock price from yahoo finance
p <- tq_get(req(input$text),get="stock.price","2000-01-01") %>% select(date,adjusted)
ggplot(p, aes(x=date,y=adjusted,color="red")) + geom_line() +
theme(legend.position = "",axis.text = element_text(size = rel(1.1))) +
labs(title="",x="",y="")
})
}
Completed app
later, ・ Stock price ・ Financial data (raw data) ·growth rate ·Profit rate Etc., and plot each index in the same procedure.
The completed app looks like this.
When you enter the ticker code, various data will be displayed.
Code used this time
app.R
# Settings ---------------------------------------------------------------#Package loading
pacman::p_load(shiny,shinydashboard,tidyverse,reticulate,tidyquant,patchwork,wesanderson)
#Pass Python through the system path
Sys.setenv(RETICULATE_PYTHON = "/Users/user/.pyenv/versions/anaconda3-5.2.0/bin")
use_python("/Users/user/.pyenv/versions/anaconda3-5.2.0/bin/python")
#Python module import
sf <- reticulate::import(module = "simfin")
pd <- reticulate::import(module = "pandas")
sn <- reticulate::import(module = "simfin.names")
#Specify API key and directory
sf$set_api_key(api_key='free')
sf$set_data_dir('~/Documents/R/simfin')
#Get financial data
df_income = sf$load(dataset='income', variant='annual', market='us')
df_cashflow = sf$load_cashflow(variant='annual', market='us')
df_balance = sf$load_balance(variant='annual', market='us')
#merge all data. "XXX" is a dummy for duplicated column.
df <- inner_join(df_income,df_cashflow,by=c("SimFinId","Fiscal Year"),suffix=c("","XXX")) %>%
inner_join(.,df_balance,by=c("SimFinId","Fiscal Year"),suffix=c("","XXX")) %>%
select(-contains("XXX"))
colnames(df)[6] <- "date"
df["date"] <- ymd(df[,"date"])
rm(df_income);rm(df_cashflow);rm(df_balance)
#conpmany attribution
df_companies = sf$load_companies(market='us')
df_industries = sf$load_industries()
df_industries <- df_industries %>% mutate(IndustryId = as.numeric(row.names(.)))
df_companies <- df_companies %>% mutate(Ticker=row.names(.)) %>% left_join(.,df_industries,by="IndustryId")
rm(df_industries)
#barplot function
bar_plot <- function(x) {
p <- x %>% pivot_longer(col=-date,names_to = "item",values_to = "value") %>%
ggplot(.,aes(x=date,y=value,fill=item)) +
geom_bar(stat="identity") +
theme(legend.position = "",
axis.text = element_text(size = rel(1.1)),
#strip.background = element_rect(fill = "blue"),
strip.text = element_text(size = rel(1.1))
) +
labs(title="",x="",y="") +
facet_wrap(~item) +
scale_fill_manual(values=wes_palette(name="Zissou1"))
return(p)
}
# ui ----------------------------------------------------------------------
ui <- dashboardPage(
dashboardHeader(title = "My Stock"),
dashboardSidebar(
sidebarMenu(
menuItem("Financials", tabName = "financials", icon = icon("comment-dollar"))
#icon website
#https://fontawesome.com/icons?d=gallery&q=finance
)
),
dashboardBody(
tabItems(
tabItem(tabName = "financials",
fluidRow(
box(
textInput("text", h6("Please Enter Ticker")),
tableOutput("company_name"),
status = "primary",
width = 3 #a row has 12 width
),
box(
title = "Stock Price",
plotOutput("stock_price"),
status = "primary",
solidHeader = TRUE,
width = 9
)
),
fluidRow(
box(
title = "Financials",
plotOutput("financials_raw"),
status = "primary",
solidHeader = TRUE,
width = 12
)
),
fluidRow(
box(
title = "Growth Rate",
plotOutput("financials_growth"),
status = "primary",
solidHeader = TRUE,
width = 12
)
),
fluidRow(
box(
title = "Profitabillity",
plotOutput("financials_profitabillity"),
status = "primary",
solidHeader = TRUE,
width = 12
)
),
fluidRow(
box(
title = "Others",
plotOutput("financials_others"),
status = "primary",
solidHeader = TRUE,
width = 12
)
)
)
)
)
)
# Server ------------------------------------------------------------------
server <- function(input, output) {
df_TICKER <- reactive({
#stock price from yahoo finance
p <- tq_get(req(input$text),get="stock.price","2000-01-01") %>% select(date,adjusted)
#calculate signals
df_TICKER <- df %>% filter(Ticker == !!input$text) %>%
left_join(.,p,by="date") %>%
mutate("Sales Growth" = (Revenue / lag(Revenue)-1)*100,
"Gross Margin" = (`Gross Profit`/Revenue)*100,
"Operating Margin" = `Operating Income (Loss)`/Revenue*100,
"Net Income Growth" = (`Net Income (Common)` / lag(`Net Income (Common)`)-1)*100,
"Net Profit Margin" = (`Net Income (Common)`/Revenue)*100,
"Operating Cash Flow Margin" = (`Net Cash from Operating Activities`/Revenue)*100,
"Free Cash Flow" = `Net Cash from Operating Activities` - `Net Cash from Investing Activities`,
"Dividend per Share" = -`Dividends Paid`/`Shares (Diluted)`,
EBIT = `Net Income (Common)` - `Income Tax (Expense) Benefit, Net` - `Interest Expense, Net`,
"EBIT Margin" = (EBIT / Revenue)*100,
"Long Term Debt per Net Income" = `Long Term Debt` / `Net Income (Common)`,
ROE = (`Net Income (Common)` / `Total Equity`)*100,
"PP&E per Total Assets" = (`Property, Plant & Equipment, Net` / `Total Assets`)*100,
EPS = `Net Income (Common)` / `Shares (Diluted)`,
BPS = `Total Equity` / `Shares (Diluted)`)
#PE = adjusted / EPS)
return(df_TICKER)
})
output$financials_raw <- renderPlot({
#When exchanging data in the server, add (). df_TICKER()
df_TICKER() %>% select(date,Revenue,`Net Income (Common)`,`Free Cash Flow`) %>%
bar_plot(.)
})
output$financials_growth <- renderPlot({
df_TICKER() %>% select(date,`Sales Growth`,`Net Income Growth`) %>%
bar_plot(.)
})
output$financials_profitabillity <- renderPlot({
df_TICKER() %>% select(date,`Gross Margin`,`Operating Margin`,`Net Profit Margin`,
`Operating Cash Flow Margin`,`ROE`) %>%
bar_plot(.)
})
output$financials_others <- renderPlot({
df_TICKER() %>% select(date,`Dividend per Share`,`EPS`,`BPS`,`Long Term Debt per Net Income`,
`PP&E per Total Assets`) %>%
bar_plot(.)
})
output$company_name <- renderTable({
return(df_companies %>% filter(Ticker==req(input$text)) %>%
select(`Company Name`,`Sector`,`Industry`) %>% t() %>% data.frame() %>% rename(.,`Company Description`=.))
})
output$stock_price <- renderPlot({
#stock price from yahoo finance
p <- tq_get(req(input$text),get="stock.price","2000-01-01") %>% select(date,adjusted)
ggplot(p, aes(x=date,y=adjusted,color="red")) + geom_line() +
theme(legend.position = "",axis.text = element_text(size = rel(1.1))) +
labs(title="",x="",y="")
})
}
# Run the application
shinyApp(ui = ui, server = server)