Hi Guys, Trying to integrate IB and XERO.
Having an issue where I have connected xero, but only the buy trades are being registered and not the sell trades.
Additionally, what do I reconcile to? do I reconcile to an export of the IB transaction history?
Thanks!
Hi @langinvest and welcome to the community! It would be best if you haven’t already to reach out to our support team directly to look into further for you 
Thanks I have yesterday, waiting on a response now. Whats the best way to contact?
Hi I’ve been trialling the Xero sharesight integration for a while but haven’t connected up the accounts with xero and sharesight in the connection settings. As mentioned before I’ve been using Quicken for 20 years but realise at some stage it might not work.
My impression of the integration, compared to using quicken is that you have to have a lot of workarounds using spreadsheets. That’s because if you have multi brokerage accounts, multi FX accounts, dividends going to different accounts it all gets quite complex.
Not to mention xero cashbook takes a bit of getting used to and it’s not helped by the email only support for questions, that sometimes takes 2-3 days to get a response. As a former trained accountant I can see quite a potential for a mess to develop if xero is not set up properly intially with regards to chart of accounts etc.
It would be helpful if sharesight did some in depth training videos on the integration and how it handles multi fx, multi brokerage, dividends coming from many sources etc. I will continue to trial it alongside my quicken which works well, at the moment.
In saying that I was interested to find a personal finance software that looks to be similar, if not exactly the same as quicken called jiosoft.co.uk. I contacted the company and they replied very promptly in 24 hours to let me know they have several NZ users and it works with importing bank downloads, thus negating the need to have bank integrated downloads. Let me know how you get on with xero/sharesight. I’m keen to make it work for me down the track but so far there have been a lot of workarounds, requiring a spreadsheet, compared to quicken and the support is not quick enough to resolve problems, being email based only.
Hi again @ecco I have 5 Xero organisations (for what it’s worth - one Starter and four Non-GST cashbooks). Each of four of the organisations are connected to one Sharesight portfolio. The fifth xero organisation is connected to five Sharesight portfolios (ie 5 to 1).
I have no off-system workarounds - all transactions are directly posted from SS to Xero. I do have a couple of foreign currency holdings in SS which are all translated to $A by SS and posted to Xero in $A. So the forex works for me.
I actually used to have my children’s portfolio in a spreadsheet that I manually posted to xero and it go too messy so I put them all onto SS.
Set up was pretty simple. I needed to set up the following accounts in Xero.
Revenue:
Unfranked Income
Franking Credits (Revenue)
Interest Income*
Realised Gains / Losses
[*Note I have 2 interest income account in Xero. ONe for int income from SS and one for other interest - e.g. on cash accounts that are not set up in Sharesight]
Assets:
Franking Credits (Asset)
TFN Withholding Tax
Non Resident Withholding Tax
Dividend Reinvestment Plan (DRP) Balance
Share Market Investments
For Xero cashbook organisation you also have to map the bank accounts
Select Bank Account Mapping:
-Select Bank Account for Trades
-Select Bank Account for Payouts (Dividends)
For My Xero Starter organisation (ie non-cash book I think) SS posts cash transactions as invoices so you can match them to payments in any account
There’s a good SS help page to walk you through it.
The only extra steps are:
Hope this assists
cheers
Many thanks @mwse for posting up your setup. That’s very helpful. I may have to spend time looking into this. (have plenty of time as I’m based in Auckland and we are in an intense lockdown, hardly being able to leave the house…).
Just wanting to clarify a couple of things about your set up -
so from what you are saying your forex is not a problem for the fx cash accounts because sharesight is sending through the AUD amounts to your xero cashbook. Do you only have the xero basic cashbook that is offered via sharesight plans or do you have one that caters for fx?
I did read up the original set up instructions on the sharesight help area but hadn’t actually linked everything up to xero. I wasn’t keen on paying that high monthly fee for xero with fx functionality.
I did get xero up and running but it needs fine tuning. I didn’t realise you could set up several xero organisations and link them to your various portfolios.
Also I’ve got brokerage accounts outside of NZ that pay reinvested dividends and some holdings with cash dividends in other currencies. Do you know how that would work with the sharesight/xero integration if you only have the basic xero plan via sharesight? Someone did mention to me once that maybe all your dividends, both local and fx, could be synched to one clearing account in xero, but then would have to be cleared out regularly by way of converting all the foreign dividends, which is kind of a clunky solution and would be time consuming.
I get what you mean with revaluing the portfolios monthly via journal. That was the easiest thing I figured out!!
It’s a pity that this forum doesn’t allow private messaging as there’s a lot of detail that I’m missing. I was having enough trouble just getting one of my credit cards with kiwibank to download to xero🤪.
Anyway sounds like it’s running well for you.
I just was wondering whether you were able to do the sharing of sharesight accounts with one paid account and the rest the free plans and also whether you were able to do with xero/sharesight integration without having to buy the xero plan with fx, which I find expensive.
It would be great if sharesight could put some work into this and tutorials to get their integration with xero working well, including for people with multiple foreign brokerage accounts.
There’s a real lack of personal finance packages available out there that have the fx, investment and good custom reporting capabilities. I’ve looked at everything out there and still continue to use quicken until I can get off that to something works ok and gives me the reports I need. What would be great, I think is if xero could have a function where your net worth was on the dashboard and being constantly updated as you add transactions. Maybe that is possible and I’m just not aware of how to do this. It seems to be geared up to small business and bank account and debtor reporting.
Anyway some stuff for me to work on. Thanks again for sharing your setups. cheers
Hi @mwse. Thank you this is helpful.
I am close to having it all worked out now.
One question re gain/loss of the investment.
How do you account for this?
My thought was doing a manual journal to match the current market value at the end of every month.
I was hoping sharesight would work this out when I sold a investment but sadly it looks like it doesnt.
Hello
Taking each in turn
No - I don’t use the Xero account offered via sharesight. I have a bookkeeper who maintains my xero subscriptions (she does a great job - mainly does the monthly rec and GST returns). I have a ‘non-GST cashbook’ and a ‘starter’ xero subscription.
For what it’s worth Xero only allows SS to post bank transactions to Xero (that must be posted to a specific ac). The ‘starter’ and above (I think) Xero subscriptions allow SS to post transactions as invoices to Xero. This means you can allocate transactions to specific accounts - which might help with forex bank accounts
Blockquote
Also I’ve got brokerage accounts outside of NZ that pay reinvested dividends and some holdings with cash dividends in other currencies. Do you know how that would work with the sharesight/xero integration if you only have the basic xero plan via sharesight?
My foreign currency dividend payments are all paid into my local ($A) bank ac so are converted into local currency. SS picks all that up. You just need to ensure the ‘bank amount’ in the dividend screen is correct (sometimes there’s a diff in FX rate, or WHT tax rate) and the local currency amount is posted correctly from SS to Xero.
Blockquote
Someone did mention to me once that maybe all your dividends, both local and fx, could be synched to one clearing account in xero, but then would have to be cleared out regularly by way of converting all the foreign dividends, which is kind of a clunky solution and would be time consuming.
As above I have all my divs paid to the same bank ac - regardless of currency and SS posts the correct amounts. No need to have a clearing ac. Essentially all the detail is in SS and Xero just shows the transactions.
Blockquote
I just was wondering whether you were able to do the sharing of sharesight accounts with one paid account and the rest the free plans and also whether you were able to do with xero/sharesight integration without having to buy the xero plan with fx, which I find expensive.
I definitely don’t see a need for forex Xero ac.
My main frustration is that my online broker NABTrade doesn’t provide a decent bank feed for the internal NABTRade bank ac. This mean I (or my bookeeper) have to do a manual upload of the bank transactions each month. Trades are picked up easily by SS via the confirmation email.
Bottom line is I find the Xero and SS integration works very well.
Hi @langinvest
SS does work all that out and posts the gain/loss at a trade level to Xero. No need to do it manually.
Some points that helped me:
I run all my cost bases in Xero as equal to tax cost base so it all lines up for tax (esp for SMSF)
A trick to watch out for is to be careful when changing ‘sale allocation method’ in the SS CGT report. If you are linked to Xero you need to ensure the Xero transaction is unreconciled (esp for cashbook xero accounts). Changing the allocation method will change the gain/loss amount posted to Xero - and it will only work if the transaction in Xero is not reconciled.
Finally - just reiterating that I post a reversing monthly manual journal in Xero to a separate Cr ‘unrealised gain/loss’ revenue account, Dr Market value (asset account).
So my balance sheet shows
PLUS
Equals Investments at Market - which I’ve set up as Group in a custom report in Xero: Which agrees to the Market value on the Historical cost report!
This part is only required if you want Xero to show the market value.
The SS/Xero set up (via the API I believe) has been thought through really well. There are just some tricks to watch out for!
Good luck!
Thanks mwse for taking the time to explain how your set up works and what accounts you have with sharesight and xero. I think the key for you is that all your dividends are paid into an AUD account which simplifies things greatly from an accounting point of view.
Very helpful to know how others are making it work. I think I’m set in my ways with quicken which does still work very well but you never know how long that will last as it’s an old program.
Ahh interesting!
So mine isnt showing any profit or loss. Its just putting the sell amount.
When I contacted support they said it doesn’t do that. they said it only has the sold amount, not a split out profit and loss amount.
Can you confirm yours does? Its a bit of an issue for me. Finding it very buggy at the moment.
Appreciate the time taken to reply.
Thanks!
SS definitely posts the sale against the cost base and splits out the gain/loss in Xero. All done automatically
Here is a trade from SS
Here is the linked Xero transaction
Suggest you check the account mapping in the Xero integration section on the SS side.
Good luck! Worth persisting with SS
@mwse hah… Mine is not showing this. Im going to contact support again.
Thank you for posting these screenshots! Thats very helpful.
The accounts are mapped out, so I think its a bug on their end.
Sorry to hear that. Suggest you to the following and click through each part of the process from SS to Xero (ensure you are logged into Xero to the same organisation as you are posting from in SS). You should be able to see the parts of the trade being posted from SS to Xero. It all happens pretty much instantly.
Either way - The outcomes of thjs will be helpful for the helpdesk if it’s an error at their end
cheers
So the buys are working as intended, but the sells arent.
I will see what sharesight says via support.
Andre.
All solved! looks like a few of the setup accounts were set as BAS included which caused the issues!
