Portfolio set up tips: Bulk upload opening balances

Last time we told you how to setup a portfolio manually using our opening balance feature. For portfolios with less than 20 holdings, this is an easy process, which takes about 10 minutes.

Now, for the second entry in our "scrap your bold New Year's resolution and do something you can actually commit to" series we're staying with opening balances, but we're covering how to upload an unlimited number of holdings at once using our Bulk Import: Opening Balance feature (seriously, we once helped a client do this with 12,000 holdings).

This process is especially useful for Sharesight's professional plan partners. Some of the accountants and financial advisers we work with are onboarding new clients who inherently have opening balances. Others are moving data from legacy or offline software into Sharesight, and more still are moving data from spreadsheets into Sharesight. Spreadsheets are by far the most common "system" used by professionals. Sharesight is here to end your reliance on offline, error-prone, manually updated procedures forever. It's 2014 after all, time to go cloud!

If you're a Sharesight Investor or Expert Plan user with more than 20 holdings or you'd prefer to organise your opening balance data in spreadsheet format first (or already maintain it this way), this process will be helpful for you too.

To get started, as we explained last time, you'll need to pick your "Big Bang" date and wave goodbye to transactional history data prior to this date. Your portfolio will NOT BE inaccurate using this method, it's just that by forgoing historical trade data, you can't go back before the opening balance date to examine your portfolio's performance in say, 2004, for example.

Chances are you’ll have access to an accurate opening balance date as at the portfolio’s last tax lodgement or some other official statement period. We advise clients to pick the oldest possible date at which your portfolio data is reliable. This way you’ll have access to some history too. From a compliance perspective, though, choosing an opening balance date immediately following the previous tax year (e.g. 1 July 2013) is the safest way to go.

1. In order to calculate an accurate opening balance, all we need is opening balance date, code, cost basis, and quantity held. This means Sharesight needs to have at least these data points available on the spreadsheet you're about to import.

2. In terms of the file itself, we require CSV format. Most software will export files in CSV format, but if your file is in .xls, .xlt, or .xlsx format (standard Excel formats), you can easily switch to CSV by "Saving As" and then changing the file format.

3. Or download a pre-formatted template from Sharesight here

4. You'll notice the file we provide contains extra data columns. These aren't mandatory, but if you have the data feel free to include it in the appropriate columns.

5. On the Add New Holdings page, scroll down to the bottom and click the "+Bulk Import" button.

6. The next page will prompt you to locate and upload the file. Be sure to choose "opening balances" from the "You are importing" dropdown. Note the template file is available for download here too.

7. Sharesight will now begin its "automagic" upload process and first ask you to confirm the column headers (data columns) in your file. If you have data in the wrong column, you can make edits here.

8. Next, Sharesight will ask you to confirm the actual data in your file, and alert you if any errors have been found. You can make edits to your data directly on this page without having to change your file. Notice in this screenshot, I left out the number of QAN shares. Sharesight still accepts the file so you can fill in the blanks here.

And that's it! Once you click Save, your portfolio will be setup from that opening balance date forward.

A few handy notes on the logic behind this process

This upload will add data to anything you already have input for this portfolio, and will look for any discrepancies and alert you if there are issues. As an example, if you recorded a few transactions for QAN prior to the newly introduced opening balance, Sharesight will inform you of the discrepancy and allow you to make edits to fix the error.

You can include different opening balance dates for each holding in the same file. Opening balance dates correspond to holdings, not the portfolio as a whole.

If you want to layer on or backfill buy and sell transactions you can do so at any time between your portfolio’s opening balance date and today. You can do this by forwarding trade confirmation emails to Sharesight or by manually entering buy or sell transactions. More on both of these processes later in this series.

Here’s a link to our help page on setting up your holdings for more info.

Next up: Portfolio Setup Tip #3: Creating a Portfolio Using Historical Trades (Manual)