R Recipe: Shiny Tables from Excel Templates

In this recipe we'll see how to use table templates created in Excel. The templates come with all the styling, indenting, and positioning that Excel supports intuitively.

Why?

A task where Excel still beats R/Shiny hands down is in quickly designing professional and good looking tables. R's table libraries are great, however I've found that applying styling to a single cell in a table almost impossible or extremely clunky. This recipe will provide a way to style your perfect table intuitively in Excel, and directly use it as a template in Shiny. This approach also works well when you want to replicate an existing Excel sheet/table in Shiny.

Income Statement Generator

The example Shiny app we'll be working through will take a stock, and generate its latest quarterly income statement using an existing Excel Income Statment as a template. What's more, the income statments we generate in the Shiny app will be fully copy-pastable back in Excel, with all table indetations and styling preserved. The income statement data will be provided by my {yfinance} package, which wraps the yahoo finance API and can be installed with remotes::install_github("ljupch0/yfinance").

Ingredients

shiny

shinydashboard

shinydashboardPlus

shinyWidgets

glue

dplyr

yfinance

Getting a Template

To get started, we'll take a ready made Excel income statement template from the internet. I selected this one. With a little cleanup, this is what we're starting with.

Now we define all the variables in the locations where we want to plug in values in Shiny. All the variables need to be in the form of {variable_name}, so that we can easily use the glue package later on. After doing that and deleting the more detailed items, our template looks like this. Download this Excel here, so that you can follow the rest of this recipe in RStudio or wherever you write your shiny apps.

Next is to save the Excel file as a web page. This is where most of the magic happens - Excel converts the table to HTML and styles it with CSS. You can open the saved file in a browser to preview how the table will look in your shiny app. There are some limitations, like cell content not being able to overflow into other cells, so make some adjustments to make sure the final result is as desired.

Once the table is acceptable, we need to extract only the html that forms the table wihtout the extra code Excel provides you. My way of doing it is to copy the table html in developer tools. If you're not acquainted, open the saved html file in a browser, right click > Inspect to open developer tools. In the elements tab, find the line starting with <table>, right click > Copy > Copy Element. With the html copied, we now go to the Shiny app to build the table function.


      table <- function(variable_names) {
        tagList(HTML(glue::glue("

        Paste here the html copied from devtools

                                ")
        ))

      }

The html we just copied needs to be pasted as a string. As function arguments we need to provide the variables that we defined in Excel like company_name, date, sales and the rest. So the final function looks like this.


table <- function(company_name, date, quarter, sales, cost_goods, gross_profit, SGnA, RnD, total_operating_expenses, operating_income,
                    other_income_net, interest_expense, EBT, income_tax, income_ops, net_income) {
  tagList(HTML(glue::glue('

    <table border="0" cellpadding="0" cellspacing="0" width="954" style="border-collapse:
   collapse;table-layout:fixed;width:715pt">
   <colgroup><col class="xl65" width="19" style="mso-width-source:userset;mso-width-alt:608;
   width:14pt">
   <col class="xl65" width="43" style="mso-width-source:userset;mso-width-alt:1376;
   width:32pt">
   <col class="xl65" width="325" style="mso-width-source:userset;mso-width-alt:10400;
   width:244pt">
   <col class="xl65" width="127" style="mso-width-source:userset;mso-width-alt:4064;
   width:95pt">
   <col class="xl65" width="124" style="mso-width-source:userset;mso-width-alt:3968;
   width:93pt">
   <col class="xl65" width="19" style="mso-width-source:userset;mso-width-alt:608;
   width:14pt">
   <col class="xl65" width="69" style="mso-width-source:userset;mso-width-alt:2208;
   width:52pt">
   <col class="xl65" width="228" style="mso-width-source:userset;mso-width-alt:7296;
   width:171pt">
   </colgroup><tbody><tr class="xl66" height="35" style="mso-height-source:userset;height:26.25pt">
    <td height="35" class="xl66" width="19" style="height:26.25pt;width:14pt"><a name="Print_Area"></a></td>
    <td class="xl101" colspan="2" width="368" style="mso-ignore:colspan;width:276pt">{company_name}</td>
    <td colspan="2" class="xl103" width="251" style="width:188pt">Income Statement</td>
    <td class="xl66" width="19" style="width:14pt"></td>
    <td class="xl66" width="69" style="width:52pt"></td>
    <td class="xl66" width="228" style="width:171pt"></td>
   </tr>
   <tr class="xl66" height="17" style="height:12.75pt">
    <td height="17" class="xl67" style="height:12.75pt"></td>
    <td class="xl66"></td>
    <td class="xl72"></td>
    <td colspan="2" class="xl102">For the Quarter ending {date}</td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl68"></td>
   </tr>
   <tr class="xl66" height="17" style="height:12.75pt">
    <td height="17" class="xl66" style="height:12.75pt"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl69"></td>
   </tr>
   <tr class="xl66" height="24" style="height:18.0pt">
    <td height="24" class="xl66" style="height:18.0pt"></td>
    <td class="xl76" colspan="2" style="mso-ignore:colspan">Revenue</td>
    <td class="xl76">&nbsp;</td>
    <td class="xl94">{quarter}</td>
    <td class="xl70"></td>
    <td class="xl70"></td>
    <td class="xl70"></td>
   </tr>
   <tr class="xl66" height="17" style="height:12.75pt">
    <td height="17" class="xl66" style="height:12.75pt"></td>
    <td class="xl66"></td>
    <td class="xl72">Gross sales</td>
    <td class="xl72"></td>
    <td class="xl93"><span style="mso-spacerun:yes">&nbsp;</span>{sales}<span style="mso-spacerun:yes">&nbsp;</span></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
   </tr>
   <tr class="xl66" height="21" style="height:15.75pt">
    <td height="21" class="xl66" style="height:15.75pt"></td>
    <td class="xl66"></td>
    <td class="xl74">Net Sales</td>
    <td class="xl74">&nbsp;</td>
    <td class="xl95"><span style="mso-spacerun:yes">&nbsp;</span>{sales}<span style="mso-spacerun:yes">&nbsp;</span></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
   </tr>
   <tr class="xl66" height="17" style="height:12.75pt">
    <td height="17" class="xl66" style="height:12.75pt"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl96"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
   </tr>
   <tr class="xl66" height="24" style="height:18.0pt">
    <td height="24" class="xl66" style="height:18.0pt"></td>
    <td class="xl76" colspan="2" style="mso-ignore:colspan">Cost of Goods Sold</td>
    <td class="xl76">&nbsp;</td>
    <td class="xl97">&nbsp;</td>
    <td class="xl70"></td>
    <td class="xl70"></td>
    <td class="xl70"></td>
   </tr>
   <tr class="xl66" height="17" style="height:12.75pt">
    <td height="17" class="xl66" style="height:12.75pt"></td>
    <td class="xl66"></td>
    <td class="xl72">Cost of Goods Sold</td>
    <td class="xl72"></td>
    <td class="xl93"><span style="mso-spacerun:yes">&nbsp;</span>{cost_goods}<span style="mso-spacerun:yes">&nbsp;</span></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl92"></td>
   </tr>
   <tr class="xl66" height="17" style="height:12.75pt">
    <td height="17" class="xl66" style="height:12.75pt"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl96"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
   </tr>
   <tr class="xl66" height="21" style="height:15.75pt">
    <td height="21" class="xl66" style="height:15.75pt"></td>
    <td class="xl66"></td>
    <td class="xl74">Gross Profit (Loss)</td>
    <td class="xl74">&nbsp;</td>
    <td class="xl95"><span style="mso-spacerun:yes">&nbsp;</span>{gross_profit}<span style="mso-spacerun:yes">&nbsp;</span></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
   </tr>
   <tr class="xl66" height="17" style="height:12.75pt">
    <td height="17" class="xl66" style="height:12.75pt"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl96"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
   </tr>
   <tr class="xl66" height="24" style="height:18.0pt">
    <td height="24" class="xl71" style="height:18.0pt">[42]</td>
    <td class="xl76" colspan="2" style="mso-ignore:colspan">Expenses</td>
    <td class="xl76">&nbsp;</td>
    <td class="xl97">&nbsp;</td>
    <td class="xl70"></td>
    <td class="xl70"></td>
    <td class="xl70"></td>
   </tr>
   <tr class="xl66" height="17" style="height:12.75pt">
    <td height="17" class="xl66" style="height:12.75pt"></td>
    <td class="xl66"></td>
    <td class="xl72">Sales, General &amp; Administrative</td>
    <td class="xl72"></td>
    <td class="xl93"><span style="mso-spacerun:yes">&nbsp;</span>{SGnA}<span style="mso-spacerun:yes">&nbsp;</span></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
   </tr>
   <tr class="xl66" height="17" style="height:12.75pt">
    <td height="17" class="xl66" style="height:12.75pt"></td>
    <td class="xl66"></td>
    <td class="xl72">Research and Development</td>
    <td class="xl72"></td>
    <td class="xl93" style="border-top:none"><span style="mso-spacerun:yes">&nbsp;</span>{RnD}<span style="mso-spacerun:yes">&nbsp;</span></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
   </tr>
   <tr class="xl66" height="17" style="height:12.75pt">
    <td height="17" class="xl66" style="height:12.75pt"></td>
    <td class="xl66"></td>
    <td class="xl73">Total Operating Expenses</td>
    <td class="xl73">&nbsp;</td>
    <td class="xl98"><span style="mso-spacerun:yes">&nbsp;</span>{total_operating_expenses}<span style="mso-spacerun:yes">&nbsp;</span></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
   </tr>
   <tr class="xl66" height="17" style="height:12.75pt">
    <td height="17" class="xl66" style="height:12.75pt"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl96"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
   </tr>
   <tr class="xl66" height="21" style="height:15.75pt">
    <td height="21" class="xl66" style="height:15.75pt"></td>
    <td colspan="2" class="xl91">Operating Income (Loss)</td>
    <td class="xl91">&nbsp;</td>
    <td class="xl99">{operating_income}</td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
   </tr>
   <tr class="xl66" height="17" style="height:12.75pt">
    <td height="17" class="xl66" style="height:12.75pt"></td>
    <td class="xl66"></td>
    <td class="xl72">Other Income or Loss</td>
    <td class="xl72"></td>
    <td class="xl93"><span style="mso-spacerun:yes">&nbsp;</span>{other_income_net}<span style="mso-spacerun:yes">&nbsp;</span></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
   </tr>
   <tr class="xl66" height="17" style="height:12.75pt">
    <td height="17" class="xl66" style="height:12.75pt"></td>
    <td class="xl66"></td>
    <td class="xl72">Interest Expense</td>
    <td class="xl72"></td>
    <td class="xl93" style="border-top:none"><span style="mso-spacerun:yes">&nbsp;</span>{interest_expense}<span style="mso-spacerun:yes">&nbsp;</span></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
   </tr>
   <tr class="xl66" height="21" style="height:15.75pt">
    <td height="21" class="xl66" style="height:15.75pt"></td>
    <td colspan="2" class="xl91">Earnings Before Tax</td>
    <td class="xl91">&nbsp;</td>
    <td class="xl99">{EBT}</td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
   </tr>
   <tr class="xl66" height="17" style="height:12.75pt">
    <td height="17" class="xl66" style="height:12.75pt"></td>
    <td class="xl66"></td>
    <td class="xl72">(Less income tax expense)</td>
    <td class="xl72"></td>
    <td class="xl93"><span style="mso-spacerun:yes">&nbsp;</span>{income_tax}<span style="mso-spacerun:yes">&nbsp;</span></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
   </tr>
   <tr class="xl66" height="21" style="height:15.75pt">
    <td height="21" class="xl66" style="height:15.75pt"></td>
    <td colspan="2" class="xl91">Income From Continuing Operations</td>
    <td class="xl91">&nbsp;</td>
    <td class="xl99">{income_ops}</td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
   </tr>
   <tr class="xl66" height="17" style="height:12.75pt">
    <td height="17" class="xl66" style="height:12.75pt"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl96"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
   </tr>
   <tr class="xl66" height="22" style="height:16.5pt">
    <td height="22" class="xl66" style="height:16.5pt"></td>
    <td colspan="2" class="xl90">Net Income</td>
    <td class="xl90">&nbsp;</td>
    <td class="xl100"><span style="mso-spacerun:yes">&nbsp;</span>{net_income}<span style="mso-spacerun:yes">&nbsp;</span></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
   </tr>
   <tr class="xl66" height="18" style="height:13.5pt">
    <td height="18" class="xl66" style="height:13.5pt"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
   </tr>
   <tr class="xl66" height="17" style="height:12.75pt">
    <td height="17" class="xl66" style="height:12.75pt"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
    <td class="xl66"></td>
   </tr>
   <!--[if supportMisalignedColumns]-->
   <tr height="0" style="display:none">
    <td width="19" style="width:14pt"></td>
    <td width="43" style="width:32pt"></td>
    <td width="325" style="width:244pt"></td>
    <td width="127" style="width:95pt"></td>
    <td width="124" style="width:93pt"></td>
    <td width="19" style="width:14pt"></td>
    <td width="69" style="width:52pt"></td>
    <td width="228" style="width:171pt"></td>
   </tr>
   <!--[endif]-->
  </tbody></table>

                            ')
    ))

  }

The final result isn't exactly beautiful code, yet again no computer generated code is. When this function is called, R will take the values you provide and plug them in their place in the html. However this is only the html, and while it will generate a table, it won't look good as it doesn't cotain all the CSS needed for styling. We need to include it in a sperate function.

When you save the Excel file as a webpage, in addition to the html file Excel will also save a folder with the same name. Within the folder, open the stylesheet file in any text editor. Once open, copy the contents. Paste them in the CSS function.


    table_css <- function() {
      tags$body(tags$style(HTML('

            Paste the CSS here

    ')))
    }

This function is simpler because it takes no arguments. When it's called it just updates the global CSS of the shiny app to include the styling for our table. Caution: it is possible for some parts of this CSS to clash with other parts of the CSS of your shiny app. Check that after loading the table everything looks as it should.

Another thing that need to be done is to escape all the back slashes. So in your text editor you need to replace all \ with \\. The final function looks like this.


  table_css <- function() {
    tags$body(tags$style(HTML('

          tr
  	{mso-height-source:auto;}
  col
  	{mso-width-source:auto;}
  br
  	{mso-data-placement:same-cell;}
  .style43
  	{mso-number-format:"_\\(\\0022$\\0022* \\#\\,\\#\\#0\\.00_\\)\\;_\\(\\0022$\\0022* \\\\\\(\\#\\,\\#\\#0\\.00\\\\\\)\\;_\\(\\0022$\\0022* \\0022-\\0022??_\\)\\;_\\(\\@_\\)";
  	mso-style-name:Currency;
  	mso-style-id:4;}
  .style50
  	{color:blue;
  	font-size:10.0pt;
  	font-weight:400;
  	font-style:normal;
  	text-decoration:underline;
  	text-underline-style:single;
  	font-family:Arial, sans-serif;
  	mso-font-charset:0;
  	mso-style-name:Hyperlink;
  	mso-style-id:8;}
  .style0
  	{mso-number-format:General;
  	text-align:general;
  	vertical-align:bottom;
  	white-space:nowrap;
  	mso-rotate:0;
  	mso-background-source:auto;
  	mso-pattern:auto;
  	color:windowtext;
  	font-size:11.0pt;
  	font-weight:400;
  	font-style:normal;
  	text-decoration:none;
  	font-family:Arial, sans-serif;
  	mso-font-charset:0;
  	border:none;
  	mso-protection:locked visible;
  	mso-style-name:Normal;
  	mso-style-id:0;}
  td
  	{mso-style-parent:style0;
  	padding:0px;
  	mso-ignore:padding;
  	color:windowtext;
  	font-size:11.0pt;
  	font-weight:400;
  	font-style:normal;
  	text-decoration:none;
  	font-family:Arial, sans-serif;
  	mso-font-charset:0;
  	mso-number-format:General;
  	text-align:general;
  	vertical-align:bottom;
  	border:none;
  	mso-background-source:auto;
  	mso-pattern:auto;
  	mso-protection:locked visible;
  	white-space:nowrap;
  	mso-rotate:0;}
  .xl65
  	{mso-style-parent:style0;
  	font-size:10.0pt;}
  .xl66
  	{mso-style-parent:style0;
  	font-size:10.0pt;
  	vertical-align:middle;}
  .xl67
  	{mso-style-parent:style0;
  	font-size:10.0pt;
  	text-align:left;
  	vertical-align:middle;}
  .xl68
  	{mso-style-parent:style50;
  	color:blue;
  	font-size:10.0pt;
  	text-decoration:underline;
  	text-underline-style:single;
  	vertical-align:middle;}
  .xl69
  	{mso-style-parent:style0;
  	font-size:8.0pt;
  	vertical-align:middle;}
  .xl70
  	{mso-style-parent:style0;
  	color:white;
  	font-size:10.0pt;
  	vertical-align:middle;}
  .xl71
  	{mso-style-parent:style0;
  	color:white;
  	font-size:2.0pt;
  	text-align:right;
  	vertical-align:middle;}
  .xl72
  	{mso-style-parent:style0;
  	font-size:10.0pt;
  	vertical-align:middle;
  	mso-protection:unlocked visible;}
  .xl73
  	{mso-style-parent:style0;
  	font-size:10.0pt;
  	vertical-align:middle;
  	background:#F2F2F2;
  	mso-pattern:black none;}
  .xl74
  	{mso-style-parent:style0;
  	font-size:12.0pt;
  	font-weight:700;
  	vertical-align:middle;
  	background:#F2F2F2;
  	mso-pattern:black none;}
  .xl75
  	{mso-style-parent:style0;
  	font-size:16.0pt;
  	vertical-align:middle;
  	mso-protection:unlocked visible;}
  .xl76
  	{mso-style-parent:style0;
  	color:white;
  	font-size:14.0pt;
  	font-weight:700;
  	vertical-align:middle;
  	background:#3A5D9C;
  	mso-pattern:black none;}
  .xl77
  	{mso-style-parent:style0;
  	color:white;
  	font-size:18.0pt;
  	font-weight:700;
  	text-align:left;
  	vertical-align:middle;
  	border-top:none;
  	border-right:none;
  	border-bottom:.5pt solid #3464AB;
  	border-left:none;
  	background:#3464AB;
  	mso-pattern:black none;
  	padding-left:12px;
  	mso-char-indent-count:1;}
  .xl78
  	{mso-style-parent:style0;
  	color:white;
  	font-size:18.0pt;
  	font-weight:700;
  	text-align:left;
  	vertical-align:middle;
  	border-top:none;
  	border-right:none;
  	border-bottom:.5pt solid #3464AB;
  	border-left:none;
  	background:#3464AB;
  	mso-pattern:black none;}
  .xl79
  	{mso-style-parent:style0;
  	color:white;
  	font-size:18.0pt;
  	vertical-align:middle;
  	border-top:none;
  	border-right:none;
  	border-bottom:.5pt solid #3464AB;
  	border-left:none;
  	background:#3464AB;
  	mso-pattern:black none;}
  .xl80
  	{mso-style-parent:style0;
  	font-size:10.0pt;
  	background:white;
  	mso-pattern:black none;}
  .xl81
  	{mso-style-parent:style0;
  	font-size:12.0pt;
  	text-align:left;
  	background:white;
  	mso-pattern:black none;
  	white-space:normal;
  	padding-left:12px;
  	mso-char-indent-count:1;}
  .xl82
  	{mso-style-parent:style0;
  	background:white;
  	mso-pattern:black none;}
  .xl83
  	{mso-style-parent:style0;
  	font-size:12.0pt;
  	background:white;
  	mso-pattern:black none;}
  .xl84
  	{mso-style-parent:style50;
  	color:blue;
  	font-size:10.0pt;
  	text-decoration:underline;
  	text-underline-style:single;
  	text-align:left;
  	background:white;
  	mso-pattern:black none;
  	white-space:normal;}
  .xl85
  	{mso-style-parent:style0;
  	font-size:12.0pt;
  	text-align:left;
  	background:white;
  	mso-pattern:black none;
  	white-space:normal;}
  .xl86
  	{mso-style-parent:style0;
  	font-size:12.0pt;
  	font-weight:700;
  	text-align:left;
  	background:white;
  	mso-pattern:black none;
  	white-space:normal;}
  .xl87
  	{mso-style-parent:style0;
  	color:blue;
  	font-size:12.0pt;
  	text-decoration:underline;
  	text-underline-style:single;
  	text-align:left;
  	background:white;
  	mso-pattern:black none;
  	white-space:normal;}
  .xl88
  	{mso-style-parent:style0;
  	font-size:12.0pt;
  	text-align:left;
  	background:white;
  	mso-pattern:black none;}
  .xl89
  	{mso-style-parent:style0;
  	color:black;
  	font-size:12.0pt;
  	text-align:left;
  	background:white;
  	mso-pattern:black none;
  	white-space:normal;}
  .xl90
  	{mso-style-parent:style0;
  	font-size:12.0pt;
  	font-weight:700;
  	text-align:left;
  	vertical-align:middle;
  	background:#D3DDEF;
  	mso-pattern:black none;}
  .xl91
  	{mso-style-parent:style0;
  	font-size:12.0pt;
  	font-weight:700;
  	text-align:left;
  	vertical-align:middle;
  	background:#F2F2F2;
  	mso-pattern:black none;}
  .xl92
  	{mso-style-parent:style0;
  	color:#3A5D9C;
  	font-size:9.0pt;
  	font-weight:700;
  	vertical-align:middle;}
  .xl93
  	{mso-style-parent:style43;
  	font-size:10.0pt;
  	mso-number-format:"_\\(* \\#\\,\\#\\#0_\\)\\;_\\(* \\\\\\(\\#\\,\\#\\#0\\\\\\)\\;_\\(* \\0022-\\0022_\\)\\;_\\(\\@_\\)";
  	text-align:right;
  	vertical-align:middle;
  	border:.5pt solid silver;
  	mso-protection:unlocked visible;}
  .xl94
  	{mso-style-parent:style0;
  	color:white;
  	font-size:14.0pt;
  	font-weight:700;
  	text-align:right;
  	vertical-align:middle;
  	background:#3A5D9C;
  	mso-pattern:black none;
  	mso-protection:unlocked visible;}
  .xl95
  	{mso-style-parent:style0;
  	font-size:12.0pt;
  	font-weight:700;
  	mso-number-format:"_\\(* \\#\\,\\#\\#0_\\)\\;_\\(* \\\\\\(\\#\\,\\#\\#0\\\\\\)\\;_\\(* \\0022-\\0022_\\)\\;_\\(\\@_\\)";
  	text-align:right;
  	vertical-align:middle;
  	border-top:.5pt solid windowtext;
  	border-right:none;
  	border-bottom:none;
  	border-left:none;
  	background:#F2F2F2;
  	mso-pattern:black none;}
  .xl96
  	{mso-style-parent:style0;
  	font-size:10.0pt;
  	text-align:right;
  	vertical-align:middle;}
  .xl97
  	{mso-style-parent:style0;
  	color:white;
  	font-size:14.0pt;
  	font-weight:700;
  	text-align:right;
  	vertical-align:middle;
  	background:#3A5D9C;
  	mso-pattern:black none;}
  .xl98
  	{mso-style-parent:style0;
  	font-size:10.0pt;
  	mso-number-format:"_\\(* \\#\\,\\#\\#0_\\)\\;_\\(* \\\\\\(\\#\\,\\#\\#0\\\\\\)\\;_\\(* \\0022-\\0022_\\)\\;_\\(\\@_\\)";
  	text-align:right;
  	vertical-align:middle;
  	border-top:.5pt solid windowtext;
  	border-right:none;
  	border-bottom:none;
  	border-left:none;
  	background:#F2F2F2;
  	mso-pattern:black none;}
  .xl99
  	{mso-style-parent:style0;
  	font-size:12.0pt;
  	font-weight:700;
  	text-align:right;
  	vertical-align:middle;
  	background:#F2F2F2;
  	mso-pattern:black none;}
  .xl100
  	{mso-style-parent:style0;
  	font-size:12.0pt;
  	font-weight:700;
  	mso-number-format:"_\\(* \\#\\,\\#\\#0_\\)\\;_\\(* \\\\\\(\\#\\,\\#\\#0\\\\\\)\\;_\\(* \\0022-\\0022_\\)\\;_\\(\\@_\\)";
  	text-align:right;
  	vertical-align:middle;
  	border-top:.5pt solid windowtext;
  	border-right:none;
  	border-bottom:2.0pt double windowtext;
  	border-left:none;
  	background:#D3DDEF;
  	mso-pattern:black none;}
  .xl101
  	{mso-style-parent:style0;
  	font-size:16.0pt;
  	font-weight:700;
  	font-family:Arial, sans-serif;
  	mso-font-charset:204;
  	vertical-align:middle;
  	mso-protection:unlocked visible;}
  .xl102
  	{mso-style-parent:style0;
  	font-size:10.0pt;
  	text-align:right;
  	vertical-align:middle;
  	mso-protection:unlocked visible;}
  .xl103
  	{mso-style-parent:style0;
  	color:#3A5D9C;
  	font-size:20.0pt;
  	font-weight:700;
  	text-align:right;
  	vertical-align:middle;}


  ')))
  }

Again, not pretty but it gets the job done. With that we're ready to port this table over in Shiny.

The Shiny App

With the HTML and CSS functions ready, we can build out the shiny app. In order to make the code more manageable, I've seperated the table functions in another R script that is sourced in at the start.

In terms of input, the app accepts character for the ticker of the desired company and uses it to download the data. A selectizeInput dropdown is generated once the data downloads that allows the user to select one of the quarters available. With that, the data is filtered, and the filtered_data() reactive is used by our html function to build out the table. As the inputs to the table can change, we need to generate it using renderUI.

Lastly we shouln't forget to call table_css() someplace in the ui function, or our table will look very dull. With that the Shiny app is ready to generate styled quarterly income statements for virtually any stock you can think of.


  library(shiny)
  library(shinyWidgets)
  library(shinydashboard)
  library(shinydashboardPlus)
  library(dplyr)
  library(yfinance)

  source("../table_function.R")

  date_to_quarter <- function(date) {
      paste0(format(as.Date(date), "%Y"), " Q", lubridate::quarter(date))
  }

  millions <- function(x) {
      x/1000000
  }

  ui <- navbarPage("IncomeStat",


        tabPanel(title = "Income Statement",
                 useShinydashboard(),
                 useShinyalert(),
                 table_css(),

                 fluidPage(#theme = shinytheme("cosmo"),
                     tags$head(
                         tags$style(HTML("
                            body, html { background-color: #efefef; }

                         "))

                          ),
                          fluidRow(
                              box(title = "Input",
                                  width= 4,
                                  textInput("stock", "Stock Symbol", placeholder = "AAPL"),
                                  actionBttn("download", "Download Data", style = "material-flat", color = "primary", size = "sm", block = TRUE),
                                  br(),
                                  uiOutput("select_quarter")

                                  ),
                              box(title = "Income Statement",
                                  width = 8,
                                  uiOutput("income_statement"))
                          )
                            ))

  )

  server <- function(input, output) {
      data <- eventReactive(input$download, {
          getIncome(ticker = input$stock,
                    report_type = "quarterly")
      })

      output$select_quarter <- renderUI(
          selectizeInput("quarter", "Select Quarter", choices = data()$date )
      )

      filtered_data <- reactive(
          data() %>%
              dplyr::filter(date == input$quarter)
      )

      output$income_statement <- renderUI(
          table(company_name = filtered_data()$ticker,
                date = filtered_data()$date,
                quarter = date_to_quarter(filtered_data()$date),
                sales = millions(filtered_data()$totalRevenue),
                cost_goods = millions(filtered_data()$costOfRevenue),
                gross_profit = millions(filtered_data()$grossProfit),
                SGnA = millions(filtered_data()$sellingGeneralAdministrative),
                RnD = millions(filtered_data()$researchDevelopment),
                total_operating_expenses = millions(filtered_data()$totalOperatingExpenses),
                operating_income = millions(filtered_data()$operatingIncome),
                other_income_net = millions(filtered_data()$totalOtherIncomeExpenseNet) - millions(filtered_data()$interestExpense),
                interest_expense = millions(filtered_data()$interestExpense),
                EBT = millions(filtered_data()$incomeBeforeTax),
                income_tax = millions(filtered_data()$incomeTaxExpense),
                income_ops = millions(filtered_data()$netIncomeFromContinuingOps),
                net_income = millions(filtered_data()$netIncome)
                )
      )

  }




  shinyApp(ui = ui, server = server)

I hope you find this approach useful. All the code for the app can be found on github. I want to thank Tan Ho for his many useful comments. If you have ideas on improving this process, feel free to reach out on twitter or via email. Happy coding!