Introduction

Problem Statement

Is there a relationship in earnings within Industry for companies listed on the Nasdaq?

There has been a lot of research for whether earnings and stock prices are correlated, but very insignificant work on how correlated the earnings within a specific industry are. We all know that there are seasonal variations in each industry but I wanted to see to what extent the earnings within a specific industry move in line with each other. The purpose of this dashboard is to give us a glimpse of that.

Business Relevance

This research lets investors have a glimpse of how the financial indicators of the company are moving in line with industry. This is especially useful in deciding which whether to use a passive or active strategy to invest in that particular sector. For eg: if the revenue is moving in line with industry for most companies, the sector is pretty homogenous and a passive sector index investing strategy may be used. On the other hand, if there are much fluctuations, it makes sense to go for a more active investing approach.

Because we have limited space, I have decided to keep the visualizations limited to the ‘Capital Goods’ sector. The app may be used to explore the other sectors.

Dataset

https://data.world/riku-iki/nasdaq-financial-fundamentals

The dataset contains essential financial fundamental indicators for 2389 companies included into NASDAQ index extracted from https://finintelligence.com service. Dataset contains 10 indicators from Income, Cash Flow and Assets statements. All indicators in datasets are provided for calendar quarters for years of 2014, 2015 and 2016. Each record contains period, company name, stock tickers (some companies have several tickers), indicator name and amount. Data is originally extracted from companies SEC filings.

For the purpose of this report and the app, only five financial indicators and top 12 companies have been chosen.

Data Cleaning

Datatable and Charts

I started with the fundamentals_dataset1.csv file which is the file I downloaded from data world. There was no sector and market cap in it so I had to incorporate it into the file.

I found sector and market cap at the following link and saved the file as nasdaq.csv

https://public.opendatasoft.com/explore/dataset/nasdaq-companies/export/

data<-read.csv('fundamentals_dataset1.csv', stringsAsFactors = FALSE)
industry<-read.csv('nasdaq.csv', stringsAsFactors = FALSE )
#Change name to appropriate
names(industry)[names(industry)=="ï..Symbol"]<-"tickers"
#join the two files
final <- left_join(data, industry, by = "tickers")
final<-final%>%
  filter(indicator %in% c("Final Revenue", "Income from Continuing Operations before Taxes", "Net Income (Loss)", "Assets", "Total Equity"))%>%
  filter(Sector!="")

#Change market cap to numeric
final$MarketCap<-as.numeric(gsub(",","",final$MarketCap,fixed=TRUE))
final$amount<-as.numeric(gsub(",","",final$amount,fixed=TRUE))
#Remove NA values
final<-final%>%
  drop_na(MarketCap)

#Filter for top 12 by market cap
ind<-data.frame(c('Basic Industries', 'Capital Goods', 'Consumer Durables', 'Consumer Non-Durables'     
                  ,'Consumer Services', 'Energy', 'Finance', 'Health Care', 'Miscellaneous', 'Technology', 'Transportation', 'Public Utilities'))
raw<-data.frame()

for(i in 1:nrow(ind)){
  temporary<-final%>%
    filter(Sector==ind[i,])%>%
    group_by(tickers)%>%
    summarize(mktcap=mean(MarketCap))%>%
    top_n(12)%>%
    arrange(desc(mktcap))
  
  raw<-append(raw, temporary$tickers)
}


raw<-data.frame(unlist(raw))
names(raw)[names(raw)=="unlist.raw."]<-"tickers"

clean_data<-final %>% 
  filter(tickers %in% raw$tickers)
str(clean_data)
names(clean_data)[names(clean_data)=="ï..period"]<-"period"
write.csv(clean_data, file = "clean.csv", row.names = FALSE, fileEncoding="UTF-8")

A glimpse at the cleane dup dataset:

temp<-read.csv('clean.csv', stringsAsFactors = F)
head(temp, 12)
##     period                    company tickers
## 1  2014 Q2 8point3 Energy Partners LP    CAFD
## 2  2014 Q2 8point3 Energy Partners LP    CAFD
## 3  2014 Q3 8point3 Energy Partners LP    CAFD
## 4  2014 Q3 8point3 Energy Partners LP    CAFD
## 5  2014 Q4 8point3 Energy Partners LP    CAFD
## 6  2014 Q4 8point3 Energy Partners LP    CAFD
## 7  2015 Q1 8point3 Energy Partners LP    CAFD
## 8  2015 Q1 8point3 Energy Partners LP    CAFD
## 9  2015 Q1 8point3 Energy Partners LP    CAFD
## 10 2015 Q2 8point3 Energy Partners LP    CAFD
## 11 2015 Q2 8point3 Energy Partners LP    CAFD
## 12 2015 Q2 8point3 Energy Partners LP    CAFD
##                                         indicator      unit    amount
## 1                                   Final Revenue US Dollar   2103000
## 2  Income from Continuing Operations before Taxes US Dollar   4708000
## 3                                   Final Revenue US Dollar   2331000
## 4  Income from Continuing Operations before Taxes US Dollar  -2815000
## 5                                          Assets US Dollar 247969000
## 6                                    Total Equity US Dollar 127510000
## 7                                   Final Revenue US Dollar   2134000
## 8  Income from Continuing Operations before Taxes US Dollar  -9160000
## 9                               Net Income (Loss) US Dollar  -9166000
## 10                                         Assets US Dollar 947717000
## 11                                  Final Revenue US Dollar   2179000
## 12 Income from Continuing Operations before Taxes US Dollar -10967000
##     MarketCap           Sector                    Industry
## 1  1031162867 Public Utilities Electric Utilities: Central
## 2  1031162867 Public Utilities Electric Utilities: Central
## 3  1031162867 Public Utilities Electric Utilities: Central
## 4  1031162867 Public Utilities Electric Utilities: Central
## 5  1031162867 Public Utilities Electric Utilities: Central
## 6  1031162867 Public Utilities Electric Utilities: Central
## 7  1031162867 Public Utilities Electric Utilities: Central
## 8  1031162867 Public Utilities Electric Utilities: Central
## 9  1031162867 Public Utilities Electric Utilities: Central
## 10 1031162867 Public Utilities Electric Utilities: Central
## 11 1031162867 Public Utilities Electric Utilities: Central
## 12 1031162867 Public Utilities Electric Utilities: Central

Correlation

This part was tricky as the data was in long format so I had to restructure so that I could calcualte the percentage change in the values for the 12 quarters for the purpose of calculating correlation. This was needed as calculating correlation between dollar values does not make any sense.Two separate files were saved, revcorr.csv and toteqcorr.csv to be used in the visualizations.

#Steps to create toteqcorr.csv file

ind<-data.frame(c('Basic Industries', 'Capital Goods', 'Consumer Durables', 'Consumer Non-Durables'     
                  ,'Consumer Services', 'Energy', 'Finance', 'Health Care', 'Miscellaneous', 'Technology', 'Transportation', 'Public Utilities'))

data<-read.csv('clean.csv')
#Filter for total equity
data1<-data%>%
  filter(indicator=='Total Equity')

#unlist and spread to show values under each ticker
temporary=data.frame()
for(i in 1:nrow(ind)){
  #data<-data
  
  data2<-data1%>%
    filter(Sector==ind[i,])
    
  data3<-data2[c('tickers','amount' )]

  data4<-data3 %>% 
    group_by(tickers)%>%
    mutate(rowid=1:n())%>% 
    ungroup() %>%  # build group index
    spread(key=tickers, value=amount)   # spread
  data4<-data4[-1]
  temporary<-append(temporary, as.data.frame(data4))
}

tmp1<-data.frame(temporary)

#Convert to time series
library(zoo)
rev<-as.ts(tmp1)
#Calculate percentage change
rev1<-rev/stats::lag(rev,-1) - 1
colnames(rev1) <- gsub("rev.", "", colnames(rev1))
rev1<-na.locf(rev1)
#Save file
write.csv(rev1, file = "toteqcorr.csv", row.names = FALSE, fileEncoding="UTF-8")

#Similar steps have been taken to construct the revcorr.csv file, so I am not repeating the code.

A glimpse of the cleaned up file to analyze correlation in revenue:

temp1<-read.csv('revcorr.csv', stringsAsFactors = F)
head(temp1)
##          AMWD        BCPC         CVCO         GPRE         IOSP         PATK
## 1  0.11748158  0.53764754  0.060628463  0.141668563  0.002718623  0.104055245
## 2  0.10853900  0.21371852  0.001085051 -0.004694113  0.031179394  0.001506481
## 3 -0.14694177  0.01357094  0.054674658 -0.004779806  0.273882559  0.007632695
## 4  0.10195671 -0.10946222 -0.038684562 -0.110310517 -0.073959408  0.178368342
## 5 -0.04036559 -0.06964559  0.144568419  0.008263948 -0.097696880  0.045181478
## 6  0.10853900  0.03973348  0.187396393 -0.002274040  0.046521202 -0.079989378
##          PRIM        SHLM        SRCL        STLD        UFPI         WDFC
## 1  0.09619124  0.09724082  0.12433789  0.13096626  0.39486424  0.015565276
## 2  0.19007900 -0.02845904  0.04221921  0.13008990 -0.07669084  0.020616832
## 3 -0.20488816 -0.01961400  0.01358034  0.07613330 -0.13090461 -0.012999119
## 4 -0.19444946 -0.11829550 -0.02013156 -0.18658746  0.02085988  0.010150177
## 5  0.23108356  0.03423045  0.07895145 -0.02072251  0.32407251 -0.049788865
## 6  0.14972753  0.20176408  0.00406182 -0.02697447 -0.09054954 -0.005449532
##          BEAV        CGNX        COHR        FLIR         GNTX        GRMN
## 1  0.02791996  0.01481376 -0.01357782  0.05074216  0.008036004  0.33371055
## 2 -0.01342991  0.67276432  0.04491723  0.01620553  0.036866536 -0.09200384
## 3 -0.02447232 -0.42413641 -0.02302965  0.15717993 -0.001433400  0.13737128
## 4  0.08184384  0.14045765  0.01548239 -0.20685090  0.052869345 -0.27126898
## 5  0.01536232  0.41880974 -0.07470511  0.14065489  0.027974966  0.32189602
## 6 -0.02968884 -0.25197978  0.11204125 -0.02811120  0.027872846 -0.12165463
##          IEP        ILMN        NDSN        PCAR         TRMB        TSLA
## 1  0.2783567 0.063660194  0.09842596  0.04331149  0.062014222  0.23980166
## 2 -0.3067879 0.073870339  0.02189510  0.07851891 -0.089380255  0.10717503
## 3 -0.2388060 0.066057050 -0.19117352  0.03869410 -0.035909713  0.12309992
## 4  0.3401664 0.051106700  0.05730486 -0.05594733  0.033345158 -0.01754122
## 5  0.1048548 0.001509567  0.15472878  0.05115153  0.005492619  0.01606162
## 6 -0.3555377 0.020195484 -0.03572486 -0.04580799 -0.040116081 -0.01904446
##          CASY        CENT        CPRT        FELE         HELE        IRBT
## 1  0.19359584 -0.12683932 -0.07190965  0.22947277 -0.002374248  0.22415152
## 2 -0.06152927 -0.14554998  0.01021395 -0.02249561  0.026207750  0.02642290
## 3 -0.22242003 -0.17881134 -0.04865248 -0.08737864  0.361698271  0.11042043
## 4 -0.01082741  0.61916569  0.07559600 -0.11071710 -0.132998526 -0.25969926
## 5  0.23867466 -0.07667976 -0.04997274  0.09614533 -0.085735843  0.26133214
## 6 -0.06052547 -0.15905460  0.02318513 -0.06022635  0.068870260 -0.03480791
##          LFUS        LITE        MLHR       POOL        SLGN         VWR
## 1  0.06791583 -0.00297619  0.06931345  1.0874924  0.07184704  0.04363051
## 2 -0.01493835  0.08955224  0.04553846 -0.2743375  0.33914291  0.01061032
## 3 -0.05049447 -0.03881279  0.10927997 -0.3884322 -0.25909199 -0.01148600
## 4  0.01787339 -0.05605701 -0.08666431  0.1965456 -0.10279796 -0.06535948
## 5  0.05566941  0.05133367  0.06642138  0.8912040  0.11955410  0.05011655
## 6 -0.02932605  0.01771182  0.02669330 -0.2419144  0.31643713  0.01322605
##          BUFF        CTAS         ENTG        FIZZ        HAIN         HAS
## 1 -0.03356067  0.02110042  0.517321657  0.06264376 -0.03476911  0.29912104
## 2  0.07370549 -0.02850632  0.085365175 -0.06334282  0.17325510  0.63609836
## 3  0.01413724  0.01932896 -0.005204099 -0.12565490  0.10316876  0.03404519
## 4  0.04487818 -0.01293597 -0.030408676  0.15082400 -0.04831249 -0.54465593
## 5  0.02099898  0.03042440  0.065822996  0.12633664 -0.06564424  1.00398740
## 6  0.02141355  0.02448925 -0.037248539 -0.03618396  0.10973881  1.40105934
##           LANC        LULU         MAT        MDLZ        MNST         WOOF
## 1  0.070122266  0.01583389  0.12267789 -0.02372411  0.28177920  0.088908515
## 2  0.004555501  0.07342568  0.90296088 -0.01173542 -0.07454464  0.020644695
## 3  0.167023736  0.43656830 -0.01358349  0.05913398 -0.04780871 -0.039333276
## 4 -0.131870631 -0.29701191 -0.53722932 -0.12095130  0.03504814  0.040685354
## 5  0.054350797  0.06957010  0.07087843 -0.01301211  0.10678360  0.098772057
## 6  0.058941509  0.05890157  0.81345380 -0.10599138  0.09066600  0.005342712
##          AMZN         CHTR        CMCSA        COST          DISH        EQIX
## 1 -0.02031305 0.0258855586 -0.032398897 -0.01946324  0.0261312816 0.043285700
## 2  0.06406412 0.0123948650 -0.003146521  0.37718074 -0.0023773636 0.025249479
## 3  0.42514214 0.0319195453  0.056041927 -0.24370126  0.0006435918 0.028495860
## 4 -0.22541598 0.0008474576  0.006823821 -0.02847465  0.0115459653 0.007918561
## 5  0.02060131 0.0287891617  0.049851566  0.04287192  0.0290521955 0.034839717
## 6  0.09372439 0.0082304527 -0.003948141 -0.01656870 -0.0257956777 0.031651998
##           FOX       LBTYA          MAR       NFLX        ROST         SBUX
## 1  0.02494221  0.01510907  0.058001822 0.05536462  0.01826947  0.072254634
## 2 -0.06374644 -0.02281518 -0.006888634 0.05149555 -0.04789992  0.006524304
## 3  0.02130087  0.02623855  0.028612717 0.05342294  0.16695192  0.148871029
## 4 -0.15083799 -0.02129919 -0.012924979 0.05954020 -0.03117686 -0.049904231
## 5 -0.09283626  0.01098098  0.050099630 0.04549214  0.01025204  0.069617618
## 6 -0.02062853  0.00676667 -0.030089455 0.05694737 -0.06246568  0.006883553
##          AHGP        GPOR        MGEE       PDCE        PTEN       PVAC
## 1  0.10431160 -0.02789992 -0.38754786 -0.1740952  0.11664956 -0.2659995
## 2 -0.04884839  0.48866964  0.04946996  1.2166767  0.11667080  0.4738413
## 3  0.03771059  0.56727594  0.07823288  0.8155427  0.06573931 -0.5026631
## 4 -0.05145794 -0.34225262  0.16764466 -0.6452411 -0.27021179 -0.2704232
## 5  0.07909870 -0.36224493 -0.28217758 -0.6476575 -0.28118942  0.1219558
## 6 -0.06330764  1.05169466  0.15286671  3.5349294 -0.10684045  0.3392652
##            WWD        ACGL        AMTD        CBOE        CINF          CME
## 1  0.086673285  0.09451075 -0.06034483 -0.08831111  0.02102607 -0.058914330
## 2  0.078512791 -0.04164398  0.04193971  0.03451390  0.05436573  0.042099508
## 3 -0.137592029  0.02208495  0.03018868  0.11804446 -0.01406250  0.103226653
## 4  0.011434524  0.06692585 -0.01953602 -0.14204627  0.01822504  0.001902271
## 5  0.003219646 -0.05792244 -0.01120797  0.04120723  0.02412451 -0.026937226
## 6  0.137052606 -0.08538403  0.04659950  0.25758951 -0.02887538  0.036951220
##          ETFC        IBKR        NDAQ         TROW          ALXN        AMGN
## 1 -0.07789474 -0.13008130 -0.03674833  0.031112508 -0.0955161873  0.14576421
## 2  0.00456621 -0.38940810 -0.05433526  0.037082190  0.0832222753 -0.02876448
## 3  0.04772727  0.16836735  0.12347188  0.001567398  0.0798528675  0.05963029
## 4 -0.04338395 -0.18340611 -0.06637650  0.004499218  0.0008740967 -0.05589946
## 5 -0.02721088  1.16577540 -0.05944056  0.044206426  0.0603500000  0.06695808
## 6 -0.85780886 -0.07407407  0.07930607 -0.021820216  0.0483959699  0.06573557
##          BIIB        BMRN         ESRX         GILD        INCY         ISRG
## 1  0.13696122  0.26548643  0.060206882  0.307261452  0.10902976  0.102216484
## 2  0.03712575 -0.07945273  0.026581976 -0.075439939  0.98978731  0.073994533
## 3  0.05148523  0.29933333  0.020718816  0.210526316 -0.37433824  0.099254681
## 4 -0.03245352 -0.11541614 -0.053700509  0.038282745  0.28475555 -0.120059534
## 5  0.01432485  0.23267790  0.022273450  0.085593890  0.02328677  0.101484683
## 6  0.07188609 -0.16483499 -0.009098695  0.006186317  0.15110072  0.006142297
##             MYL       REGN        SHPG          WBA        AKAM        ATHN
## 1  0.0709372814 0.06386039  0.11531037 -0.010405509  0.04968666  0.14038090
## 2  0.1342731182 0.09026288  0.06324479 -0.017731045  0.04622979  0.02423597
## 3 -0.0006238004 0.10545917 -0.01314883  0.026079656  0.07680678  0.11965677
## 4 -0.1013107985 0.08385961 -0.05564368  0.358954690 -0.01819707 -0.03195850
## 5  0.2671368275 0.14834777  0.04649288  0.083618711  0.02694403  0.08866279
## 6  0.1364000506 0.13899723  0.06253210 -0.009480813  0.01906152  0.05073431
##            CDK        CMPR       CSGP         EBAY         HQY        IDCC
## 1  0.008980243  0.18159582 0.24045148  0.008841322 0.032623202  2.35789364
## 2  0.022547468 -0.01248836 0.03620657 -0.008302583 0.046479345 -0.60036863
## 3  0.000000000  0.31734904 0.01986201  0.080465116 0.137636081  0.10949215
## 4  0.018181818 -0.22733090 0.01873206 -0.112785192 0.200192996  0.28166185
## 5 -0.044262918  0.11934946 0.07317947  0.023774867 0.021574539  0.07404555
## 6  0.022858279 -0.01240577 0.10794166 -0.005213270 0.002033187 -0.15303962
##          MELI         PYPL        TIVO         ZG        AAPL           ADP
## 1  0.14271723  0.058164354 -0.04629733 0.18767266 -0.17995005 -0.0891475619
## 2  0.12200320 -0.004034291 -0.10122047 0.12673657  0.12532058 -0.0001948103
## 3  0.09081691  0.110379747  0.30171828 0.04154728  0.77098022  0.0340984373
## 4 -0.08221479 -0.025535796 -0.20836677 0.37847264 -0.22237564  0.1396970154
## 5  0.04193703  0.074871315  0.05795547 0.34568212 -0.14488881 -0.1090500281
## 6  0.09284316 -0.016978668  0.22257176 0.03208987  0.03822195  0.0072369642
##          ATVI        AVGO         CSCO       CTSH          FB       GOOGL
## 1 -0.12691269 -0.01284404  0.070333478 0.03913636  0.16306954  0.04448345
## 2 -0.22371134  0.07496902 -0.009063689 0.02538636  0.10068729  0.02717580
## 3  1.09163347  0.06051873 -0.025234790 0.06249516  0.20231033  0.05347775
## 4 -0.18857143 -0.03750000  0.016839812 0.06151770 -0.07997923  0.14211513
## 5 -0.18309859  0.99943535  0.058169235 0.05977327  0.14084110 -0.05026021
## 6 -0.05172414  0.07088393 -0.012536012 0.03306321  0.11355764  0.06136150
##          INTC         MSFT         NVDA        YHOO         AAL        CHRW
## 1  0.08359448  0.146007940  0.000000000 -0.04285134  0.13606803  0.11466134
## 2  0.05227388 -0.007740997  0.110607434  0.05898315 -0.01902246 -0.01015039
## 3  0.01147451  0.140899099  0.021224490  0.09139304 -0.08788940 -0.03177055
## 4 -0.13178453 -0.179108425 -0.079936051 -0.02162845 -0.03277559 -0.01677349
## 5  0.03239183  0.020755672  0.001737619  0.01410720  0.10176046  0.07397944
## 6  0.09624858 -0.081199279  0.131830009 -0.01414984 -0.01117576 -0.03549559
##            CSX         EXPD          HA         JBHT        JBLU         LSTR
## 1  0.077025232  0.072065404  0.09690621  0.100190631  0.10674574  0.183444566
## 2 -0.007090012  0.066263075  0.11071702  0.034427377  0.02411253  0.005988142
## 3 -0.009003415  0.037373065 -0.10105839  0.005218105 -0.05428385  0.053105014
## 4 -0.051691729 -0.051618301 -0.06011944 -0.105206488  0.05325035 -0.116419225
## 5  0.012223323  0.008361718  0.05740542  0.069280923  0.05843729  0.139042210
## 6 -0.040796345 -0.023777558  0.10579998  0.030219675  0.04652605 -0.030697284
##          ODFL        SAVE         SKYW          WERN        APLP         ATNI
## 1  0.13334548  0.14007265  0.057209737  0.1018206503  0.20362507  0.107683508
## 2  0.05775213  0.04091826  0.022115571  0.0181528075  0.05126498  0.073544777
## 3 -0.03030988 -0.08711947 -0.024895972  0.0022193597  0.05203607 -0.009866544
## 4 -0.03439854  0.03976505 -0.065682543 -0.1040011859  0.01962354 -0.035769565
## 5  0.09465921  0.12175006  0.036847809  0.0786637453  0.02133966  0.058363114
## 6  0.02272909  0.03870471  0.007086352 -0.0003665991 -0.02686516  0.071474437
##          CAFD         CNSL          FTR        INFN          NTGR        OTTR
## 1  0.10841655  0.009275099 -0.006065858 0.158134650 -0.0337358432 -0.09583841
## 2 -0.08451308 -0.013215392 -0.005231037 0.049335244  0.0466048980  0.01111831
## 3  0.02108716  0.248081052  0.165644172 0.073444765 -0.0004415036 -0.01586567
## 4  0.41165672  0.035287667  0.030827068 0.002984338 -0.1246524455  0.04877797
## 5  0.31046814  0.043784856 -0.002188184 0.109621004 -0.0659050256 -0.07241140
## 6  0.76184570 -0.035082832  0.040935673 0.121179092  0.1839138173  0.06308696
##          PEGI         SHEN       TERP        TMUS
## 1  0.31197401  0.011982300  1.6665872  0.04509091
## 2  0.10017383  0.010464774  1.3936902  0.02296451
## 3  0.11044618  0.006588224 -0.2006123  0.10938776
## 4 -0.18323302  0.017836010  0.6467772 -0.04611234
## 5  0.30532174  0.016776015  0.8442317  0.05155567
## 6  0.05935917 -0.005705884  0.2556403 -0.04034723

Visualizations

Datatable

The datatable shows the financial indicators for 2016 Q4 for Capital Goods sector

data <- read.csv('clean.csv', stringsAsFactors = F) %>% 
    mutate(MarketCap = gsub(',','',MarketCap, fixed = T)) %>% 
    mutate(MarketCap = as.numeric(MarketCap)) %>% 
    rename(Period = period, Indicator = indicator) 
    
data$Indicator = gsub('Net Income (Loss)','Net Income', data$Indicator, fixed = T)
data$Indicator = gsub('Final Revenue','Revenue', data$Indicator, fixed = T)
data$Indicator = gsub('Income from Continuing Operations before Taxes','Income from continuing operations', data$Indicator, fixed = T)
data$Indicator = gsub('Assets','Total Assets', data$Indicator, fixed = T)

dtdata = data %>% 
      filter(Sector == 'Capital Goods') %>% 
      filter(Period == '2016 Q4') %>%
      mutate(amount=amount/1000)%>%
      mutate(MarketCap=MarketCap/1000)%>%
      select(-Period) %>% 
      spread(key = Indicator, value = amount) %>% 
      select(
        Company = company,
        MarketCap=MarketCap,
        Revenue,
        `Income from continuing operations`,
        `Net Income`,
        `Total Assets`,
        `Total Equity` )

table<-DT::datatable(dtdata, editable = F,  filter = 'none', rownames = F,
                    options = list(
                      columnDefs = list(list(className = 'dt-center', targets = 0:(-1+ncol(dtdata)))),
                      dom = 'ft',pageLength = 12,ordering=T,searching = F)
      ) %>% 
        formatStyle(c(1:ncol(dtdata)), `text-align` = 'center') %>% 
        formatCurrency(2:ncol(dtdata),currency = "", interval = 3, mark = ",",   digits = 0)

table

Relation to Industry Average

The visualization aims to show the revenue of each company in the datatable in relation to the industry average. The purpose is to understand if the two move in line with each other.

We see that 6 out of 12 companies seem to move in line with the Industry average.

#Calculating Industry Average
avg <- data %>% 
  group_by(Sector, Indicator, Period) %>% 
    summarise(Average = mean(amount)) %>% 
    ungroup()
#Filter for specific sector
cdata = data %>% 
  filter(Sector == 'Capital Goods') 
    
companies = unique(cdata$company)
periods = unique(cdata$Period)
    
plots = list()
    
for(x in 1:12){
      
  ccdata = cdata %>% 
    filter(company == companies[x]) %>% 
    filter(Indicator == 'Revenue') %>% 
    complete(Period  = periods) %>% 
    fill(company, .direction = 'down') %>%
    mutate(amount = ifelse(is.na(amount),0,amount)) %>%
    left_join(
      avg %>% 
        filter(Sector == 'Capital Goods') %>% 
        filter(Indicator == 'Revenue')
    )
      
  over = seq(1, 100, by=2)
  ay <- list( tickfont = list(color = "black"), overlaying = paste(c("y", over[x]), collapse = ""),                 side = "right", title = "Industry Avg" )
  a <- list( title = "", showticklabels = TRUE)
      fig <- plot_ly(ccdata)
      fig <- fig %>% add_lines(x = ~periods, y = ~amount, color = I("#698096"), name =             
                                 ccdata$company[1] , customdata = ~as.numeric(Average),text =   
                                 unique(ccdata$Period),
                                 hovertemplate = paste(
                                   '<i>Company:</i>: $%{y:.2s}', 
                                   '<br><i>Industry:</i>: $%{customdata:.2s}', 
                                   '<br><b>%{text}</b>'))
                                                     
  fig <- fig %>% 
    add_lines(x = ~periods, y = ~Average, color = I("grey"), name = "Industry", yaxis = "y2" )
  fig <- fig %>%
    add_annotations(
          text = ccdata$company[1], x = 0.5, y = 1.2, yref = "paper", xref = "paper", xanchor =    
            "center", yanchor = "top",showarrow = FALSE, font = list(family = "Courier New,    
                                      monospace",size = 12,color = "black" ) ) %>% 
    layout(xaxis = a, yaxis = a, yaxis2 = ay,showlegend = FALSE)
  fig
      
  plots[[x]] = subplot(fig  %>% layout(showlegend = FALSE))
}
    
p<-subplot(plots, margin = 0.065, nrows = 4, shareX = T)
p

Movement with other companies

Let us now see how the revenue of the companies move with each other. I have taken the log of the revenue to account for the difference in scales. The animation below shows revenue for each company moving from 2014 Q1 to 2016 Q4.

We do not see most companies moving exactly in line with each other, but it is hard to say from the animation to what extent they are correlated. Please see Correlation for that part.

cdata1<-cdata%>%
  filter(Indicator == 'Revenue')
  
cdata1$Period = gsub('2014 Q1','2014-03-31', cdata1$Period, fixed = T)
cdata1$Period = gsub('2014 Q2','2014-06-30', cdata1$Period, fixed = T)
cdata1$Period = gsub('2014 Q3','2014-09-30', cdata1$Period, fixed = T)
cdata1$Period = gsub('2014 Q4','2014-12-31', cdata1$Period, fixed = T)
cdata1$Period = gsub('2015 Q1','2015-03-31', cdata1$Period, fixed = T)
cdata1$Period = gsub('2015 Q2','2015-06-30', cdata1$Period, fixed = T)
cdata1$Period = gsub('2015 Q3','2015-09-30', cdata1$Period, fixed = T)
cdata1$Period = gsub('2015 Q4','2015-12-31', cdata1$Period, fixed = T)
cdata1$Period = gsub('2016 Q1','2016-03-31', cdata1$Period, fixed = T)
cdata1$Period = gsub('2016 Q2','2016-06-30', cdata1$Period, fixed = T)
cdata1$Period = gsub('2016 Q3','2016-09-30', cdata1$Period, fixed = T)
cdata1$Period = gsub('2016 Q4','2016-12-31', cdata1$Period, fixed = T)

cdata1$Period<-as.Date(cdata1$Period)

p2<-  ggplot(cdata1, aes(x=Period, y=log(amount), group=tickers, color=tickers)) +
  geom_line() +
  geom_point() +
  scale_color_viridis(discrete = TRUE) +
  ggtitle("Movement in Revenue") +
  theme_classic() +
  ylab("Revenue") +
  theme(plot.title = element_text(hjust = 0.5),
        axis.line=element_blank(),
        axis.title.y=element_blank(),
        axis.text.y=element_blank(),
        axis.ticks.y=element_blank())+
  transition_reveal(Period)

animate(p2, renderer = gifski_renderer(loop=T), nframes = 50, duration = 25,end_pause = 40)

Correlation

We have already calculated the percentage change in revenue for all the companies the revcorr.csv file. We will now filter that for Capital Goods, run correlation, and do a correlation plot to explore the extent to which the revenues are correlated.

We see that very few pairs have a correlation over 0.70.

tickers = data %>% 
    filter(Sector == 'Capital Goods')%>% 
    filter(Indicator == 'Revenue') %>% 
    select(tickers) %>% unique() %>% pull()

rev_corr <- read.csv('revcorr.csv', stringsAsFactors = F)
m<- cor(rev_corr[,tickers])     
par(xpd=TRUE)
corrplot(m, type = "upper", 
          order = "hclust", tl.cex=.8,
          tl.col="#003572", tl.pos = "lt",
          mar = c(2, 1, 2, 1),
          col = gray.colors(100))
corrplot(m, add = TRUE, type = "lower", method = "number", order = "hclust",
          diag = FALSE, col = "black",tl.pos = "n",tl.cex=.3, cl.pos = "n", number.cex=.7)

Conclusion and limitations

We can conclude that the revenue of the top 12 companies by market cap of Capital Goods sector on Nasdaq are not correlated. In fact out of 132 pairs in the dataset, only the 12 pairs listed below have a correlation over 0.70 (that is less than 10% of the pair list). Therefore a passive industry based approach for investing is not exactly a good idea if the strategy permits different.

Of course, this study is only limited to the top 12 companies by market cap. The findings may be different if we include all companies. The data is also outdated and it will be interesting to run the same research with more outdated data.

corr_check <- function(Dataset, threshold){
  matriz_cor <- cor(Dataset)
  matriz_cor
  
  for (i in 1:nrow(matriz_cor)){
    correlations <-  which((abs(matriz_cor[i,i:ncol(matriz_cor)]) > threshold) & (matriz_cor[i,i:ncol(matriz_cor)] != 1))
    
    if(length(correlations)> 0){
      lapply(correlations,FUN =  function(x) (cat(paste(colnames(Dataset)[i], "with",colnames(Dataset)[x]), "\n")))
      
    }
  }
}

corr_check(m, 0.7)
## BEAV with FLIR 
## BEAV with IEP 
## BEAV with NDSN 
## BEAV with TRMB 
## CGNX with GRMN 
## CGNX with ILMN 
## COHR with FLIR 
## FLIR with CGNX 
## FLIR with FLIR 
## FLIR with GNTX 
## IEP with COHR 
## IEP with GNTX

Dashboard

The dashboard lets you analyze further sectors and can be accessed below:

https://zahirf.shinyapps.io/Project/

The datatable tab lets you choose between sectors and diaplays all the reelvant financial variables for the latest quarter available

The charts tab sets you choose sectors and which financial variable you want to display. It then displays the line charts for both company and industry average for all 12 companies so users may identify which companies are moving more in line with the industry average.

The correlation tab lets you choose between sectors and select either revenue or equity for the correlation plot. The plot displays the correlation factor between pairs.