Sorry for the delay getting back to you. I was trying to isolate what was causing the issues. My findings were:
Having only received 7 months of feeds and manually processing the other 5 months for 2020, I have found that it is best if the feed comes in as it will auto allocate the buys & sells for you and about 95% of these selected the correct parcel.
I also requested an excel copy of the cash transactions (including managed portfolio) report. I added a column and a formula to put a running balance in.
NOTE: This report will have irrelevant transactions where the cash has been transferred from the internal platform to the main Hub24 cash account. These can be identified by the reference (“ Managed Portfolio Capital Withdrawal ” & “ Deposit From Managed Portfolio ”)
I then compared the General Ledger for the Hub24 cash account in BGL to the excel spreadsheet and isolated the discrepancies. (I didn’t check every single transaction, just checked end of month and where needed checked smaller sections). The discrepancies mostly consisted of:
- Buys & Sells within the internal platforms where one side (buy / sell) has been auto allocated but the cash side has gone to suspense. It doesn’t auto allocate the cash transactions within the platforms that sit inside the Hub24 account.
- You need to allocate the suspense of these transactions to the Hub24 cash account
- Missing transactions (mostly in the first 3 months of the year).
- These need to be bought in manually through smart matching (I had about 15 transactions).
Once I had reconciled the cash accounts, the remaining unmatched transactions mostly consisted of duplicate transactions from the income.
The income transactions are bought in at the record date and allocated to the income account and balanced to the 92000 investment income clearing account. This includes dividends that may be paid after June 30 and these need to be deleted in the transaction list window.
Most of the income transactions are bought in again on the payment date. I think this allocated to the 92000 account also but in my file they were allocated it to income (I think I may have allcoated them from smart matching though) so I had to delete the record date transaction. This could be better processed by:
- Isolating the record date transactions that belong to the next financial year and deleting them. This should in theory balance the 92000 account to nil or at least make it easier to identify the discrepancies; OR
- Select both the cash & record date transactions in the transaction listing screen and match them together to clear the 92000 account (this is my preference as it is cleaner, and I can make sure nothing is missed).
There are no reports that show the cash transactions for each platform separately. The only report I could get showing the cash transactions within the platforms was the one the one mentioned above and this shows every transaction for all cash accounts in one report.
For ease I record the Hub24 cash account in BGL and include all cash transactions here. This then matches the cash summary report you get at end of year.
I’ve passed this feedback onto BGL. Hopefully they will improve the way the import comes through and get rid of the record date transactions which will reduce the qty of unmatched transactions significantly.
I will also be processing the transactions monthly or quarterly (once BGL fixes the feed so that start importing again) so this will hopefully make it a bit quicker to notice errors as well.
Hope this is helpful.