Skip to main content
Skip table of contents

Portfolio Export

This Documentation will go over the portfolio export process and how to add custom fields to the portfolio. The following will be required:

What is the portfolio export process?

The portfolio export process allows you to export your portfolio along with some additional custom fields such as balance sheet info into a “portfolio.xlsx” file which will then be converted into a “portfolio_current.csv” file daily. The “portfolio_current.csv” file can then be used to import your portfolio data into other systems such as Salesforce.

The portfolio export process allows you to download a “portfolio.xlsx” file in which you will be able to add some custom fields. Daily, the “portfolio.xlsx” file will be converted into a “portfolio-current.csv” file and a “portfolio-YYYY-MM-DD.csv” file.

What is the benefit of the export process?

There are many benefits to using the portfolio export process. One is to share portfolio data with an external system such as a CRM like salesforce that allows the import of a CSV file into a custom object. Another use for the export may be to import the portfolio data into the company's database.

An overview of the export process

The process begins with a portfolio.xlsx file which can be downloaded using from WebDAV using Cyberduck from the “private/dashboard“ folder. The “portfolio.xlsx” will contain fields such as “Account Id”, and “date”. You can also add custom fields like “Total Recurring Revenue for Period” that can be obtained from the balance sheet using Excel formulas. Once the custom fields are specified the file can be uploaded to the WebDAV “private/dashboard” folder. Now a job will run every day to convert the file into a “portfolio-current.csv” which will be accessible in the “private/dashboard/portfolio-data” folder of the company WebDAV and a file transfer host.

Where can I see the output of the process?

The output of the process can be seen in WebDAV using Cyberduck in the “private/dashboard/portfolio-data” folder. There you will find the “portfolio-current.csv” file along with a “portfolio-current.xlsx” file. In addition, there will be 2 other files that specify the date they were created “portfolio-YYYY-MM-DD.csv” and “portfolio-YYYY-MM-DD.xlsx”.

When does the data get exported?

The export process will run daily and will produce a file called “portfolio-current.csv”. The “portfolio-current.csv” file will be added to the “portfolio-data” folder contained in the “private/dashboard” folder of the company WebDAV.

How do I add or remove fields from the exported data?

Step 1: Log in to WebDAV

To download the portfolio template file (portfolio.xlsx), Cyberduck will connect with the Boss Insights account. Open Cyberduck and navigate to the bookmarks tab. Then right click and in the open area and select new bookmark.

A window will pop up prompting you to enter credentials. From the dropdown at the very top of the window select “WebDAV (HTTPS)” then where it says “server” enter the company URL and port 443. Then enter the username and password for the account and specify the path as “dock/files.php”. See the image below for an example.

If you do not have credentials to access exported data please contact our support help desk

Step 2: Download the file

Navigate to the browser tab. Expand the “private” then “dashboard“ folder, right-click on portfolio.xlsx and select “download” to copy to your computer. Make a note of which folder you save this file to as we will need to open the document and re-upload it later. Alternatively, you can select “Download To…” to specify where on your computer you would like to download the file.

Step 2: Make changes to portfolio.xlsx

Now that the file is on your computer, columns can be added to your liking. Add a new column and the name at the end of the first row. An Excel formula will need to be added in the second row to reference the value in the correct sheet. For example “Last Reconciled” is located in the “META” sheet in the accounting.xlsx so you can simply use “=META_LAST_RECONCILED” in your portfolio.xlsx file as the formula to get the value.

Get Data Based on Reconcile Date

If the reconciliation date is greater than one month then the month must be specified in the excel formula as “=TOTAL_RECURRING_REVENUE_FOR_PERIOD_M2”. “M2” means 2 months and “M3” would be 3 and so on. To make this more dynamic the following Excel formula can be used so it does not have to manually entered each time:

CODE
=INDIRECT("{{referenced_field}}"&IF(DATEDIF(META_LAST_RECONCILED, TODAY(), "m") > 0, "_M"&DATEDIF(META_LAST_RECONCILED,TODAY(), "m"),""))

Step 3: Upload the portfolio.xlsx file back to the server

Open up cyber duck again and upload the file from your computer to the server by clicking the upload button or clicking and dragging ensuring that it is being uploaded to the “private/dashboard” folder.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.