Re: [GNC] Trial Balance Issue with Fund Merger
Simple enough in my case. Thanks. Lisa R On 9/1/2021 3:54 PM, David Carlson wrote: If the price in the price database is wrong for a particular day it can be manually corrected in the price database tool If there are not too many errors to deal with. I run into that when manually entering transactions On Wed, Sep 1, 2021 at 4:50 PM Lisa Rowell <mailto:lisa.gives...@gmail.com>> wrote: On 9/1/2021 12:28 PM, John Ralls wrote: > >> On Sep 1, 2021, at 12:16 PM, John Ralls mailto:jra...@ceridwen.us>> wrote: >> >> >> >>> On Sep 1, 2021, at 11:15 AM, Lisa Rowell mailto:lisa.gives...@gmail.com>> wrote: >>> >>> >>> On 9/1/2021 5:27 AM, Lisa Rowell wrote: >>>> On 8/31/2021 9:41 PM, John Ralls wrote: >>>>>> On Aug 31, 2021, at 8:32 PM, Lisa Rowell mailto:lisa.gives...@gmail.com>> wrote: >>>>>> >>>>>> I'm working my way through my account history after a massive GnuCash import, straightening out issues with missing realized capital gains/losses and came across an event that I can't figure out how to properly handle. >>>>>> >>>>>> I held shares of a fund called Spartan 500 Index Investor Class (FSMKX) which merged with Spartan US Equity Index Investor Class (FUSEX) at some odd rate around 1:0.513. When I did the import, I ended up with an account for FSMKX and an account for FUSEX and a manually entered exchange transaction which did a sell of FSMKX shares and a buy of FUSEX shares with no share price. This got everything balanced out as far as share counts go, but now I'm finding it's showing up as being not correct in the Trial Balance. It looks to me like the exchange is being interpreted as if an actual sell event had taken place. >>>>>> >>>>>> Can GnuCash properly account for this? The case in the manual's More Complex Merger example is a bit different because the example stock continued to trade under the same symbol, so that solution doesn't map well. I found a past mailing list thread that said that the proper way to account for this is as a sell transaction of the going away fund and a buy transaction of the fund that lives on with an accompanying Realized Gain transaction. This doesn't seem right to me though since I didn't sell the shares and did not realize a gain and I don't even have prices for the time of the merger. In my way of looking at it, the gain calculation should come at the time of sale and be based on purchase price of the various share amounts, and not at the time of the merger, since that maps to the tax view of things where I live. >>>>>> >>>>>> I understand that GnuCash wouldn't be able to calculate the realized gains post merger, and I'm ok with doing that in a side spreadsheet, but am more looking for a way around the bogus realized gain entry at the time of merger just to make the Trial Balance happy. >>>>> If you're not too compulsive and since this is presumably ancient history in a personal book one simple way to deal with it would be to pretend that you bought the FUSEX in the first place and ignore the FSMKX, but that might be a little painful if you have a bunch of reinvested FSMKX dividend transactions that you'd also need to change. >>>>> >>>>> I've handled similar situations in the past by doing a simple transfer transaction between the two accounts, as in CR FSMKX 513 and DR FUSEX 1000. As long as there's no currency component to the transaction it shouldn't create a trading imbalance in the book currency. >>>>> >>>>> Regards, >>>>> John Ralls >>>> That solution was what worked for me for share transfers between brokerages, where the commodity was the same, but when I changed commodities it somehow shows up as a mismatch in the Trial Balance. I don't have a share price for the shares in either split so, in theory it shouldn't involve the book currency at all. I don't get it at all. >>>> >>>> I'm sure it's this transaction since the balances match on the previous day and the only other transaction on this date is a paycheck deposit in the book currency that's no where near the amount of the imbalance. The transaction is in the image attached, that's what you're advocating, right? >>>> >>>> Thanks. >>>> >>>> Lisa R. >>>> >>> Additional information: >>> >>> Removing
Re: [GNC] Trial Balance Issue with Fund Merger
On 9/1/2021 12:28 PM, John Ralls wrote: On Sep 1, 2021, at 12:16 PM, John Ralls wrote: On Sep 1, 2021, at 11:15 AM, Lisa Rowell wrote: On 9/1/2021 5:27 AM, Lisa Rowell wrote: On 8/31/2021 9:41 PM, John Ralls wrote: On Aug 31, 2021, at 8:32 PM, Lisa Rowell wrote: I'm working my way through my account history after a massive GnuCash import, straightening out issues with missing realized capital gains/losses and came across an event that I can't figure out how to properly handle. I held shares of a fund called Spartan 500 Index Investor Class (FSMKX) which merged with Spartan US Equity Index Investor Class (FUSEX) at some odd rate around 1:0.513. When I did the import, I ended up with an account for FSMKX and an account for FUSEX and a manually entered exchange transaction which did a sell of FSMKX shares and a buy of FUSEX shares with no share price. This got everything balanced out as far as share counts go, but now I'm finding it's showing up as being not correct in the Trial Balance. It looks to me like the exchange is being interpreted as if an actual sell event had taken place. Can GnuCash properly account for this? The case in the manual's More Complex Merger example is a bit different because the example stock continued to trade under the same symbol, so that solution doesn't map well. I found a past mailing list thread that said that the proper way to account for this is as a sell transaction of the going away fund and a buy transaction of the fund that lives on with an accompanying Realized Gain transaction. This doesn't seem right to me though since I didn't sell the shares and did not realize a gain and I don't even have prices for the time of the merger. In my way of looking at it, the gain calculation should come at the time of sale and be based on purchase price of the various share amounts, and not at the time of the merger, since that maps to the tax view of things where I live. I understand that GnuCash wouldn't be able to calculate the realized gains post merger, and I'm ok with doing that in a side spreadsheet, but am more looking for a way around the bogus realized gain entry at the time of merger just to make the Trial Balance happy. If you're not too compulsive and since this is presumably ancient history in a personal book one simple way to deal with it would be to pretend that you bought the FUSEX in the first place and ignore the FSMKX, but that might be a little painful if you have a bunch of reinvested FSMKX dividend transactions that you'd also need to change. I've handled similar situations in the past by doing a simple transfer transaction between the two accounts, as in CR FSMKX 513 and DR FUSEX 1000. As long as there's no currency component to the transaction it shouldn't create a trading imbalance in the book currency. Regards, John Ralls That solution was what worked for me for share transfers between brokerages, where the commodity was the same, but when I changed commodities it somehow shows up as a mismatch in the Trial Balance. I don't have a share price for the shares in either split so, in theory it shouldn't involve the book currency at all. I don't get it at all. I'm sure it's this transaction since the balances match on the previous day and the only other transaction on this date is a paycheck deposit in the book currency that's no where near the amount of the imbalance. The transaction is in the image attached, that's what you're advocating, right? Thanks. Lisa R. Additional information: Removing the cross commodity sell/buy transaction does remove the Trial Balance discrepancy. Additionally I added up all of the purchase prices of shares of the fund being merged away and that amount does equal to the credit / debit difference in the report. It is, but that doesn't quite work. Here's a sample that does: If you don't price the share transfer to the currency (USD in both examples) the trial balance logic assumes 0 and calculates a loss. To avoid that enter the book value of the shares you're transferring as a sell (credit) amount in the source split and a buy (debit) amount in the destination split: Unfortunately my screen shot didn't make it to the list. It contained the following transactions: 1/23/19 Buy PIODX 1000 Brokerage Account1000 25. 25,000.00 1,000 2/28/19 Reinv PIODX 11.231 Brokerage Account 11.231 22.2598 250.00 1,011.231 9/1/21Transfer to P -1011.231 1,011.231 0 P1971.211 12.8094 25,250.00 PIODX -1011.231 24.9698 25,250.00 The last transaction is in split view showing the identical cash amounts for the two stock accounts. Enter the amounts and let GnuCash compute the prices to prevent
Re: [GNC] Trial Balance Issue with Fund Merger
On 9/1/2021 5:27 AM, Lisa Rowell wrote: On 8/31/2021 9:41 PM, John Ralls wrote: On Aug 31, 2021, at 8:32 PM, Lisa Rowell wrote: I'm working my way through my account history after a massive GnuCash import, straightening out issues with missing realized capital gains/losses and came across an event that I can't figure out how to properly handle. I held shares of a fund called Spartan 500 Index Investor Class (FSMKX) which merged with Spartan US Equity Index Investor Class (FUSEX) at some odd rate around 1:0.513. When I did the import, I ended up with an account for FSMKX and an account for FUSEX and a manually entered exchange transaction which did a sell of FSMKX shares and a buy of FUSEX shares with no share price. This got everything balanced out as far as share counts go, but now I'm finding it's showing up as being not correct in the Trial Balance. It looks to me like the exchange is being interpreted as if an actual sell event had taken place. Can GnuCash properly account for this? The case in the manual's More Complex Merger example is a bit different because the example stock continued to trade under the same symbol, so that solution doesn't map well. I found a past mailing list thread that said that the proper way to account for this is as a sell transaction of the going away fund and a buy transaction of the fund that lives on with an accompanying Realized Gain transaction. This doesn't seem right to me though since I didn't sell the shares and did not realize a gain and I don't even have prices for the time of the merger. In my way of looking at it, the gain calculation should come at the time of sale and be based on purchase price of the various share amounts, and not at the time of the merger, since that maps to the tax view of things where I live. I understand that GnuCash wouldn't be able to calculate the realized gains post merger, and I'm ok with doing that in a side spreadsheet, but am more looking for a way around the bogus realized gain entry at the time of merger just to make the Trial Balance happy. If you're not too compulsive and since this is presumably ancient history in a personal book one simple way to deal with it would be to pretend that you bought the FUSEX in the first place and ignore the FSMKX, but that might be a little painful if you have a bunch of reinvested FSMKX dividend transactions that you'd also need to change. I've handled similar situations in the past by doing a simple transfer transaction between the two accounts, as in CR FSMKX 513 and DR FUSEX 1000. As long as there's no currency component to the transaction it shouldn't create a trading imbalance in the book currency. Regards, John Ralls That solution was what worked for me for share transfers between brokerages, where the commodity was the same, but when I changed commodities it somehow shows up as a mismatch in the Trial Balance. I don't have a share price for the shares in either split so, in theory it shouldn't involve the book currency at all. I don't get it at all. I'm sure it's this transaction since the balances match on the previous day and the only other transaction on this date is a paycheck deposit in the book currency that's no where near the amount of the imbalance. The transaction is in the image attached, that's what you're advocating, right? Thanks. Lisa R. Additional information: Removing the cross commodity sell/buy transaction does remove the Trial Balance discrepancy. Additionally I added up all of the purchase prices of shares of the fund being merged away and that amount does equal to the credit / debit difference in the report. Lisa Rowell ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information. - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: [GNC] Trial Balance Issue with Fund Merger
On 8/31/2021 9:41 PM, John Ralls wrote: On Aug 31, 2021, at 8:32 PM, Lisa Rowell wrote: I'm working my way through my account history after a massive GnuCash import, straightening out issues with missing realized capital gains/losses and came across an event that I can't figure out how to properly handle. I held shares of a fund called Spartan 500 Index Investor Class (FSMKX) which merged with Spartan US Equity Index Investor Class (FUSEX) at some odd rate around 1:0.513. When I did the import, I ended up with an account for FSMKX and an account for FUSEX and a manually entered exchange transaction which did a sell of FSMKX shares and a buy of FUSEX shares with no share price. This got everything balanced out as far as share counts go, but now I'm finding it's showing up as being not correct in the Trial Balance. It looks to me like the exchange is being interpreted as if an actual sell event had taken place. Can GnuCash properly account for this? The case in the manual's More Complex Merger example is a bit different because the example stock continued to trade under the same symbol, so that solution doesn't map well. I found a past mailing list thread that said that the proper way to account for this is as a sell transaction of the going away fund and a buy transaction of the fund that lives on with an accompanying Realized Gain transaction. This doesn't seem right to me though since I didn't sell the shares and did not realize a gain and I don't even have prices for the time of the merger. In my way of looking at it, the gain calculation should come at the time of sale and be based on purchase price of the various share amounts, and not at the time of the merger, since that maps to the tax view of things where I live. I understand that GnuCash wouldn't be able to calculate the realized gains post merger, and I'm ok with doing that in a side spreadsheet, but am more looking for a way around the bogus realized gain entry at the time of merger just to make the Trial Balance happy. If you're not too compulsive and since this is presumably ancient history in a personal book one simple way to deal with it would be to pretend that you bought the FUSEX in the first place and ignore the FSMKX, but that might be a little painful if you have a bunch of reinvested FSMKX dividend transactions that you'd also need to change. I've handled similar situations in the past by doing a simple transfer transaction between the two accounts, as in CR FSMKX 513 and DR FUSEX 1000. As long as there's no currency component to the transaction it shouldn't create a trading imbalance in the book currency. Regards, John Ralls That solution was what worked for me for share transfers between brokerages, where the commodity was the same, but when I changed commodities it somehow shows up as a mismatch in the Trial Balance. I don't have a share price for the shares in either split so, in theory it shouldn't involve the book currency at all. I don't get it at all. I'm sure it's this transaction since the balances match on the previous day and the only other transaction on this date is a paycheck deposit in the book currency that's no where near the amount of the imbalance. The transaction is in the image attached, that's what you're advocating, right? Thanks. Lisa R. ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information. - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
[GNC] Trial Balance Issue with Fund Merger
I'm working my way through my account history after a massive GnuCash import, straightening out issues with missing realized capital gains/losses and came across an event that I can't figure out how to properly handle. I held shares of a fund called Spartan 500 Index Investor Class (FSMKX) which merged with Spartan US Equity Index Investor Class (FUSEX) at some odd rate around 1:0.513. When I did the import, I ended up with an account for FSMKX and an account for FUSEX and a manually entered exchange transaction which did a sell of FSMKX shares and a buy of FUSEX shares with no share price. This got everything balanced out as far as share counts go, but now I'm finding it's showing up as being not correct in the Trial Balance. It looks to me like the exchange is being interpreted as if an actual sell event had taken place. Can GnuCash properly account for this? The case in the manual's More Complex Merger example is a bit different because the example stock continued to trade under the same symbol, so that solution doesn't map well. I found a past mailing list thread that said that the proper way to account for this is as a sell transaction of the going away fund and a buy transaction of the fund that lives on with an accompanying Realized Gain transaction. This doesn't seem right to me though since I didn't sell the shares and did not realize a gain and I don't even have prices for the time of the merger. In my way of looking at it, the gain calculation should come at the time of sale and be based on purchase price of the various share amounts, and not at the time of the merger, since that maps to the tax view of things where I live. I understand that GnuCash wouldn't be able to calculate the realized gains post merger, and I'm ok with doing that in a side spreadsheet, but am more looking for a way around the bogus realized gain entry at the time of merger just to make the Trial Balance happy. Thanks. Lisa R. ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information. - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: [GNC] CVS import success/issues
Thanks for the congratulations, it was probably the longest run once program I've ever written and there was a LOT of time spent cleaning things up. Thanks for the confirmation that Capital Gains method works, that was the section I was referring to. I played with it more and realized what was going on. I had left the Trial Balance report up all during my experimentation, and only played with the General tab, not noticing the Accounts tab. When you first bring up a report it defaults to all accounts, but if leave it up and add a new account, for example a Capital Gains:X account, you have to go to the accounts tab and explicitly add it to the set, otherwise it's not included in the next refresh and it looks like the added gain/loss isn't recognized. Sigh. I should have figured that out much sooner than I did. I also played with the lots mechanism and yeah it's definitely a handy tool, thanks for the pointer. I couldn't get the UI to let me select multiple splits to add at once, but it still seems to be the way to go. It should work for 99% of my sell transactions, and the remainder are ones involving transfers without sales, which I can handle on my own. I've had very few of those anyway (maybe 2 or 3). I tried the Scrub mechanism, but it seemed to alter transactions in my ledger and I'd rather not go that route. Thanks. Lisa On 8/17/2021 12:55 AM, Geoff wrote: Hi Lisa Congratulations on pulling off such a massive data migration! To try and answer your question about the Trial Balance, yes I have seen unresolved capital gains / losses causing this report to not balance. When you mentioned "9.7.1.2" did you mean "9.7.1.2. Separate the Capital Gain/Loss Transaction from the Sale Transaction." in the "GnuCash Tutorial and Concepts Guide"? https://www.gnucash.org/viewdoc.phtml?rev=4=C=guide If so, yes, I record the capital gains / loss transactions separately from the sell transactions and have not encountered any problems. I recommend the use of the Lots functionality - hard to grasp at first, but once it clicks you are cooking with gas ;--) My Trial Balance is currently in balance, but it took quite some time and effort (running it at different "As At" dates) to track down the offending transactions. Good luck! Regards Geoff = On 16/08/2021 9:42 am, Lisa Rowell wrote: In preparation for a move from Mac to Linux, I went to move from my circa 2015 version of Quicken for Mac to GnuCash. Sadly that version lacks QIF support and couldn't even be imported correctly into the latest PC Quicken. It does, however, have the option to view all transactions in one list and export the current list to a CSV file with Quicken-isms describing the transactions. Long story story: I was able to write a program to translate 99% of my 24 years of transactions into a format digestible by GnuCash's CSV import. The remainder, mostly Split, Add and Remove shares, were best dealt with by hand anyway. Along the way I ran into a few issues that might not have been reported. There's a question at the end. 1) Large files will blow up the program a crash that gives no clue of the issue on MacOS. On a PC it gives a popup with a cryptic message that I think amounts to a too many threads error. I got around this by outputting to per year files for most years and smaller for a few select ones. The crash would normally happen at the point of selecting the import configuration. 2) Multiple back to back imports will also crash the program. I got around this by exiting and restarting between imports. 3) The Num field, which I translated, for better or worse, from Quicken's similar field, had an issue where entries which did not have a Num entry would be given one. My limited experimentation with this seemed to indicate that when a split with no Num followed an entry on the same date that had a Num, it would inherit the previous entries Num. 4) The match algorithm is a bit too greedy in pairing splits. If you have a sequence of splits that describe two transactions that have the same date and description, they will be merged into a single transaction. This often came up with transactions like end of the month dividends or interest payments. If the algorithm encountered two splits that balance out in amounts, followed by two more that also balanced themselves, would not it be better to make them two separate transactions? This can be gotten around by artificially altering the transaction's description or by tedious manual editing. 5) When pulling in a lot of transactions at once, it seems that random entries get deselected in the match list. I wasn't able to predict the behavior though. 6) I wasn't able to get the Cleared field to work. If it was anything but "n" the split would be ignored. I'm not sure what the intention was on this, but it might be worth documenting. And now for the questi
[GNC] CVS import success/issues
In preparation for a move from Mac to Linux, I went to move from my circa 2015 version of Quicken for Mac to GnuCash. Sadly that version lacks QIF support and couldn't even be imported correctly into the latest PC Quicken. It does, however, have the option to view all transactions in one list and export the current list to a CSV file with Quicken-isms describing the transactions. Long story story: I was able to write a program to translate 99% of my 24 years of transactions into a format digestible by GnuCash's CSV import. The remainder, mostly Split, Add and Remove shares, were best dealt with by hand anyway. Along the way I ran into a few issues that might not have been reported. There's a question at the end. 1) Large files will blow up the program a crash that gives no clue of the issue on MacOS. On a PC it gives a popup with a cryptic message that I think amounts to a too many threads error. I got around this by outputting to per year files for most years and smaller for a few select ones. The crash would normally happen at the point of selecting the import configuration. 2) Multiple back to back imports will also crash the program. I got around this by exiting and restarting between imports. 3) The Num field, which I translated, for better or worse, from Quicken's similar field, had an issue where entries which did not have a Num entry would be given one. My limited experimentation with this seemed to indicate that when a split with no Num followed an entry on the same date that had a Num, it would inherit the previous entries Num. 4) The match algorithm is a bit too greedy in pairing splits. If you have a sequence of splits that describe two transactions that have the same date and description, they will be merged into a single transaction. This often came up with transactions like end of the month dividends or interest payments. If the algorithm encountered two splits that balance out in amounts, followed by two more that also balanced themselves, would not it be better to make them two separate transactions? This can be gotten around by artificially altering the transaction's description or by tedious manual editing. 5) When pulling in a lot of transactions at once, it seems that random entries get deselected in the match list. I wasn't able to predict the behavior though. 6) I wasn't able to get the Cleared field to work. If it was anything but "n" the split would be ignored. I'm not sure what the intention was on this, but it might be worth documenting. And now for the question: I'm using version 4.5 and was not able to get the Trail Balance Report to show equal totals after a security Sell transaction, despite making Capital Gain/Loss entries. Is the technique described in 9.7.1.2 still supported? I tried the example in 9.7.2.1 and it gave me with an imbalance, but did it in my existing file. If it's supported I'll make a clean file and experiment with it more. Thanks. Lisa ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information. - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.