Creating a Consolidated Balance Sheet with Orion and Quovo. Part 1: Retrieving the Data

You just rolled out a new client portal showing all aspects of the accounts that you oversee, but what about the accounts that you don’t oversee. Account Aggregation gives you access to outside accounts held at other institutions and provides your clients a total picture of their wealth and gives you the Advisor insight into your clients held-away assets to better serve them.  In this post I am going to give you taste of what can be done with Account Aggregation by describing one method of creating a Consolidated Balance Sheet that shows managed accounts and outside accounts at other institutions. This is the first part of two, part one describes the method to create a consolidated balance sheet object, and part two describes how you can use the balance sheet object for visualization to give your clients an easy to grasp format to view their wealth.

As the above diagram shows, we are going to take managed accounts and manual balance sheet items from the Orion Advisor Platform and outside accounts from Quovo and integrate into a single Orion’s balance sheet object. We utilized three main Orion balance sheet api endpoints to retrieve and update the balance sheet data within Orion.  For more details on Orion’s API go here.

GET v1/Portfolio/Clients/{clientId}/BalanceSheet

Retrieves the balance sheet object.  If no data is stored in Orion, an empty balance sheet object will be sent
        {
               "id": /{clientId}/,
               "assets": {
                 "categories": [],
                 "value": 0
               },
               "liabilities": {
                 "categories": [],
                 "value": 0
               }
        }

GET v1/Portfolio/Clients/{clientId}/BalanceSheet/Category

Retrieves a list of all categories currently available for client.
        [
         "Cash",
         "Loans",
         "Property"
        ]

PUT v1/Portfolio/Clients/{key}/BalanceSheet

Saves the balance sheet object to Orion.  In our case we did not save the outside account data to Orion; it was only stored in memory for the duration of the client’s session but we did provide a way for the client to enter manual items, such as property. After retrieving the balance sheet object, we need to add all the managed accounts and values.  There are several api endpoints to retrieve account values. For more details, go here (http://forum.riadevelopers.com/post/orion-webapi-getting-started-guide-7019125).  Once you have retrieved the accounts and values, load into the balance sheet object.  To add a balance sheet item, you need to add an entry to items collection under the appropriate category.  You also need to update the category total as well as the Asset total.  Below in the balance sheet object under assets, there are two accounts in the category “Managed Accounts”.
             {
               "id": {clientId},
               "assets": {
                  "categories": [
                   {
                     "items": [
                               {
                                "id": 1,
                                "name": "Account1",
                                "value": 100000
                              },
                              {
                                "id": 2,
                                "name": "Account2",
                                "value": 100000
                              }
                            ],
                   "value": 200000,
                   "id": 1,
                   "clientId": {clientId},
                   "name": "Managed Accounts",
                 },
                ],
                "value": 200000
             },
             "liabilities": {
                "categories": [],
                "value": 0
             }
           }

We now have all manually added balance sheet items and all managed accounts loaded into the balance sheet object.  It is now time to add the outside accounts by utilizing Quovo’s API.  For more details on the Quovo API go here. In this post we are discussing how to retrieve outside account data using Quovo’s API, which will not discuss authentication and account set-up. I refer you to the link above for more information on those topics. To retrieve the outside account data to populate the balance sheet object, we need to make calls to Quovo’s account’s and portfolio’s endpoints

GET https://api.quovo.com/v2/users/{user_id}/accounts

Quovo defines an account as a login to a brokerage or bank.  Returns all accounts for a user.
        {
          "accounts": [
             {
              "brokerage": 123,
              "brokerage_name": "Test Bank Institution",
              "config_instructions": null,
              "failures": 0,
              "id": 456,
              "is_inactive": false,
              "last_good_sync": "2017-10-24T15:11:12.093",
              "nickname": null,
              "opened": "2017-07-24T15:10:43.880",
              "status": "good",
              "update_count": 1,
              "updated": "2017-10-24T15:11:12.093",
              "user": 789,
              "username": "quovo_user",
              "value": 2467.23
            }
          ]
        }
            

The above response shows the user has an account “Test Bank Institution” with a balance of $2,467.23.

GET https://api.quovo.com/v2/users/{user_id}/portfolios

Portfolios are accounts linked to brokerage or bank login.  Returns all portfolios for a user, across all accounts.
             {
              "portfolios": [
               {
                "account": 111111,
                "brokerage": 123,
                "brokerage_name": "Test Bank Institution",
                "description": null,
                "id": 456,
                "is_inactive": false,
                "is_taxable": true,
                "last_change": "2017-10-24T15:11:12.100",
                "nickname": “Vacation Fund”,
                "owner_type": "Individual Account",
                "portfolio_category": "Banking",
                "portfolio_name": "Savings Account",
                "portfolio_type": "Savings",
                "portfolio_type_confidence": "High",
                "update_count": 0,
                "user": 789,
                "username": "quovo_user",
                "value": 2467.23
               }
             ]
           }

This response contains the portfolios or accounts at Test Bank Institution. There is one savings account with a balance of $2,467.23 with a nickname of “Vacation Fund”.

Now that we have the outside account data, we need to populate the balance sheet object with the data.  We first must determine if the account is an asset or liability, either by the account type or value direction.  Next, we need to map the Quovo data to the balance sheet object.
  • If there is a nickname present in quovo portfolio, we map to the name field for example “Vacation Fund”. If there is not a nickname present, we take the account brokerage_name, for example “Test Bank Institution (Savings) Savings Account”.
  • We use the portfolio_category “Banking” for the balance sheet category.
  • The Quovo portfolio field value $2,467.23 will be used for the balance sheet item value.
We now have a consolidated balance sheet object containing all managed accounts and accounts from outside institution including the held-away assets.
             {
              "id": {clientId},
              "assets": {
                "categories": [
                 {
                 "items": [
                    {
                      "id": 1,
                      "name": "Account1",
                      "value": 100000
                    },
                   {
                     "id": 2,
                     "name": "Account2",
                     "value": 100000
                   }
                 ],
                 "value": 200000,
                 "id": 1,
                 "clientId": {clientId},
                 "name": "Managed Accounts",
                },
                {
                "items": [
                  {
                    "id": 1,
                    "name": "Vacation Fund",
                    "value": 2467.23
                  },
                ],
                "value": 2467.23,
                "id": 1,
                "clientId": {clientId},
                "name": "Banking",
               },
              ],
                  "value": 202467.23
              },
              "liabilities": {
                 "categories": [],
                  "value": 0
               }
                }
 

This data is now ready for the user interface where it can be visualized through charts and tables. 

I will take that topic up in part two.  This just scratches the surface of what is available from Quovo besides accounts and portfolios. You also can retrieve positions and transactions.  The balance sheet is just one more method you can use to help clients see their complete wealth picture.  For advisors, having an automated, aggregated and integrated view of their clients’ entire financial situation leads to providing better service and insight into assets that they are not currently managing.  This is one way to integrate managed accounts with outside accounts on the Orion Platform. I hope you found it helpful.

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