I am trying to use the below code to make a dynamic balance sheet that I use for a test. but the currency function gives me an error that I am having difficulty solving. It gives me the following error: Error in as_numeric(x) : 'list' object cannot be coerced to type 'double'
# Define account names
Accounts <- data.frame(c("","(Millions of USD)","Current assets:","Cash","Accounts Receivable","Prepaid expenses","Inventory","Total current assets","","Property & Equipment","Goodwill","Total Assets","Liabilities","Accounts Payable","Accrued expenses","Unearned revenue","Total Current Liabilities","Longterm Debt","Other Long Term Liabilities","Total Liabilities","Shareholder's Equity","Equity Capital","Retained Earnings","Shareholder's Equity","Total Liabilities & Shareholder's Equity"))
# Function to generate financial data for a given year
generate_financials <- function(year) {
set.seed(123) # Set seed for reproducibility
# Generate random financial data
cash <- sample(89000:217000, 1)
AR <- 0.25 * cash
pexp <- sample(4800:6800, 1)
inv <- sample(7800:11900, 1)
tca <- sum(cash, AR, pexp, inv)
PPE <- 2.7 * cash
gw <- round(pi / 2.7 * PPE, 2)
TAss <- tca + PPE + gw
AP <- 0.03 * TAss
acexp <- 0.5 * AP
UR <- 1/3 * AP
Tliab <- sum(AP, acexp, UR)
LTD <- TAss * 0.5
OLTD <- 0.23 * LTD
TL <- sum(Tliab, LTD, OLTD)
RE <- TAss - TL - sample(35000:76000, 1)
EQ <- TAss - TL - RE
SE <- EQ + RE
TLSE <- SE + TL
# Return financial data for the given year
return(c("", "", "", cash, AR, pexp, inv, tca, "", PPE, gw, TAss, "", AP, acexp, UR, Tliab, LTD, OLTD, TL, "", EQ, RE, SE, TLSE))
}
# Create a data frame to store financial data for each year
financial_data <- data.frame(matrix(ncol = 5, nrow = length(Accounts)))
colnames(financial_data) <- c(lubridate::year(Sys.Date()), lubridate::year(Sys.Date()) - 1, lubridate::year(Sys.Date()) - 2, lubridate::year(Sys.Date()) - 3, lubridate::year(Sys.Date()) - 4)
# Populate the data frame with financial data for each year
for (i in 1:ncol(financial_data)) {
financial_data[, i] <- generate_financials(colnames(financial_data)[i])
}
bs <- cbind(Accounts,financial_data)
colnames(bs)[1] <- "Accounts"
bs
rownames(bs) <- Accounts
# Format numeric values as currency
bs[,-1] <- formattable::currency(bs[,-1], symbol = "$", digits = 2L, big.mark = ",")
I have tried to set financial_data to as.numeric. I have tried to use lapply(x,as.numeric)
Help!
The issue is that you pass a data.frame to formattable::currency which expects a vector as its first argument. Instead use e.g. lapply to loop over the columns you want to format as currencies.
Note: Your code to create the example data did not work, so I fixed and simplified that part too.
set.seed(123)
# Create a data frame to store financial data for each year
financial_data <- lapply(
2023:2019,
generate_financials
) |>
setNames(2023:2019) |>
data.frame(check.names = FALSE)
bs <- cbind(Accounts, financial_data)
names(bs)[1] <- "Accounts"
bs[, -1] <- lapply(bs[, -1], \(x)
formattable::currency(
x,
symbol = "$",
digits = 2L, big.mark = ","
))
head(bs)
#> Accounts 2023 2022 2021 2020
#> 1 NA NA NA NA
#> 2 (Millions of USD) NA NA NA NA
#> 3 Current assets: NA NA NA NA
#> 4 Cash $140,662.00 $213,021.00 $105,127.00 $173,486.00
#> 5 Accounts Receivable $35,165.50 $53,255.25 $26,281.75 $43,371.50
#> 6 Prepaid expenses $4,978.00 $6,122.00 $5,508.00 $6,249.00
#> 2019
#> 1 NA
#> 2 NA
#> 3 NA
#> 4 $103,182.00
#> 5 $25,795.50
#> 6 $5,172.00