Creating a Consolidated Balance Sheet with Orion and Quovo. Part 2: Visualizing the Data

In part 1, I showed how to integrate managed account data from Orion with outside accounts from Quovo into Orion’s balance sheet object. In part 2, I am going to show how you can visualize the consolidated balance sheet object through charts and tables.

We are going to create the above view utilizing Angular 2, Orion, Quovo, and FusionCharts. This post assumes you have Angular 2 installed, you can get more information here. You can download FusionCharts and the Angular 2 FusionCharts plugin here. Once the plugin is downloaded, follow the quick start to install and Import angular2-fusioncharts in the root NgModule.

We are now ready to create the component that will present the above balance sheet and charts. Components are the building blocks of any Angular 2 application. They are blocks of reusable code consisting of a view and some logic. By building a balance sheet component, it will give you the capability to drop the balance anywhere in your html simply by placing the following tags:

        
                    <balance-sheet></balance-sheet>
                    
                    

We first define the component. The basic structure of the component is shown below, we now need to fill in the details of the component.

        
                                                import { Component, Input, OnInit, OnChanges } from 'angular/core';
                                                Component({
                                                    selector: 'balance-sheet',
                                                    templateUrl: 'balance-sheet.component.html'
                                                })
                                                export class BalanceSheetComponent  {
                                                    dataSource: any;
                                                    errorMessage: string;
                                                    chartDef: any;
                                                    assetChart: any;
                                                    liabilitiesChart: any;  
                                                    data: any ;
                                                    colors: any;
                                                    lastColorUsed: number;
 
                                                    constructor() {
                                                        this.getData();
                                                    }
                                                    getData(): void {}
                                                    getNextColor() {}
                                                    createChartData(): void {}
                                                }
                    
                    

In the above component we first import needed modules and define the components properties selector and templateURL. The selector is a css selector that identifies this component in a template and the templateUrl is a url to an external file containing a template for the view. The Class method is where we add behavior and event bindings for the template. Inside the class, we first define needed variables. We now define the constructor. Once activated, we execute the getData() method that will retrieve the balance sheet from our backend. We are using ASP.NET WEB API as our backend, which contains the logic that we described in part 1.

        
                       getBalanceSheet(): void {
                        this.data = null;
                        var that = this;
                        this.service.getBalanceSheet()
                            .subscribe(Data => {
                                that.balanceSheet = data;
                                that.createChart();
                            },
                            error => {
                                that.errorMessage = error;
                            });
                      }
                    
                    

The above method will retrieve the balance sheet json object asynchronously through a promise. Once retrieved it will call the method createChart() to format the data for the charts. The balance sheet object we will be working with is shown below. It contains the managed and outside accounts for the Flintstones family.

        
                                                {
  "id": 123,
  "networth": "143900",
  "description": "The Flintstones",
     "assets": {
            "categories": [
                {
                    "items": [
                        {"id": 1,"name": "Fred and Wilma Flintstone","value": 100000},
                        {"id": 2,"name": "Pebbles Flintstone","value": 20000}
                    ],
                    "value": 120000,"id": 1,"clientId": 1,"name": "Managed Accounts",
                },             
                {
                    "items": [
                        {"id": 1,"name": "Bank of Bedrock - Checking Account", "value": 14000 },
                        {"id": 2,"name": "Bank of Bedrock- Savings Account","value": 30000}
                    ],
                    "value": 44000,"id": 2, "clientId": 1, "name": "Banking",
               },
               {
                    "items": [
                        {"id": 1,"name": "Primary Residence in Bedrock","value": 68000}
                    ],
                    "value": 68000,"id": 3,"clientId": 1,"name": "Property"
               },
               {
                    "items": [
                        {"id": 1,"name": "Slate Rock and Gravel Company - Pension","value": 42000},
                        {"id": 2,"name": "IRA - ROTH","value": 12000
                    }
                    ],
                    "value": 54000,"id": 4,"clientId": 1,"name": "Retirement Account"
                }
         ],
         "value": 286000
     },
     "liabilities": {
         "categories": [
          {
            "items": [
                {"id": 1,"name": "Fred's Credit Card","value": 100},
                {"id": 2,"name": "Wilma's Credit Card","value": 10000}
            ],
            "value": 10100,"id": 1,"clientId": 1,"name": "Short-Term Credit"
          },
          {
            "items": [
                {"id": 1,"name": "Pebbles Student Loan","value": 32000}
              ],
             "value": 32000,"id": 2,"clientId": 1,"name": "Loans"
          },
          {
              "items": [
                  {"id": 1,"name": "Primary Residence in Bedrock","value": 100000} 
              ],
              "value": 100000,"id": 2,"clientId": 1,"name": "Mortgages",
          },
	       ],
          "value": 142100
        }
  };
                     
                    
                    

Now that we retrieved the balance sheet object, we can create the data objects that FusionCharts utilizes to render two bar charts. Below is the definition for two charts: one for the assets and the second for the liabilities.

                                
                        {
                          "chart": this.chartDef,
                          "categories": [{"category": [{"label": "ASSETS", "showlabel": "0"}]}],
                          "dataset": null
                        }
                        {
                          "chart": this.chartDef,
                          "categories": [{"category": [{"label": "LIABILITIES", "showlabel": "0"}]}],
                          "dataset": null
                        }
                    
                    

The chart property contains the chart definition utilized by FusionCharts. Categories contain properties to describe the dataset, and the dataset contains the data.

                                
                               {
                                "showLegend": "0",
                                "showBorder": "0",
                                "showShadow": "0",
                                "showAxisLines": "0",
                                "showAlternateHGridColor": "0",
                                "alternatevgridcolor": "ffffff",
                                "showCanvasBorder": "0",
                                "usePlotGradientColor": "0",
                                "canvasbgcolor": "ffffff",
                                "bgcolor": "ffffff",
                                "xaxisname": "",
                                "yaxisname": "",
                                "caption": "",
                                "subcaption": "",
                                "animation": "0",
                                "numberprefix": "",
                                "showvalues": "0",
                                "showborder": "0",
                                "chartLeftMargin": "0",
                                "chartTopMargin": "0",
                                "chartRightMargin": "0",
                                "chartBottomMargin": "0",
                                "chartLeftPadding": "0",
                                "chartTopPadding": "0",
                                "chartRightPadding": "0",
                                "chartBottomPadding": "0",
                                "canvasPadding": "0",
                                "showYAxis": "0",
                                "showYAxisValues": "0",
                                "chartPadding": "0",
                                "showLimits": "0",
                                "numDivLines": "0",
                                "stack100percent": "1",
                                }
                    
                    

Now that we have the structure of the chart object, we need to build the dataset property from the balance sheet data. Below is the method createChartData() that will create the chart object from the balance sheet data.


colors: any = ['#2f7ed8', '#0d233a', '#8bbc21', '#910000', '#1aadce', '#492970', '#f28f43', '#77a1e5', '#c42525', '#a6c96a'];
lastColorUsed: number = -1;
getNextColor() {
    this.lastColorUsed = this.lastColorUsed === 9 ? 0 : this.lastColorUsed + 1;
    return this.colors[this.lastColorUsed];
}
createChartData(): void {
    var assetDataset: any = [];
    var liabilitiesDataset: any = [];
    var that = this;
    for (let assetCategory of this.data.assets.categories) {
      assetCategory.color = that.getNextColor();
      assetDataset.push({"seriesname": assetCategory.name, "color": assetCategory.color,"data":[{"value": assetCategory.value}]});
    }
    for (let liabilitiesCategory of this.data.liabilities.categories) {
      liabilitiesCategory.color = that.getNextColor();
      liabilitiesDataset.push({ "seriesname":liabilitiesCategory.name,"color":liabilitiesCategory.color, "data": [{ "value": liabilitiesCategory.value }]});
    }
    this.assetChart.dataset = assetDataset;
    this.liabilitiesChart.dataset = liabilitiesDataset;    
}
                                       
                    

The method above loops through the asset and liabilities categories in the balance sheet object and builds the dataset for each chart object, assetChart, and liabilitiesChart. It also sets the color both in the chart objects and balance sheet object.

We have created three objects that will be used by our html to render the Flintstones balance sheet and charts.

  • data: the balance sheet data used by the table
  • assetChart: the chart definition for the asset chart
  • liabilitiesChart: the chart definition for the liabilities chart

 Now that we have the necessary data objects, it is time to turn our attention to the html. To define a chart in the html, the following tag is used:


                           <fusioncharts 
                            width="100%" 
                            height="75" 
                            type="stackedbar2d" 
                            dataFormat="json" 
                            dataSource]=assetChart> 
                            </fusioncharts>

The statement above defines a stacked bar chart, the data is contained in the assetChart object, and the data is in json format. The rest of the html utilizes bootstrap and is standard as shown below:


<div id="balanceSheetView">
<div style="margin:20px;">
<div style="border: 5px solid #337ab7;border-radius:10px">
<table class="table table-condensed table-borderless" style="padding: 0; margin: 0;">
<tbody>
<tr class="bg-primary" style="padding:0;margin:0">
<td class="bold text-center" colspan="2">
<h2>
{{data.description}}&nbsp;&nbsp;&nbsp;&nbsp;Net Worth:&nbsp;&nbsp;{{data.networth currency:'USD':true:'1.2-2'}}
</h2>
</td>
</tr>
<tr style="padding:0;margin:0">
<td style="padding:0;margin:0" class="bg-success" width="50%">
<h3 class="text-center bold">
ASSETS&nbsp;&nbsp;&nbsp;&nbsp;{{data.assets.value | currency:'USD':true:'1.2-2'}}
</h3>
</td>
<td style="padding:0;margin:0" class="bg-danger" width="50%">
<h3 class="text-center bold">
LIABILITIES&nbsp;&nbsp;&nbsp;&nbsp;{{data.liabilities.value | currency:'USD':true:'1.2-2'}}
</h3>
</td>
</tr>
<tr style="padding:0;margin:0">
<td class="bg-success" width="50%" style="padding:0;margin:0">
<div *ngIf="assetChart && assetChart.dataset">
<fusioncharts width="100%" height="75"
type="stackedbar2d"
dataFormat="json"
[dataSource]=assetChart>
</fusioncharts>
</div>
</td>
<td class="bg-danger" width="50%" style="padding:0;margin:0">
<div *ngIf="liabilitiesChart && liabilitiesChart.dataset">
<fusioncharts width="100%" height="75"
type="stackedbar2d"
dataFormat="json"
[dataSource]=liabilitiesChart>
</fusioncharts>
</div>
</td>
</tr>
<tr>
<td class="bg-success">
<table class="table table-condensed bg-success table-borderless" style="padding: 0; margin: 0;">
<tr *ngFor="let category of data.assets.categories">
<td class="text-left bg-success" style="padding: 0; margin: 0;">
<div style="margin:0 20px">
<h4 class="bold pull-left"><i [style.color]="category.color" class="fa fa-square fa-lg"></i>&nbsp;</h4>
<h4 class="bold pull-left">{{category.name}}</h4>
<h4 class="bold pull-right">{{category.value | currency:'USD':true:'1.2-2'}}</h4>
<div class="clearfix"></div>
<div *ngFor="let item of category.items" style="padding:0; margin:0;">
<h4 class="pull-left">&nbsp;&nbsp;&nbsp;&nbsp;{{item.name}}</h4>
<h4 class="pull-right">{{item.value | currency:'USD':true:'1.2-2'}}</h4>
<div class="clearfix"></div>
</div>
<div class="clearfix"></div>
</div>
</td>
</tr>
</table>
</td>
<td class="bg-danger">
<table class="table table-condensed bg-danger table-borderless" style="padding:0; margin:0;">
<tr *ngFor="let category of data.liabilities.categories">
<td class="text-left bg-danger" style="padding: 0; margin: 0;">
<div style="margin:0 20px">
<h4 class="bold pull-left"><i [style.color]="category.color" class="fa fa-square fa-lg"></i>&nbsp;</h4>
<h4 class="bold pull-left">{{category.name}}</h4>
<h4 class="bold pull-right">{{category.value | currency:'USD':true:'1.2-2'}}</h4>
<div class="clearfix"></div>
<div *ngFor="let item of category.items" style="padding:0; margin:0;">
<h4 class="pull-left">&nbsp;&nbsp;&nbsp;&nbsp;{{item.name}}</h4>
<h4 class="pull-right">{{item.value | currency:'USD':true:'1.2-2'}}</h4>
<div class="clearfix"></div>
</div>
</div>
</td>
</tr>
</table>
</td>
</tr>
</tbody>
</table>
</div>    
                    

The above HTML code will generate the view shown above.

As you can see we have a table that contains the line items for assets and liabilities along with an embedded stacked bar chart for each. This post shows you how to take the consolidated balance sheet object containing outside account data from Quovo and managed accounts from Orion created in Part 1, and visualize the data through a chart to have the biggest impact. I hope you found the series of posts useful.

Let's discuss your project .... Give us a call or drop us a line