When I started freelancing, I quickly realized that administration and project management eat into your time like nothing else. Many days I would spend more than half my working time on tasks that did not generate any income – answering emails, managing appointments, recording when I worked for how long on what and writing invoices.

Over time I went from filling every invoice and time sheet by hand in word, via paying for an overpriced online time tracking and invoicing tool to using a custom R package for administration.

The general setup

In order to generate invoices and track my time spent on projects and tasks I need several data sets:

• a data set containing names and addresses of clients, perhaps with additional information such as reference numbers etc. For simplicity, this also contains my address and VAT number.
• a data set containing the hours I worked on each specific task and project for each client in detail.

Both of these are simply excel files somewhere on my drive. I don’t add clients often enough that an automated solution would save me time, but I have switch between clients and tasks often enough during a day that I need to closely keep track if I don’t want to forget what I did.

In order to create the invoices, I also need a template. In this case I’m using R Markdown with LaTex, because I want to render my invoices in pdf. You could of course also use html, no one is stopping you. This template needs to be fairly general, because I have clients from multiple countries (so need to pay attention to tax issues), some of them organizations and some individuals.

Information is passed to the template through a bunch of R functions that select and aggregate the data from my client list and time sheet. All of these features are conveniently served with an R package that lives in a private git repository.

admintools: the example package

I published an example of how such a package could look on my GitHub and called it admintools. The package is designed only as an example, and is not the actual package I use. It is a bit more general, which unfortunately comes at the cost of requiring a couple more arguments to the functions. It is therefore less easy to use than a package that is tailored to your own needs, and I encourage everyone to use it as a starting point for their own package.

In any case, you can install it via devtools:

devtools::install_github("jonas-schropp/admintools")

And then load it like any other library

library(admintools)

admintools comes with two synthetic data sets to illustrate usage and to make starting out and testing easier: addresses and timesheets. addresses is a data.frame containing contact information for several hypothetical clients. For simplicity it also contains the “sender” address in the first line in order to facilitate creating the header for invoices.

str(addresses)

## 'data.frame':	10 obs. of  13 variables:
##  $ Client      : chr  "talynsight" "Client A" "Client B" "Client C" ...
##  $ street      : chr  "Sepapaja tn 6" "23 Random Street" "84 Some Street" "8 Avenue" ...
##  $ city        : chr  "Tallinn" "San Francisco" "Los Angeles" "New York" ...
##  $ zip_code    : chr  "11415" "CA 94131" "CA 90027" "NY 10002" ...
##  $ country     : chr  "Estonia" "USA" "USA" "USA" ...
##  $ email       : chr  "jonas.schropp@protonmail.com" "email@clienta.com" "email@clientb.com" "email@clientc.com" ...
##  $ phone       : chr  NA "+1(0)12345678" "+1(0)12345678" "+1(0)12345678" ...
##  $ vat_id      : chr  "EE102496143" "12345678" "12345678" "12345678" ...
##  $ organization: chr  "talynsight OÜ" "Client A Ltd" "Client B University" "Client C Ltd" ...
##  $ ref         : chr  "Jonas Schropp" NA "Albert Einstein" "Bill Gates" ...
##  $ ref_code    : chr  NA NA "B-123" "44E" ...
##  $ ref_email   : chr  "jonas.schropp@protonmail.com" NA "albi@email.com" "billyboy@microsoft.com" ...
##  $ VAT         : chr  "Reverse Charge" "Reverse Charge" "Reverse Charge" "Reverse Charge" ...

timesheets is an example how a time sheet to track work could look like.

str(timesheet)

## 'data.frame':	80 obs. of  9 variables:
##  $ Date        : Date, format: "2022-01-07" "2022-01-08" ...
##  $ Client      : chr  "Client B" "Client B" "Client B" "Client C" ...
##  $ Project     : chr  "Project 1" "Project 3" "Project 2" "Project X" ...
##  $ With        : chr  "PI 2" "PI 1" "PI 2" NA ...
##  $ Task        : chr  "data analysis" "data analysis" "reporting" "meeting" ...
##  $ Description : chr  "Stately, plump Buck Mulligan came from the stairhead, bearing a bowl of lather on which a \n    mirror and a ra"| __truncated__ " —Introibo ad altare Dei." "Halted, he peered down the dark winding stairs and called out coarsely:" " —Come up, Kinch! Come up, you fearful jesuit!" ...
##  $ Hours       : int  4 5 8 9 6 7 5 9 8 9 ...
##  $ Hourly      : num  90 90 90 120 90 120 90 120 120 120 ...
##  $ Compensation: num  360 450 720 1080 540 840 450 1080 960 1080 ...

The package has two main functions: comp_table and render_invoice.

• comp_table is mostly a wrapper over several dplyr functions for aggregating and summarizing data that can be used to easily and quickly transform data from the time sheet into a format that is useful for reporting.
• render_invoice renders an invoice in pdf format, using either the template supplied with this package or a template of your own. If you want to use your own template, try to work around the existing one and only change the styling of the output rather than the content - otherwise the code will likely break because render_invoice performs quite a lot of computation to transform the R output into LaTeX code even before passing it on to rmarkdown.

Generating Reports

comp_table, being a simple wrapper to aggregate and summarize the time sheet data, might seem somewhat superfluous. After all, you could just use dplyr, data.table or even base. So why comp_table? Mostly because in addition to aggregating the time sheet in whichever way you desire, it also provides a way to standardize the output and pass it on to render_invoice without all the clutter.

Consequently it also has a long host of arguments, including some to tell it what the relevant columns in the data set are called. These would not be necessary in your own package:

comp_table(
  data,
  agg_by = c("Month", "Task", "Project"),
  min_date = NULL,
  max_date = NULL,
  proj_name = NULL,
  client_name = NULL,
  available_comp = NULL,
  date = "Date",
  hours = "Hours",
  compensation = "Compensation",
  project = "Project",
  task = "Task",
  client = "Client"
)

In any case, it can be used to prepare simple reports such as the one below:

Not very pretty, I know, but that’s not the point. You can style it any way you like.

Rendering Invoices

comp_table makes it easy to pass the data to render_invoice in the right format. If you want to adapt the invoice to your needs, the arguments to render_invoice already provide a lot of options:

render_invoice(
  data,
  client,
  address,
  proj_name = NULL,
  inv_number = 10001,
  with = NULL,
  discount = NULL,
  VAT = "20%",
  currency = "Euro",
  iban = NULL,
  bic = NULL,
  swift = NULL,
  bank = NULL,
  intro = NULL,
  timelimit = NULL,
  template = "invoice-template",
  filename = paste0(Sys.Date(), "-SENDER-RECIPIENT-NUMBER"),
  dir = NULL
)

Some of these are entirely unnecessary if you use your own package instead of admintools, such as iban, bic, swift, bank and timelimit. These need to be set for obvious reasons, but if you’re a single freelancer or manage a small agency, you likely don’t use more than one business banking account and rely on defaults for your invoices in other areas too.

render_invoice does a lot of computation under the hood in order to format the data to pass it on to R Markdown. Most importantly, every table is already transformed to LaTex code before passing it on to the invoice template. The template is then only used for styling that output. I believe this separation of tasks between R and R Markdown makes it relatively easy to change the default template or pass on different parameters that I didn’t anticipate in the package.

Finally, rendering the invoice is trivial. We simply aggregate our time sheet however we want to present the work on the invoice:

p1 <- timesheet |>
  comp_table(
    client_name = "Client B",
    proj_name = "Project 1",
    agg_by = "Month",
    min_date = as.Date("2022-01-01"),
    max_date = as.Date("2022-04-30")
  ) 

Select the sender and the receiver from the client data base:

sender <- filter(addresses, Client == "talynsight")
receiver <- filter(addresses, Client == "Client B")

And pass the information to render_invoice.

render_invoice(
  data = p1,
  client = receiver,
  address = sender,
  proj_name = "Project 1",
  inv_number = "10098",
  iban = "DE12345678",
  bic = "BLABLA01",
  bank = "Parkbank",
  with = "PI Jim Lahey, Trailer Park Supervisor,",
  discount = "10%",
  VAT = "Reverse Charge",
  filename = paste0(Sys.Date(), "-talynsightOÜ-ClientB-10098.pdf")
)

Which will return the fully rendered pdf invoice:

Much more customize-able than any (usually paid!) program I have found online. Now the question is: what else can I automate with R Markdown that might save me time and nerves? What do you use R Markdown for except ad hoc reports?

Citation