Valuation Table
// This source code is subject to the terms of the Mozilla Public License 2.0 at https://mozilla.org/MPL/2.0/
// © kenhuangsy2

//@version=4
study("Valuation Table", overlay=true)
_fin = input(true, "|| Financial Valuation ====================")
on_off = input(title="On/Off Panel?", type=input.bool, defval=true)
_select = input(title="Select Fiscal (ROA/ROE/ROIC/Current Ratio)", defval="FY", options=["FY", "FQ"])
CAGR = input(title="CAGR %", defval=20, minval=1, maxval=100)
//Financial ID-----------------------------------------------------------------------------------------
ROA = financial(syminfo.tickerid,"RETURN_ON_ASSETS", _select)
ROE = financial(syminfo.tickerid,"RETURN_ON_EQUITY", _select)
ROIC = financial(syminfo.tickerid,"RETURN_ON_INVESTED_CAPITAL", _select)
Current_Ratio = financial(syminfo.tickerid,"CURRENT_RATIO", _select)
EPS = financial(syminfo.tickerid, "EARNINGS_PER_SHARE", "TTM")
TSO = financial(syminfo.tickerid, "TOTAL_SHARES_OUTSTANDING", "FQ")
TR = financial(syminfo.tickerid, "TOTAL_REVENUE", "TTM")
GR = financial(syminfo.tickerid, "REVENUE_ONE_YEAR_GROWTH", "FY")
CA = financial(syminfo.tickerid, "TOTAL_CURRENT_ASSETS", "FY")/1000000
TL = financial(syminfo.tickerid, "TOTAL_LIABILITIES", "FY")/1000000
NI = financial(syminfo.tickerid, "NET_INCOME", "FY")/1000000
TA = financial(syminfo.tickerid, "TOTAL_ASSETS", "FY")/1000000
LT_Debt = financial(syminfo.tickerid, "LONG_TERM_DEBT", "FY")/1000000
Cash = financial(syminfo.tickerid, "FREE_CASH_FLOW", "FY")/1000000
CL = financial(syminfo.tickerid, "TOTAL_CURRENT_LIABILITIES", "FY")/1000000
DY = financial(syminfo.tickerid, "DIVIDENDS_YIELD", "FY")
DTE = financial(syminfo.tickerid, "DEBT_TO_EQUITY", "FY")
BVPS = financial(syminfo.tickerid, "BOOK_VALUE_PER_SHARE", "FY")
PEGratio = financial(syminfo.tickerid, "PEG_RATIO", "FY")
GM = financial(syminfo.tickerid, "GROSS_MARGIN", "FY")
PioFscore = financial(syminfo.tickerid, "PIOTROSKI_F_SCORE", "FY")
RevGrowth = financial(syminfo.tickerid, "REVENUE_ONE_YEAR_GROWTH", "FY")/100
EnterpriseValtoEBITDA = financial(syminfo.tickerid, "ENTERPRISE_VALUE_EBITDA", "FY")
EBITDA = financial(syminfo.tickerid, "EBITDA", "FY")
//Calculation-----------------------------------------------------------------------------------------
PriceEarningsRatio = close/EPS
MarketCap = TSO*close
PriceSalesRatio = MarketCap/TR
EV = EnterpriseValtoEBITDA*EBITDA
EVtoFCF = EV/(Cash*1000000)
FCF0 = (Cash*1000000)/TSO
FCF1 = (Cash[52.14] * 1000000)/TSO[52.14]
FCF2 = (Cash[104.28] * 1000000)/TSO[104.28]
FCF3 = (Cash[156.42] * 1000000)/TSO[156.42]
FCF4 = (Cash[208.56] * 1000000)/TSO[208.56]
FCF5 = (Cash[260.7] * 1000000)/TSO[260.7]
FCF6 = (Cash[312.84] * 1000000)/TSO[312.84]
AVGFCF = (FCF0 + FCF1 + FCF2 + FCF3 + FCF4 + FCF5 + FCF6)/7

EBITDA0 = EBITDA
EBITDA1 = EBITDA[52.14]
EBITDA2 = EBITDA[52.14*2]
EBITDA3 = EBITDA[52.14*3]
EBITDA4 = EBITDA[52.14*4]
GrowthE0 = (EBITDA3 - EBITDA4)/abs(EBITDA4)
GrowthE1 = (EBITDA2 - EBITDA3)/abs(EBITDA3)
GrowthE2 = (EBITDA1 - EBITDA2)/abs(EBITDA2)
GrowthE3 = (EBITDA0 - EBITDA1)/abs(EBITDA1)
AVGgrowthE = (GrowthE0 + GrowthE1 + GrowthE2 + GrowthE3)/4
DYFRR = ((AVGFCF/close) + AVGgrowthE)*100
//ROC% Joel Greenblatt Calculation

//get ebit
//get non current assets
EBIT = financial(syminfo.tickerid, "EBIT", "FQ")
noncur = financial(syminfo.tickerid, "TOTAL_NON_CURRENT_ASSETS", "FQ")
accrec = financial(syminfo.tickerid, "ACCOUNTS_RECEIVABLES_NET", "FQ")
tolinv = financial(syminfo.tickerid, "TOTAL_INVENTORY", "FQ")
accpay = financial(syminfo.tickerid, "ACCOUNTS_PAYABLE", "FQ")
othcurrass = financial(syminfo.tickerid, "OTHER_CURRENT_ASSETS_TOTAL", "FQ")
defrev = financial(syminfo.tickerid, "DEFERRED_INCOME_CURRENT", "FQ")
othcurrlia = financial(syminfo.tickerid, "OTHER_CURRENT_LIABILITIES", "FQ")
//NFA (might not work)
netproperty = financial(syminfo.tickerid, "PPE_TOTAL_NET", "FQ")
NFA = netproperty
//(Accounts Receivable + Total Inventories + Other Current Assets) - (Accounts Payable & Accrued Expense + Deferred Revenue + Other Current Liabilities)
NWC = (accrec + tolinv)-(accpay)
ROC = EBIT/((NFA + NWC)/2)
//PEG = close/EPS/CAGR
NetCashPS = (Cash*1000000)/TSO
RealStockPrice = close - NetCashPS
RealPriceEarningsRatio = RealStockPrice/EPS
OwnersEquity = TA-TL
BookValue = TA-TL
ShortMarketCap = MarketCap/1000000
PriceBookRatio = close/BVPS
//Truncate to Decimal---------------------------------------------------------------------------------
truncate(number, decimals) => 
    factor = pow(10, decimals) 
    int(number * factor) / factor 
    
Trunc_1 = truncate(ROA, 2)
Trunc_2 = truncate(ROE, 2)
Trunc_3 = truncate(ROIC, 2)
Trunc_4 = truncate(Current_Ratio, 2)
Trunc_5 = truncate(PriceEarningsRatio, 2)
Trunc_6 = truncate(PEGratio, 2)
Trunc_7 = truncate(PriceSalesRatio, 2)
Trunc_8 = truncate(LT_Debt, 2)
Trunc_9 = truncate(Cash, 2)
Trunc_10 = truncate(RealStockPrice, 2)
Trunc_11 = truncate(EVtoFCF, 2)
Trunc_12 = truncate(TA, 2)
Trunc_13 = truncate(TL, 2)
Trunc_14 = truncate(CA, 2)
Trunc_15 = truncate(CL, 2)
Trunc_16 = truncate(OwnersEquity, 2)
Trunc_17 = truncate(BookValue, 2)
Trunc_18 = truncate(ShortMarketCap, 2)
Trunc_19 = truncate(DY, 2)
Trunc_20 = truncate(DTE, 2)
Trunc_21 = truncate(PriceBookRatio, 2)
Trunc_22 = truncate(NetCashPS, 2)
Trunc_23 = truncate(ROC, 2)
Trunc_24 = truncate(DYFRR, 2)
//---------------------------------------------------------------------------------------------------------
draw_information_panel()=>
    
    var label info_panel = na
    info_1 = string(na)
    info_2 = string(na)
    info_3 = string(na)
    info_4 = string(na)
    info_5 = string(na)
    info_6 = string(na)
    info_7 = string(na)
    info_8 = string(na)
    info_9 = string (na)
    info_10 = string (na)
    info_11 = string (na)
    info_12 = string (na)
    info_13 = string (na)
    info_14 = string (na)
    info_panel_position_x = int(0)
    info_panel_position_y = float(0.0)
    info_panel_color = color(na)
    
    label.delete(info_panel)


//colors
color greater = color.new(color.green, 50)
color lower = color.new(color.red, 50)
color neutral = color.new(color.blue, 50)
color credits = color.new(color.blue, 50)
//plotting
var table t = table.new(position.bottom_right, 2, 12, border_width = 1) // 22 elements + last 2 for by ken
table.cell(t, 0, 0, "ROA: " +tostring(Trunc_1), width = 13, text_color = color.white, bgcolor = (Trunc_1 < 1) ? lower:greater)
table.cell(t, 1, 0, "ROE: " +tostring(Trunc_2), width = 13, text_color = color.white, bgcolor = (Trunc_2 < 1) ? lower:greater)
table.cell(t, 0, 1, "ROIC: " +tostring(Trunc_3), width = 13, text_color = color.white, bgcolor = (Trunc_3 < 1) ? lower:greater)
table.cell(t, 1, 1, "CurrentRatio: " +tostring(Trunc_4), width = 13, text_color = color.white, bgcolor = (Trunc_4 < 2) ? lower:greater)
table.cell(t, 0, 2, "PE: " +tostring(Trunc_5), width = 13, text_color = color.white, bgcolor = (Trunc_5 > 15) ? lower:greater)
table.cell(t, 1, 2, "PEG: " +tostring(Trunc_6), width = 13, text_color = color.white, bgcolor = (Trunc_6 > 1) ? lower:greater)
table.cell(t, 0, 3, "PS: " +tostring(Trunc_7), width = 13, text_color = color.white, bgcolor = (Trunc_7 > 2) ? lower:greater)
table.cell(t, 1, 3, "DebtToEquity: " +tostring(Trunc_20), width = 13, text_color = color.white, bgcolor = (Trunc_20 > 1) ? lower:greater)
table.cell(t, 0, 4, "CashFlowYield: " +tostring(Trunc_11) + "%", width = 13, text_color = color.white, bgcolor = neutral)
table.cell(t, 1, 4, "PriceToBookRatio: " +tostring(Trunc_21), width = 13, text_color = color.white, bgcolor = (Trunc_21 > 1.5) ? lower:greater)
table.cell(t, 0, 5, "NetCashPerShare: " +tostring(Trunc_22), width = 13, text_color = color.white, bgcolor = neutral)
table.cell(t, 1, 5, "LongTermDebt: " +tostring(Trunc_8) + "M", width = 13, text_color = color.white, bgcolor = neutral)
table.cell(t, 0, 6, "Cash: " +tostring(Trunc_9) + "M", width = 13, text_color = color.white, bgcolor = neutral)
table.cell(t, 1, 6, "BookValue: " +tostring(Trunc_17) + "M", width = 13, text_color = color.white, bgcolor = neutral)
table.cell(t, 0, 7, "MarketCap: " +tostring(Trunc_18) + "M", width = 13, text_color = color.white, bgcolor = neutral)
table.cell(t, 1, 7, "DividendYield: " +tostring(Trunc_19), width = 13, text_color = color.white, bgcolor = neutral)
table.cell(t, 0, 8, "TotalAssets: " +tostring(Trunc_12) + "M", width = 13, text_color = color.white, bgcolor = neutral)
table.cell(t, 1, 8, "TotalLiabilities: " +tostring(Trunc_13) + "M", width = 13, text_color = color.white, bgcolor = neutral)
table.cell(t, 0, 9, "CurrentAssets: " +tostring(Trunc_14) + "M", width = 13, text_color = color.white, bgcolor = neutral)
table.cell(t, 1, 9, "CurrentLiabilities: " +tostring(Trunc_15) + "M", width = 14, text_color = color.white, bgcolor = neutral)
table.cell(t, 0, 10, "RealPrice: " +tostring(Trunc_10), width = 13, text_color = color.white, bgcolor = neutral)
table.cell(t, 1, 10, "ROC%: " +tostring(Trunc_23*100) + "%", width = 13, text_color = color.white, bgcolor = neutral)
table.cell(t, 0, 11, "Donald Yacktman: " +tostring(Trunc_24) + "%", width = 13, text_color = color.white, bgcolor = neutral)
table.cell(t, 1, 11, "Valuation Table", width = 13, text_color = color.white, bgcolor = credits)
    
    
    
    
    
    
    