Time Series
When an Analyst sees any Financial Statement and Finance information, TIME SERIES is the first Analysis, comes in picture for research.
use case
Below function, takes a FINANCE LEDGER or any TIME BASED TRANSACTION data set, and returns a cleaned TIME ARRAY.
TIME ARRAY expects, no missing time periods/dates, in this case, this function takes created average roll forwarding entires in returned Data Frame.
This TIME ARRAY DATA FRAME must NOT be used for any Finance information, because it has roll forward entries which will infalte NET AMOUNTS. Instead TIME ARRAY DATA FRAME data is used only for AUTO REGRESSION INTEGERATED MOVING AVERAGE (ARIMA) based predictions only.
Finance Ledger , Balance Sheet, Income Statement and Cash Flow
below is sample Finance Ledger Data, in this Ledger data, we will run p-value to test following Hypothesis.
For a Given Given FISCALY_YEAR and ACCOUNTING_PERIOD, OPERATING EXPENSES are aligned (10%) tolerance range in comparison to BEFORE or AFTER FISCAL_YEAR & ACCOUNTING_PERIOD.
julia> using DataFrames, Plots, Datesjulia> # create dummy data accounts = DataFrame(AS_OF_DATE=Date("1900-01-01", dateformat"y-m-d"), ID = 11000:1000:45000, CLASSIFICATION=repeat([ "OPERATING_EXPENSES","NON-OPERATING_EXPENSES", "ASSETS","LIABILITIES", "NET_WORTH","STATISTICS","REVENUE" ], inner=5), CATEGORY=[ "Travel","Payroll","non-Payroll","Allowance","Cash", "Facility","Supply","Services","Investment","Misc.", "Depreciation","Gain","Service","Retired","Fault.", "Receipt","Accrual","Return","Credit","ROI", "Cash","Funds","Invest","Transfer","Roll-over", "FTE","Members","Non_Members","Temp","Contractors", "Sales","Merchant","Service","Consulting","Subscriptions" ], STATUS="A", DESCR=repeat([ "operating expenses","non-operating expenses", "assets","liability","net-worth","stats","revenue" ], inner=5), ACCOUNT_TYPE=repeat([ "E","E","A","L","N","S","R" ],inner=5));julia> dept = DataFrame(AS_OF_DATE=Date("2000-01-01", dateformat"y-m-d"), ID = 1100:100:1500, CLASSIFICATION=[ "SALES","HR", "IT","BUSINESS","OTHERS" ], CATEGORY=[ "sales","human_resource","IT_Staff","business","others" ], STATUS="A", DESCR=[ "Sales & Marketing","Human Resource","Infomration Technology","Business leaders","other temp" ], DEPT_TYPE=[ "S","H","I","B","O"]);julia> location = DataFrame(AS_OF_DATE=Date("2000-01-01", dateformat"y-m-d"), ID = 11:1:22, CLASSIFICATION=repeat([ "Region A","Region B", "Region C"], inner=4), CATEGORY=repeat([ "Region A","Region B", "Region C"], inner=4), STATUS="A", DESCR=[ "Boston","New York","Philadelphia","Cleveland","Richmond", "Atlanta","Chicago","St. Louis","Minneapolis","Kansas City", "Dallas","San Francisco"], LOCA_TYPE="Physical");julia> ledger = DataFrame( LEDGER = String[], FISCAL_YEAR = Int[], PERIOD = Int[], ORGID = String[], OPER_UNIT = String[], ACCOUNT = Int[], DEPT = Int[], LOCATION = Int[], POSTED_TOTAL = Float64[] );julia> # create 2020 Period 1-12 Actuals Ledger l = "Actuals";julia> fy = 2020;julia> for p = 1:12 for i = 1:10^5 push!(ledger, (l, fy, p, "ABC Inc.", rand(location.CATEGORY), rand(accounts.ID), rand(dept.ID), rand(location.ID), rand()*10^8)) end endjulia> # create 2021 Period 1-4 Actuals Ledger l = "Actuals";julia> fy = 2021;julia> for p = 1:4 for i = 1:10^5 push!(ledger, (l, fy, p, "ABC Inc.", rand(location.CATEGORY), rand(accounts.ID), rand(dept.ID), rand(location.ID), rand()*10^8)) end endjulia> # create 2021 Period 1-4 Budget Ledger l = "Budget";julia> fy = 2021;julia> for p = 1:12 for i = 1:10^5 push!(ledger, (l, fy, p, "ABC Inc.", rand(location.CATEGORY), rand(accounts.ID), rand(dept.ID), rand(location.ID), rand()*10^8)) end endjulia> ledger[:,:]2800000×9 DataFrame Row │ LEDGER FISCAL_YEAR PERIOD ORGID OPER_UNIT ACCOUNT DEPT ⋯ │ String Int64 Int64 String String Int64 Int64 ⋯ ─────────┼────────────────────────────────────────────────────────────────────── 1 │ Actuals 2020 1 ABC Inc. Region B 29000 1200 ⋯ 2 │ Actuals 2020 1 ABC Inc. Region A 30000 1400 3 │ Actuals 2020 1 ABC Inc. Region A 45000 1200 4 │ Actuals 2020 1 ABC Inc. Region C 21000 1400 5 │ Actuals 2020 1 ABC Inc. Region B 37000 1100 ⋯ 6 │ Actuals 2020 1 ABC Inc. Region C 12000 1400 7 │ Actuals 2020 1 ABC Inc. Region A 36000 1400 8 │ Actuals 2020 1 ABC Inc. Region A 30000 1500 ⋮ │ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋱ 2799994 │ Budget 2021 12 ABC Inc. Region B 25000 1500 ⋯ 2799995 │ Budget 2021 12 ABC Inc. Region A 29000 1400 2799996 │ Budget 2021 12 ABC Inc. Region A 40000 1300 2799997 │ Budget 2021 12 ABC Inc. Region A 30000 1400 2799998 │ Budget 2021 12 ABC Inc. Region B 37000 1200 ⋯ 2799999 │ Budget 2021 12 ABC Inc. Region B 21000 1300 2800000 │ Budget 2021 12 ABC Inc. Region B 17000 1400 2 columns and 2799985 rows omitted
TIME ARRAY
getTimeArrayDF(ledger, ACCOUNTCLASSIFICATION = "OPERATINGEXPENSES"; FISCALYEAR =2021, ACCOUNTINGPERIOD = 7
return p-value for given ACCOUNT NODE
getTimeArrayDF(ledger, ACCOUNTCLASSIFICATION = "OPERATINGREVENUE"; FISCALYEAR =2021, ACCOUNTINGPERIOD = 7
return p-value for given ACCOUNT NODE
getTimeArrayDF(ledger, ACCOUNTCLASSIFICATION = "OPERATINGASSETS"; FISCALYEAR =2021, ACCOUNTINGPERIOD = 7
return p-value for given ACCOUNT NODE
getTimeArrayDF(ledger, DEPTCLASSIFICATION = "OPERATINGEXPENSES"; FISCALYEAR =2021, ACCOUNTINGPERIOD = 7
return p-value for given DEPT NODE
getTimeArrayDF(ledger, DEPTCLASSIFICATION = "OPERATINGREVENUE"; FISCALYEAR =2021, ACCOUNTINGPERIOD = 7
return p-value for given DEPT NODE
getTimeArrayDF(ledger, DEPTCLASSIFICATION = "OPERATINGASSETS"; FISCALYEAR =2021, ACCOUNTINGPERIOD = 7
return p-value for given DEPT NODE'
getTimeArrayDF(ledger, REGIONCLASSIFICATION = "OPERATINGEXPENSES"; FISCALYEAR =2021, ACCOUNTINGPERIOD = 7
return p-value for given REGION NODE
getTimeArrayDF(ledger, REGIONCLASSIFICATION = "OPERATINGREVENUE"; FISCALYEAR =2021, ACCOUNTINGPERIOD = 7
return p-value for given REGION NODE
getTimeArrayDF(ledger, REGIONCLASSIFICATION = "OPERATINGASSETS"; FISCALYEAR =2021, ACCOUNTINGPERIOD = 7
return p-value for given REGION NODE