The two best dashboarding tools on the market, Tableau and Power BI, are a total joy to use (well, maybe not Power BI. If you'd tried complex DAX, you'll know what I'm talking about). However, where practical, rather than utilising their data-wrangling offerings (Prep and PowerQuery respectively), I always favour using R.
R is faster, has a very elegant syntax (if you're down with the Tidyverse), is reproducible (because it's code) and has virtually no limit to the variety of data-wrangling (and data-retrieval) tasks that it can accomplish.
But R also offers a variety of presentation / reporting options. R can programmatically construct pdf reports, PowerPoints, Word docs and webpages, all featuring charts and tables that utilize some of the web's latest visualisation libraries (it can also be set up to automatically email out such output on a scheduled basis).
But the main focus for this post is R's interactive dashboard/app solution, generally referred to as "Shiny".
Compared to what native coders of these languages might be able to produce, sure, there might be limitations; but for an R coder to be able to go that next step and produce a working prototype (which may end up being totally sufficient) rather than bringing in a new professional, is considerably valuable.
Given R's popularity in a whole heap of undergraduate science courses these days, expect a growing influx of budding R developers in the coming years. So, to demonstrate R's capability in this regard, I went ahead and made a personal finances app (values shown are semi-dummied, old transaction data).
If your feeling in the mood for a video, here's a walkthrough -that's probably a bit more detailed than you are hoping for (but it serves an additional purpose -so keep reading to find out what that is, at the end -hint, it's to invite you for collaboration).
For those after a few neat, little Tidyverse tricks, here is the github repo with the code. Yes, it is over 2,000 lines long (I like to live dangerously).
UPDATE 29/06/2020 — I have since recreated this app into several parts. I no longer stand by this code as being "all that crash hot". But still keeping it public, so I don't forget where I came from.
Just because R is open source doesn't mean that these dashboard projects are necessarily going to end up cheaper than utilising the popular, paid tools. While you might save yourself the initial subscription outlay, most will appreciate that with a proprietary, drag-and-drop tool you will be able to get up and running in far fewer billable hours. And that using one of these paid tools would probably also make the process much more transferable (there are many more Tableau and Power BI developers out there than Shiny developers).
When utilising code, however, there is a considerable advantage in the area of reproducibility. For example, if creating the same dashboard for multiple clients. With R it will no longer require entirely separate dashboard construction efforts and individual edits to each file whenever updates are requested. With (proper, neat and tidy) code, you will just have to change the relevant function (or a specific parameter) to effect the required change.
But, certainly, getting the aesthetics just right by code can be cumbersome (and incredibly so, for initial attempts). Many of the packages produce okay designs by default and you do have complete control over virtually every element present (but with so many options, sometimes just sizing your chart by dragging or simply "picking" the colour you want from a nice user palette is so much easier).
For my app, there was substantial complexity in the data wrangling. While this could have been done as a separate data pre-preparation process, the iterative and intertwined nature between the wrangling and presentation layer, meant that putting them together in the same software was ideal.
The end result is more seamless (and really the only way that my imaginary end-user would ever actually use the product).
The app includes lots of features that Tableau / Power BI users would be familiar with:
- “click” filters (clicking on one chart to filter another)
- click highlighting (see the greying out of the bars, below the dashed line)
- click parameter updating (the dashed line appearing and being changed)
- tooltips (although I didn’t bother to neaten these up, as it's just an MVP)
There are also some functionalities of the popular paid tools that don't exist (as far as I know) in Shiny dashboards. For example, floating the legend at any position and, also, having "within tool-tip charts".
The key things that my Shiny dashboard was able to do (that couldn't have been done in the paid tools) were:
- Direct uploading of "client" csv’s directly into the process, via the presentation layer
- Downloading a summary of the wrangled csv's into an Excel file (with multiple sheets and formatted tables). This was done so that the user had the option of manually categorising their transactions, in the world's most familiar data entry setting.
- Utilizing a connection to Googlesheets to store the summarised data (as an alternative to using the Excel file) and to utilize retrieval and “write back” functionality, at the click of a button. This saved the user from having to iterate between saving and uploading multiple Excel files (and it also allowed track changes -which was pretty neat).
The main feature of this app was its assistance with categorising the bank transactions. Essentially, as the user collects more keywords, the work required (to correctly identify new transactions) is substantially reduced.
The second page is where the insights begin. In particular the main feature is the spend rates charts. The key point, for me, was that this allowed me to see my overall "burn rate" broken down by specific categories. In particular, how much I have been spending on eating out, public transport and my regular unavoidable expenses.
The third page is the extrapolation, guided by user specifications, about what expense are going to be. This is where you can see how each category is contributing.
Via the Excel spreadsheet (or Googlesheet), there is the ability to
- add in anticipated events that haven't occurred yet
- modify those that have occurred (by specifying ongoing rates / schedules, stipulate that they're not expected to re-occur).
- limit the range of historical data utilised for daily spend rate calculations (eg, only use the last 20 transactions for my eating out to forecast forward, or only the transactions since [some date]).
This project is a classic case of something that seemed really simple at first being revealed to be quite complex, under the hood. Getting it to this point took considerable time. In the end it turned out to be a project that couldn't really have been done with either Tableau or Power BI (at least not as cohesively), so I guess it is not really the fair to be making these comparisons.
For secure (authentication-enabled), web-based delivery system for such an app, Shinyapps.io offer this but there are also plenty of self-hosting options available. However, because I would expect most individuals and businesses to be pretty reluctant about uploading their private transaction data to a third party (even if it was a considerably improved, secure, online version of this app) -this is why I have kept the file as something that can be used locally on one's personal machine.
Also, I have decided to make this project Open Source. Hopefully there are parts of it that might be some benefit to the R community. The project itself could also stand to benefit from their recommendations / suggestions, so feel free to use the Youtube video and the script to detect possible improvements. Feel free to shoot me an email if you would like to collaborate.