Help with Alcoa name change, reverse split, spin out of company with same name/symbol. 😲

GollyJer

I've managed to get into a "circular dependency" issue with my Trans data model.

I think I've narrowed it down to the way I'm trying to handle Alcoa.
Here's what happened.
- Bought shares of Alcoa Inc (AA).
- Alcoa Inc (AA) became Arconic Inc (ARNC).
- Arconic Inc (ARNC) spun off a new company called Alcoa Corporation but with the symbol of the old Alcoa Inc (AA).
- Shareholders of the original Alcoa Inc received one share of the new Alcoa Corporation for every three shares of the original.

So... a name & symbol change then spin out of a new company with name & symbol of the original company at a 1 for 3 "reverse split" but more like a dividend?

Very confused on how to approach this in the transactions tab of Portfolio Slicer.

Thanks for any help! 🎉

Jeremy

VidasMatelis

Hi Jeremy,

All these split, renames etc are handled with transaction types SymbolTransferIn and SymbolTransferOut and for SymbolTransferIn you must specify CostBasisOverride. You must calculate yourself CostBasisOverride

Example For name change (1share AA -> 1share ARNC):
Date: 2018-01-01 TransTYpe: Buy SymbolName: AA Qty: 100 Price 10
Date 2018-02-01 TransType: SymbolTransferOut SymbolName: AA Qty:100 Price 11 (price at the day of transfer)
Date: 2018-02-01 TransType: SymbolTransferIn SymbolName: ARNC Qty:100 Price:11 CostBasisOverride: 1000

Example for spin out (1 share ARNC -> 1 share ARNC + 0.3 shares of AA)
You have to figure out how to divide 1000 cost basis between new ARNC and AA and also how to split price during symbol transfer in, but sum of qty*price after transfer for both symbols should be the same as qty*price of ARNC before transfer. Same for cost basis - sum before and after transfer should be same.

Date: 2018-03-01 TransType:SymbolTransferOut SymbolName: ARNC Qty: 100 Price: 12
Date 2018-03-01 TransType: SymbolTransferIn SymbolName: ARNC Qty: 100 Price: 10 CostBasisOverride: 800
Date 2018-03-01 TransType: SymbolTransferIn SymbolName: AA Qty 33 Price: 6.06 CostBasisCoverride: 200

TO check (before = after):
Price*Qty: 100*12 = 100*10 + 33*6.06
CostBasis: 1000 = 800 + 200

This is just a guidance, try and see what works for you.
The fact that symbol came back to AA should not be an issue if your quotes for old symbol and new symbol are correct.

GollyJer

Hi Vidas. Thanks for the help!

I was able to nail down all the Alcoa transactions and when I look at the remaining shares in column U (QtyHeld) they are all correct for AA and ARNC.

But I'm still getting a circular dependency error.


I've spent about 6 hours trying to figure this out. I found exactly which section of my transactions to delete (row 480 and down) for the error to disappear but I truly can't figure out WHY. Row 479 is one of the last Alcoa transferIn so that's a clue...

Any more insight would be greatly appreciated. Can I send you the file to take a look? At this point I'm willing to pay for some help. This is such a stupid small thing I just want it fixed and I've never been able to not do it myself! 😈

Thank you! Sincerely.

VidasMatelis

Hi,

Can you send me your workbook? My email is posted on this page: http://www.portfolioslicer.com/contact.html

GollyJer

Sent. Thanks!

VidasMatelis

First, please take a look at your transactions that have negative Qty. For "Split" type transactions this is expected, but for Sell and SymbolTransferOut transactions Qty should be positive.
But that is not the issue you are facing.
It appears that your issue is related to the fact that for DivTA transactions you leave Qty field as empty.
Now this should not be a problem and I never encountered this, but it appears that if you update your all transactions and put value in Qty field 1 where it is blank now, you will be able to refresh.
Could you please try this and see if that helps you.
I cannot explain this behaviour, to me it appears as one of Excel "quirks".

GollyJer

Just wanted to circle back and let you know this worked. Thanks!