Financial formulas from Excel to Shiny?

shiny
1

Hi, folks.
I have a huge excel spread sheet with multiple formulas for financial modelling.
Some inputs are required but the majority of stuff is automatically calculated.
Several hundred formulas in total.
We are looking to reproduce this spreadsheet in Shiny format. And connect it to Postgres.
What is the best way to do it? Any good resources, guides, app examples?
Curious about functionality and design.

Thanks!

2 Likes
2

It will help you considerably if you don't think of this as "converting to Shiny format" and, rather, think of converting your logic to R. Then you put a Shiny UI around some R functions.

There is no magic bullet. You are porting from one language (Excel) to another (R). In my experience, the work flow looks something like this:

  • Determine the input data sources and make sure you can connect to them from R and get the needed data
  • Document the logic of the spreadsheet. Write down what it does and why.
  • Determine from the above documentation what functions need to be written... probably not a full list, but some stuff will be obvious.
  • Incrementally write each function in R, test it and make sure you get what you were expecting.
  • Create each visualization you will need for your output: tables, graphs, etc.
  • after all that is created and tested then wrap Shiny around the whole thing.

If you have some specific questions about mapping excel functions to R, feel free to ask.

7 Likes
3

With that said, keep in mind that the poor man's hack is to use R to extract data, use the openxlsx package to slam the data into your spreadsheet, refresh the logic, then extract out the data you want to present in Shiny. Now that's an ugly arse hack that should make you feel dirty for considering, but sometimes constraints drive us to interesting optimizations.

2 Likes
4

I would also recommend that you look around for implementations of your functionalities in different packages, as there are a lot of financial packages available in R already. That will save you considerable amounts of time if you do not have to write all of these functions from scratch.

A good place to start would be looking at the packages from Business Science

3 Likes