[GNC] Recording stock holdings
I have been using GnuCash for about 6 years, starting with version 2.6 and now use 3.5 on a Windows 10 laptop. I like it very much, but have a persistent problem with entering stock investments. The day-to day financial transactions cause me no problems at all, but I've never been able to fully understand how stocks and shares are recorded and managed, and I'm frustrated that, despite following the tutorials very carefully, it never works for me as it does in the tutorial, so I hope someone can explain what's going on. I have a relatively small portfolio of shares that I've tried to enter, and the following example should illustrate the problems I've faced: Having set up the various accounts according to sections 9.4.1 and 9.4.2 of the tutorial, I then followed the instructions in section 9.5.1 to record 33 National Grid shares (NG.L) that were valued at 7.833030 GBP on 5/4/2019 as follows (note that I have used || to separate the columns): Transaction date: 5/4/2019Split 1: Brokerage Account:Stock:NG.L || 33.00 || 7.833030 || 258.49 ||Split 2: Equity:Opening Balances || || || || 258.49 However, when I press Enter, GnuCash seeks to make corrections and adds three more splits, leaving me with: Split 1: Brokerage Account:Stock:NG.L || 33.00 || 0.903030303 || 258.49 || Split 2: Trading:CURRENCY:GBP || || || 258.49 ||Split 3: Imbalance - GBP || || || 15,588,385,182,335.90 ||Split 4: Equity:Opening Balances || || || || 258.49Split 5: Trading:LSE:NG.L || || || || 15,588,385,182,594.39 Subtracting the large number in column 5 of split 5 from the large number in column 4 of split 3 equals 258.49, so why is it making any of these changes? AlanG ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: [GNC] Example of importing Stock transactions from CSV
Jon, The exported CSV security purchase transaction did have more than two split lines because it had a commission and fees, so to simplify things I deleted the commission and fee lines. If there had been a dividend or if it was a sale rather than a purchase there would have been other distractions as well. For the export I started from the security account register and exported one transaction by selecting a single date to export. I opened the exported CSV file in LibreOffice which, after simplifying, was a two line by many column file with a title line to act as a header naming all the fields which I then saved as a text CSV file. That way the critical columns were already blessed with header names very similar to the names that were needed during the import. Here is a list of every column that was exported and where I selected a field to import to, the name of that field. Export Column CSV Import field Date> Date Transaction ID > None Number> None Description > Description Notes > Notes Commodity/Currency > Transaction Commodity Void Reason > None Action > None Memo > Memo Full Account Name> None Account Name > None Amount with Sym > Deposit Amount Num. > None Reconcile > None Reconcile Date > None Rate /Price > Price There are several places where alternate choices would work. One, I think, would have been Account Name, which might have eliminated the need to assign a transfer account to the transaction in the last step before accepting the import. As it was, I needed to assign the brokerage account name as the transfer account in that step. In the Import Preview step I needed to assign the security account name in the Account name box on the upper right, select the date format because Americans use M-D-Year format, and 1 leading line to skip the header line. I saved these settings under a name that would make it easy to preselect the same account names next time. Finally, because the transaction that I had exported was actually still there, in the next step I needed to click the A(dd) box and select the brokerage account as the transfer account. This test worked nicely, but I did not try extending the test to multiple transactions. On Sun, Aug 21, 2022 at 3:19 PM David Carlson wrote: > Jon, > > I just tried exporting one transaction from a security account and > re-importing it with the CSV importer. It worked like a charm! I don't > have time right now to detail what I did here, but I did not need to select > the Multi split box after all. Have fun > > On Sun, Aug 21, 2022 at 12:16 PM David T. wrote: > >> The thread at >> https://lists.gnucash.org/pipermail/gnucash-user/2019-March/083442.html >> goes into quite a discussion about how to import multi line csv files. >> >> >> On August 21, 2022 6:26:24 PM GMT+03:00, Jon Schewe >> wrote: >>> >>> David, >>> >>> I'm using GnuCash 4.11 from FlatPak. I have all of my transactions on >>> single lines (I've posted samples in this thread). >>> I would greatly appreciate someone explaining how to make this work and >>> I'd be happy to help update the documentation on this. >>> >>> Jon >>> >>> >>> On Sun, 2022-08-21 at 09:17 -0500, David Carlson wrote: >>> Jon, If you are not using some recent 4.x version of GnuCash you may not have the latest improvements to the CSV transaction importer. A normal stock purchase or sale transaction has two parts, one, a deposit or withdrawal of cash from a brokerage account, the other an exchange of the cash for a security at some exchange rate, aka price. For this discussion I will ignore commissions and fees. Thus you want to have two CSV lines per transaction. Also, when importing the CSV file, you will need to check the Multi-split box to tell GnuCash that is the type of file that you are importing. Then you can assign one line to the security purchase or sale and the other to the cash transfer. You can define fields in each line to match the respective lines in a completed transaction as seen in the security register. You only see the price and share details in the security register and the cash transfer details can be seen in both the security register and the brokerage register. I will admit that I have not done that myself, so I cannot provide an example for you to follow. Perhaps another user can provide an example. Good luck. On Sun, Aug 21, 2022 at 8:38 AM Jon Schewe wrote: > On Sat, 2022-08-20 at 12:20 -0400, Michael or Penny Novack wrote: > >> On 8/19/2022 10:33 PM, Jon Schewe wrote: >> >>> So I didn't get the CSV import tool to work. >>> >> >> Perhaps a reminder on what CSV is and is not. >> >>>
Re: [GNC] USAA OFX Direct connect stopped working
> On Aug 21, 2022, at 7:54 PM, Daffy Duck wrote: > > Hello, > > A while back, USAA changed their OFX stuff and there had to be a > workaround with a PIN, etc. > > I did that, without a problem, but I just noticed today it has broken > again? > > This is the output: > > AqBanking v6.5.2.0stable > Sending jobs to the bank(s) > Sorting commands by account > Sorting account queues by provider > Send commands to providers > Send commands to provider "aqofxconnect" > Locking customer "1299" > Sending request... > Connecting to server... > Resolving hostname "df3cx-services.1fsapi.com" ... > IP address is "45.60.151.211" > Connecting to "df3cx-services.1fsapi.com" > Connected to "df3cx-services.1fsapi.com" > Using GnuTLS default ciphers. > TLS: SSL-Ciphers negotiated: TLS1.3:ECDHE-RSA-AES-128-GCM:AEAD > Connected. > Sending message... > Message sent. > Waiting for response... > Receiving response... > HTTP-Status: 200 () > Response received. > Disconnecting from server... > Disconnected. > Parsing response... > Status for signon request: Success (Code 0, severity "INFO") > The server successfully processed the request. > Status for transaction statement request: General error (Code 2000, > severity "ERROR") > Error other than those specified by the remaining error codes. (Note: > Servers should provide a more specific error whenever possible. Error > code 2000 should be reserved for cases in which a more specific code is > not available.) > Unlocking customer "1299" > -- > > any ideas? First, try again tomorrow on the off chance USAA had a server outage over the weekend. If it still fails, turn on OFX logging (see https://wiki.gnucash.org/wiki/AqBanking#Debugging) and capture the actual OFX response. Edit it to remove your actual account number and credentials then post it here. Regards, John Ralls ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
[GNC] USAA OFX Direct connect stopped working
Hello, A while back, USAA changed their OFX stuff and there had to be a workaround with a PIN, etc. I did that, without a problem, but I just noticed today it has broken again? This is the output: AqBanking v6.5.2.0stable Sending jobs to the bank(s) Sorting commands by account Sorting account queues by provider Send commands to providers Send commands to provider "aqofxconnect" Locking customer "1299" Sending request... Connecting to server... Resolving hostname "df3cx-services.1fsapi.com" ... IP address is "45.60.151.211" Connecting to "df3cx-services.1fsapi.com" Connected to "df3cx-services.1fsapi.com" Using GnuTLS default ciphers. TLS: SSL-Ciphers negotiated: TLS1.3:ECDHE-RSA-AES-128-GCM:AEAD Connected. Sending message... Message sent. Waiting for response... Receiving response... HTTP-Status: 200 () Response received. Disconnecting from server... Disconnected. Parsing response... Status for signon request: Success (Code 0, severity "INFO") The server successfully processed the request. Status for transaction statement request: General error (Code 2000, severity "ERROR") Error other than those specified by the remaining error codes. (Note: Servers should provide a more specific error whenever possible. Error code 2000 should be reserved for cases in which a more specific code is not available.) Unlocking customer "1299" -- any ideas? ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: [GNC] Example of importing Stock transactions from CSV
Jon, I just tried exporting one transaction from a security account and re-importing it with the CSV importer. It worked like a charm! I don't have time right now to detail what I did here, but I did not need to select the Multi split box after all. Have fun On Sun, Aug 21, 2022 at 12:16 PM David T. wrote: > The thread at > https://lists.gnucash.org/pipermail/gnucash-user/2019-March/083442.html > goes into quite a discussion about how to import multi line csv files. > > > On August 21, 2022 6:26:24 PM GMT+03:00, Jon Schewe > wrote: >> >> David, >> >> I'm using GnuCash 4.11 from FlatPak. I have all of my transactions on >> single lines (I've posted samples in this thread). >> I would greatly appreciate someone explaining how to make this work and >> I'd be happy to help update the documentation on this. >> >> Jon >> >> >> On Sun, 2022-08-21 at 09:17 -0500, David Carlson wrote: >> >>> Jon, >>> >>> If you are not using some recent 4.x version of GnuCash you may not >>> have the latest improvements to the CSV transaction importer. >>> >>> A normal stock purchase or sale transaction has two parts, one, a >>> deposit or withdrawal of cash from a brokerage account, the other an >>> exchange of the cash for a security at some exchange rate, aka >>> price. For this discussion I will ignore commissions and fees. >>> >>> Thus you want to have two CSV lines per transaction. Also, when >>> importing the CSV file, you will need to check the Multi-split box to >>> tell GnuCash that is the type of file that you are importing. Then >>> you can assign one line to the security purchase or sale and the >>> other to the cash transfer. You can define fields in each line to >>> match the respective lines in a completed transaction as seen in the >>> security register. You only see the price and share details in the >>> security register and the cash transfer details can be seen in both >>> the security register and the brokerage register. >>> >>> I will admit that I have not done that myself, so I cannot provide an >>> example for you to follow. Perhaps another user can provide an >>> example. Good luck. >>> >>> On Sun, Aug 21, 2022 at 8:38 AM Jon Schewe wrote: >>> On Sat, 2022-08-20 at 12:20 -0400, Michael or Penny Novack wrote: > On 8/19/2022 10:33 PM, Jon Schewe wrote: > >> So I didn't get the CSV import tool to work. >> > > Perhaps a reminder on what CSV is and is not. > > CSV stands for "common separated variables. That means the data > is in > the form of a record consisting of a number of fields delineated > by > commas. Which means two important things. The fields (individual > data > elements) cannot contain commas but even more important, ORDER > MATTERS. > > In other words, the record A,B,C,D is not the same a B,A,C,D << > A, B, > C, > and D being elements of data, names, amounts, etc. >> > > The CSV data you are importing must not only be in CSV format but > the > data must be in the right order AND any data that is null (not > present > still has its space in the record. > > Thus if a record was supposed to include A, C, and D (B is not > relevant > to this record) it would look like A,,C,D and not A,C,D > > > But even the "wrong" examples are in CSV format. > > > Yes, I know what a CSV file is and how it should be formatted. The problem is not with the format of the file, that is handled by GnuCash just fine. The problem is the mapping of columns in the CSV file to fields in GnuCash. I cannot figure out the correct mapping to get the correct transactions. I have tried all of the following possible mappings between GnuCash fields and columns in my CSV file. In the end I finally wrote the python script out of frustration of the import CSV transactions not working. I'm hoping that someone on this list understands enough python and the import CSV transactions functionality enough to tell me how to get the mapping correct. # test 2 Transfer Account - FUND Withdrawal - FUND UNITS Account - Transfer Account Transaction Commodity - Currency Shares are correct. Close, need to get the dollars right now # test 3 Transfer Account - FUND Withdrawal - FUND UNITS Account - Transfer Account Transaction Commodity - Currency Deposit - AMOUNT None of the shares or cash lined up. # test 4 Try swapping withdrawal and deposit Transfer Account - FUND Deposit - FUND UNITS Account - Transfer Account Transaction Commodity - Currency Withdrawal - AMOUNT None of the shares or cash lined up. # test 5 Transfer Account - FUND Withdrawal - FUND UNITS Account -
Re: [GNC] gnucash-user Digest, Vol 233, Issue 68
GnuCash will do the accounting fine. It won't do the equipment, RMA, and inventory tracking. I suspect it'll be very hard to find a software which integrates these other functions with accounting - because of that, if it were me, I'd use GnuCash for the accounting, and look for other solutions for the other functions. Chris __ From: [1]gnucash-user-requ...@gnucash.org Sent: Saturday, August 20, 2022, 11:54 AM To: [2]gnucash-user@gnucash.org Subject: gnucash-user Digest, Vol 233, Issue 68 Message: 7 Date: Sat, 20 Aug 2022 10:32:03 -0400 From: Dave Mueller AA3EE [3] To: [4]gnucash-user@gnucash.org Subject: [GNC] Is GnuCash right for me? Message-ID: [5]<3d2f789355cdf0a0da9e91379e1d3...@ditdit.us> Content-Type: text/plain; charset=US-ASCII; format=flowed HI Everyone, Sorry for the totally noob uneducated question. I'm an engineer by training, not an accountant! I recently started a home business and everyone said "Quickbooks". It's too expensive and I despise subscription software. Online/cloud options are not an option, we have limited (and expensive) internet where we live. The business is repairing custom electronic equipment. What I want/need: Basic money tracking for the checking account. Keep track of equipment based on serial number (I was thinking a database). Here I will keep a record of owner(s), data like software versions, and work done per RMA. RMA tracking (another database?). Ability to generate RMA numbers or use one supplied by the distributor. Invoicing based on what was done on an RMA (labor, parts and state tax if applicable). Simple payroll (I'm the only employee). Light duty inventory control. I have about 30 items, some are almost too cheap to track, a few items are expensive, some of those are supplied by the manufacturer so I need to settle with them at the end of the month. My wife wants to run the entire business from Excel, and in one spreadsheet I think? Databases seem more proper to me :-) Thanks and enjoy the weekend. Dave -- Dit Dit Electronics References 1. mailto:gnucash-user-requ...@gnucash.org 2. mailto:gnucash-user@gnucash.org 3. mailto:d...@ditdit.us 4. mailto:gnucash-user@gnucash.org 5. mailto:3d2f789355cdf0a0da9e91379e1d3...@ditdit.us ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: [GNC] Example of importing Stock transactions from CSV
The thread at https://lists.gnucash.org/pipermail/gnucash-user/2019-March/083442.html goes into quite a discussion about how to import multi line csv files. On August 21, 2022 6:26:24 PM GMT+03:00, Jon Schewe wrote: >David, > >I'm using GnuCash 4.11 from FlatPak. I have all of my transactions on >single lines (I've posted samples in this thread). >I would greatly appreciate someone explaining how to make this work and >I'd be happy to help update the documentation on this. > >Jon > > >On Sun, 2022-08-21 at 09:17 -0500, David Carlson wrote: >> Jon, >> >> If you are not using some recent 4.x version of GnuCash you may not >> have the latest improvements to the CSV transaction importer. >> >> A normal stock purchase or sale transaction has two parts, one, a >> deposit or withdrawal of cash from a brokerage account, the other an >> exchange of the cash for a security at some exchange rate, aka >> price. For this discussion I will ignore commissions and fees. >> >> Thus you want to have two CSV lines per transaction. Also, when >> importing the CSV file, you will need to check the Multi-split box to >> tell GnuCash that is the type of file that you are importing. Then >> you can assign one line to the security purchase or sale and the >> other to the cash transfer. You can define fields in each line to >> match the respective lines in a completed transaction as seen in the >> security register. You only see the price and share details in the >> security register and the cash transfer details can be seen in both >> the security register and the brokerage register. >> >> I will admit that I have not done that myself, so I cannot provide an >> example for you to follow. Perhaps another user can provide an >> example. Good luck. >> >> On Sun, Aug 21, 2022 at 8:38 AM Jon Schewe wrote: >> > On Sat, 2022-08-20 at 12:20 -0400, Michael or Penny Novack wrote: >> > > On 8/19/2022 10:33 PM, Jon Schewe wrote: >> > > > So I didn't get the CSV import tool to work. >> > > >> > > Perhaps a reminder on what CSV is and is not. >> > > >> > > CSV stands for "common separated variables. That means the data >> > is in >> > > the form of a record consisting of a number of fields delineated >> > by >> > > commas. Which means two important things. The fields (individual >> > data >> > > elements) cannot contain commas but even more important, ORDER >> > > MATTERS. >> > > >> > > In other words, the record A,B,C,D is not the same a B,A,C,D << >> > A, B, >> > > C, >> > > and D being elements of data, names, amounts, etc. >> >> > > >> > > The CSV data you are importing must not only be in CSV format but >> > the >> > > data must be in the right order AND any data that is null (not >> > > present >> > > still has its space in the record. >> > > >> > > Thus if a record was supposed to include A, C, and D (B is not >> > > relevant >> > > to this record) it would look like A,,C,D and not A,C,D >> > > >> > > >> > > But even the "wrong" examples are in CSV format. >> > > >> > > >> > Yes, I know what a CSV file is and how it should be formatted. The >> > problem is not with the format of the file, that is handled by >> > GnuCash >> > just fine. The problem is the mapping of columns in the CSV file to >> > fields in GnuCash. I cannot figure out the correct mapping to get >> > the >> > correct transactions. >> > >> > >> > >> > I have tried all of the following possible mappings between GnuCash >> > fields and columns in my CSV file. In the end I finally wrote the >> > python script out of frustration of the import CSV transactions not >> > working. I'm hoping that someone on this list understands enough >> > python >> > and the import CSV transactions functionality enough to tell me how >> > to >> > get the mapping correct. >> > >> > # test 2 >> > >> > Transfer Account - FUND >> > Withdrawal - FUND UNITS >> > Account - Transfer Account >> > Transaction Commodity - Currency >> > >> > Shares are correct. >> > Close, need to get the dollars right now >> > >> > # test 3 >> > >> > Transfer Account - FUND >> > Withdrawal - FUND UNITS >> > Account - Transfer Account >> > Transaction Commodity - Currency >> > Deposit - AMOUNT >> > >> > None of the shares or cash lined up. >> > >> > # test 4 >> > >> > Try swapping withdrawal and deposit >> > >> > Transfer Account - FUND >> > Deposit - FUND UNITS >> > Account - Transfer Account >> > Transaction Commodity - Currency >> > Withdrawal - AMOUNT >> > >> > None of the shares or cash lined up. >> > >> > # test 5 >> > >> > Transfer Account - FUND >> > Withdrawal - FUND UNITS >> > Account - Transfer Account >> > Transaction Commodity - Currency >> > price - FUND UNITS >> > >> > Values don't line up with the price >> > >> > # test 6 >> > >> > Transfer Account - Transfer Account >> > Deposit - AMOUNT >> > Account - FUND >> > Transaction Commodity - Currency >> > >> > Price is right, Shares are the dollars >> > Shares times price equals dollars. >> > >> > >> >
Re: [GNC] Example of importing Stock transactions from CSV
> On Aug 21, 2022, at 6:37 AM, Jon Schewe wrote: > > On Sat, 2022-08-20 at 12:20 -0400, Michael or Penny Novack wrote: >> On 8/19/2022 10:33 PM, Jon Schewe wrote: >>> So I didn't get the CSV import tool to work. >> >> Perhaps a reminder on what CSV is and is not. >> >> CSV stands for "common separated variables. That means the data is in >> the form of a record consisting of a number of fields delineated by >> commas. Which means two important things. The fields (individual data >> elements) cannot contain commas but even more important, ORDER >> MATTERS. >> >> In other words, the record A,B,C,D is not the same a B,A,C,D << A, B, >> C, >> and D being elements of data, names, amounts, etc. >> >> >> The CSV data you are importing must not only be in CSV format but the >> data must be in the right order AND any data that is null (not >> present >> still has its space in the record. >> >> Thus if a record was supposed to include A, C, and D (B is not >> relevant >> to this record) it would look like A,,C,D and not A,C,D >> >> >> But even the "wrong" examples are in CSV format. >> >> > Yes, I know what a CSV file is and how it should be formatted. The > problem is not with the format of the file, that is handled by GnuCash > just fine. The problem is the mapping of columns in the CSV file to > fields in GnuCash. I cannot figure out the correct mapping to get the > correct transactions. > > > > I have tried all of the following possible mappings between GnuCash > fields and columns in my CSV file. In the end I finally wrote the > python script out of frustration of the import CSV transactions not > working. I'm hoping that someone on this list understands enough python > and the import CSV transactions functionality enough to tell me how to > get the mapping correct. > > # test 2 > > Transfer Account - FUND > Withdrawal - FUND UNITS > Account - Transfer Account > Transaction Commodity - Currency > > Shares are correct. > Close, need to get the dollars right now > > # test 3 > > Transfer Account - FUND > Withdrawal - FUND UNITS > Account - Transfer Account > Transaction Commodity - Currency > Deposit - AMOUNT > > None of the shares or cash lined up. > > # test 4 > > Try swapping withdrawal and deposit > > Transfer Account - FUND > Deposit - FUND UNITS > Account - Transfer Account > Transaction Commodity - Currency > Withdrawal - AMOUNT > > None of the shares or cash lined up. > > # test 5 > > Transfer Account - FUND > Withdrawal - FUND UNITS > Account - Transfer Account > Transaction Commodity - Currency > price - FUND UNITS > > Values don't line up with the price > > # test 6 > > Transfer Account - Transfer Account > Deposit - AMOUNT > Account - FUND > Transaction Commodity - Currency > > Price is right, Shares are the dollars > Shares times price equals dollars. > > > # test 7 > > Transfer Account - Transfer Account > Account - FUND > Transaction Commodity - Fund Commodity > Deposit - FUND UNITS > > Nothing is right, couldn't find the account to transfer from and price > is 1. > The CSV importer doesn't know how to calculate prices and there's no way to tell it the value (in the ttransaction currency), only the amount (in the account's commodity). You should supply the price instead. Ideally that will be the fraction value/amount (multiply both by the power of 10 needed to make them integers, so e.g you'd write $497.65 for 57 shares as 49765/5700. The difference between a deposit column and a withdrawal column is that the latter is sign-reversed. A positive deposit will increase the balance of an asset account and reduce the value of a liability or equity account; a positive withdrawal will do the opposite. Regards, John Ralls ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: [GNC] Example of importing Stock transactions from CSV
David, I'm using GnuCash 4.11 from FlatPak. I have all of my transactions on single lines (I've posted samples in this thread). I would greatly appreciate someone explaining how to make this work and I'd be happy to help update the documentation on this. Jon On Sun, 2022-08-21 at 09:17 -0500, David Carlson wrote: > Jon, > > If you are not using some recent 4.x version of GnuCash you may not > have the latest improvements to the CSV transaction importer. > > A normal stock purchase or sale transaction has two parts, one, a > deposit or withdrawal of cash from a brokerage account, the other an > exchange of the cash for a security at some exchange rate, aka > price. For this discussion I will ignore commissions and fees. > > Thus you want to have two CSV lines per transaction. Also, when > importing the CSV file, you will need to check the Multi-split box to > tell GnuCash that is the type of file that you are importing. Then > you can assign one line to the security purchase or sale and the > other to the cash transfer. You can define fields in each line to > match the respective lines in a completed transaction as seen in the > security register. You only see the price and share details in the > security register and the cash transfer details can be seen in both > the security register and the brokerage register. > > I will admit that I have not done that myself, so I cannot provide an > example for you to follow. Perhaps another user can provide an > example. Good luck. > > On Sun, Aug 21, 2022 at 8:38 AM Jon Schewe wrote: > > On Sat, 2022-08-20 at 12:20 -0400, Michael or Penny Novack wrote: > > > On 8/19/2022 10:33 PM, Jon Schewe wrote: > > > > So I didn't get the CSV import tool to work. > > > > > > Perhaps a reminder on what CSV is and is not. > > > > > > CSV stands for "common separated variables. That means the data > > is in > > > the form of a record consisting of a number of fields delineated > > by > > > commas. Which means two important things. The fields (individual > > data > > > elements) cannot contain commas but even more important, ORDER > > > MATTERS. > > > > > > In other words, the record A,B,C,D is not the same a B,A,C,D << > > A, B, > > > C, > > > and D being elements of data, names, amounts, etc. >> > > > > > > The CSV data you are importing must not only be in CSV format but > > the > > > data must be in the right order AND any data that is null (not > > > present > > > still has its space in the record. > > > > > > Thus if a record was supposed to include A, C, and D (B is not > > > relevant > > > to this record) it would look like A,,C,D and not A,C,D > > > > > > > > > But even the "wrong" examples are in CSV format. > > > > > > > > Yes, I know what a CSV file is and how it should be formatted. The > > problem is not with the format of the file, that is handled by > > GnuCash > > just fine. The problem is the mapping of columns in the CSV file to > > fields in GnuCash. I cannot figure out the correct mapping to get > > the > > correct transactions. > > > > > > > > I have tried all of the following possible mappings between GnuCash > > fields and columns in my CSV file. In the end I finally wrote the > > python script out of frustration of the import CSV transactions not > > working. I'm hoping that someone on this list understands enough > > python > > and the import CSV transactions functionality enough to tell me how > > to > > get the mapping correct. > > > > # test 2 > > > > Transfer Account - FUND > > Withdrawal - FUND UNITS > > Account - Transfer Account > > Transaction Commodity - Currency > > > > Shares are correct. > > Close, need to get the dollars right now > > > > # test 3 > > > > Transfer Account - FUND > > Withdrawal - FUND UNITS > > Account - Transfer Account > > Transaction Commodity - Currency > > Deposit - AMOUNT > > > > None of the shares or cash lined up. > > > > # test 4 > > > > Try swapping withdrawal and deposit > > > > Transfer Account - FUND > > Deposit - FUND UNITS > > Account - Transfer Account > > Transaction Commodity - Currency > > Withdrawal - AMOUNT > > > > None of the shares or cash lined up. > > > > # test 5 > > > > Transfer Account - FUND > > Withdrawal - FUND UNITS > > Account - Transfer Account > > Transaction Commodity - Currency > > price - FUND UNITS > > > > Values don't line up with the price > > > > # test 6 > > > > Transfer Account - Transfer Account > > Deposit - AMOUNT > > Account - FUND > > Transaction Commodity - Currency > > > > Price is right, Shares are the dollars > > Shares times price equals dollars. > > > > > > # test 7 > > > > Transfer Account - Transfer Account > > Account - FUND > > Transaction Commodity - Fund Commodity > > Deposit - FUND UNITS > > > > Nothing is right, couldn't find the account to transfer from and > > price > > is 1. > > > > ___ > > gnucash-user mailing list > > gnucash-user@gnucash.org > > To update
Re: [GNC] Example of importing Stock transactions from CSV
Jon, If you are not using some recent 4.x version of GnuCash you may not have the latest improvements to the CSV transaction importer. A normal stock purchase or sale transaction has two parts, one, a deposit or withdrawal of cash from a brokerage account, the other an exchange of the cash for a security at some exchange rate, aka price. For this discussion I will ignore commissions and fees. Thus you want to have two CSV lines per transaction. Also, when importing the CSV file, you will need to check the Multi-split box to tell GnuCash that is the type of file that you are importing. Then you can assign one line to the security purchase or sale and the other to the cash transfer. You can define fields in each line to match the respective lines in a completed transaction as seen in the security register. You only see the price and share details in the security register and the cash transfer details can be seen in both the security register and the brokerage register. I will admit that I have not done that myself, so I cannot provide an example for you to follow. Perhaps another user can provide an example. Good luck. On Sun, Aug 21, 2022 at 8:38 AM Jon Schewe wrote: > On Sat, 2022-08-20 at 12:20 -0400, Michael or Penny Novack wrote: > > On 8/19/2022 10:33 PM, Jon Schewe wrote: > > > So I didn't get the CSV import tool to work. > > > > Perhaps a reminder on what CSV is and is not. > > > > CSV stands for "common separated variables. That means the data is in > > the form of a record consisting of a number of fields delineated by > > commas. Which means two important things. The fields (individual data > > elements) cannot contain commas but even more important, ORDER > > MATTERS. > > > > In other words, the record A,B,C,D is not the same a B,A,C,D << A, B, > > C, > > and D being elements of data, names, amounts, etc. >> > > > > The CSV data you are importing must not only be in CSV format but the > > data must be in the right order AND any data that is null (not > > present > > still has its space in the record. > > > > Thus if a record was supposed to include A, C, and D (B is not > > relevant > > to this record) it would look like A,,C,D and not A,C,D > > > > > > But even the "wrong" examples are in CSV format. > > > > > Yes, I know what a CSV file is and how it should be formatted. The > problem is not with the format of the file, that is handled by GnuCash > just fine. The problem is the mapping of columns in the CSV file to > fields in GnuCash. I cannot figure out the correct mapping to get the > correct transactions. > > > > I have tried all of the following possible mappings between GnuCash > fields and columns in my CSV file. In the end I finally wrote the > python script out of frustration of the import CSV transactions not > working. I'm hoping that someone on this list understands enough python > and the import CSV transactions functionality enough to tell me how to > get the mapping correct. > > # test 2 > > Transfer Account - FUND > Withdrawal - FUND UNITS > Account - Transfer Account > Transaction Commodity - Currency > > Shares are correct. > Close, need to get the dollars right now > > # test 3 > > Transfer Account - FUND > Withdrawal - FUND UNITS > Account - Transfer Account > Transaction Commodity - Currency > Deposit - AMOUNT > > None of the shares or cash lined up. > > # test 4 > > Try swapping withdrawal and deposit > > Transfer Account - FUND > Deposit - FUND UNITS > Account - Transfer Account > Transaction Commodity - Currency > Withdrawal - AMOUNT > > None of the shares or cash lined up. > > # test 5 > > Transfer Account - FUND > Withdrawal - FUND UNITS > Account - Transfer Account > Transaction Commodity - Currency > price - FUND UNITS > > Values don't line up with the price > > # test 6 > > Transfer Account - Transfer Account > Deposit - AMOUNT > Account - FUND > Transaction Commodity - Currency > > Price is right, Shares are the dollars > Shares times price equals dollars. > > > # test 7 > > Transfer Account - Transfer Account > Account - FUND > Transaction Commodity - Fund Commodity > Deposit - FUND UNITS > > Nothing is right, couldn't find the account to transfer from and price > is 1. > > ___ > gnucash-user mailing list > gnucash-user@gnucash.org > To update your subscription preferences or to unsubscribe: > https://lists.gnucash.org/mailman/listinfo/gnucash-user > - > Please remember to CC this list on all your replies. > You can do this by using Reply-To-List or Reply-All. > -- David Carlson ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: [GNC] Example of importing Stock transactions from CSV
I’m importing prices —— I smell carrots a-cooking and where there’s carrots, there’s a rabbit. — Yosemite Sam > On Aug 21, 2022, at 9:34 AM, Jon Schewe wrote: > > > Jack, > > I have tried using this configuration multiple times to get the import to > work. I have a list of column assignments, none of which have worked > correctly. Thus the python script that does in fact work. In your screenshot > I see column options that don't exist for me. Are you importing transactions > or prices? I'm trying to import transactions. > > Jon > > >> On Sat, 2022-08-20 at 11:54 -0400, Jack Frillman via gnucash-user wrote: >> I use CSV import almost every day and it works just fine. >> You do have to setup a configuration file that tells the importer how to >> interpret your CSV data. I suspect you did not do that. >> Here is an example of my CSV data: >> >> ARSIX,14.82,Ray Jay,2022/08/20,USD >> BAFGX,26.47,Ray Jay,2022/08/20,USD >> BAGIX,10.07,Ray Jay,2022/08/20,USD >> CISMX,16.75,Ray Jay,2022/08/20,USD >> GSFTX,29.98,Ray Jay,2022/08/20,USD >> >> Each column of the separated values above are defined when you set the >> importer up. >> I saved this in a file called "ira quotes" and it's selected in the >> attached screen shot. The bottom window is the result of mapping of the >> "irs quotes" file to the CSV data above. >> >> As for your Python script I have no idea what it's supposed to do. >> I have a much smaller Python script that scrapes the closing day prices >> off a web page and creates the CSV file I import. >> >> Jack >> >>> On 8/19/22 10:33 PM, Jon Schewe wrote: >>> So I didn't get the CSV import tool to work. However I was able to >>> write a python script to do the import. I would think that this would >>> be possible with the CSV import, but I can't seem to figure out how to >>> do the right column assignments to make it work. I have attached the >>> python script and a sample input CSV file. >>> >>> If someone that knows how the CSV import code works and can compare >>> with my script and tell me how to use the CSV import tool for this, >>> that would be great. >>> >>> Thanks, >>> >>> Jon >>> >>> On Tue, 2022-07-12 at 08:45 -0500, Jon Schewe wrote: That looks similar to what I'm doing, except all of my data is on a single line in the CSV file. The biggest problem seems to be how to convince GnuCash to use 2 different commodities for the transaction when importing from CSV. > On Tue, 2022-07-12 at 13:04 +1000, Geoff wrote: > Hi Jon > > This isn't exactly what you are asking for, but it may give you > some > clues. Importing Dividends from CSV:- > > https://lists.gnucash.org/pipermail/gnucash-user/2020-August/092768.html > > > Good luck! > > Geoff > = > >> On 12/07/2022 12:35 pm, Jon Schewe wrote: >> Does anyone have an example of importing stock transactions from >> CSV? >> >> I have matched columns to Date, Description, Account, Price, >> Deposit, >> Transfer Account. >> The "Account" column is the mutual fund account. >> The "Transfer Account" column is the currency account. >> >> When I start the import process all of my transactions need an >> account >> to transfer from, despite specifying the Transfer Account. When I >> try >> and select my currency account I'm told that it has the wrong >> commodity. >> >> I have another column for the currency amount, but I don't know >> what to >> map that to in the importer. >> >> I tried checkout out >> https://www.gnucash.org/docs/v4/C/gnucash-help/trans-import.html#trans-import-csv >> >>but it doesn't seem to address this. >> Suggestions on where I'm going wrong. >> >> Jon >> >> >> ___ >> 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. ___ 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. >>> >>> ___ >>> gnucash-user mailing list >>> gnucash-user@gnucash.org >>> To
Re: [GNC] Example of importing Stock transactions from CSV
On Sat, 2022-08-20 at 12:20 -0400, Michael or Penny Novack wrote: > On 8/19/2022 10:33 PM, Jon Schewe wrote: > > So I didn't get the CSV import tool to work. > > Perhaps a reminder on what CSV is and is not. > > CSV stands for "common separated variables. That means the data is in > the form of a record consisting of a number of fields delineated by > commas. Which means two important things. The fields (individual data > elements) cannot contain commas but even more important, ORDER > MATTERS. > > In other words, the record A,B,C,D is not the same a B,A,C,D << A, B, > C, > and D being elements of data, names, amounts, etc. >> > > The CSV data you are importing must not only be in CSV format but the > data must be in the right order AND any data that is null (not > present > still has its space in the record. > > Thus if a record was supposed to include A, C, and D (B is not > relevant > to this record) it would look like A,,C,D and not A,C,D > > > But even the "wrong" examples are in CSV format. > > Yes, I know what a CSV file is and how it should be formatted. The problem is not with the format of the file, that is handled by GnuCash just fine. The problem is the mapping of columns in the CSV file to fields in GnuCash. I cannot figure out the correct mapping to get the correct transactions. I have tried all of the following possible mappings between GnuCash fields and columns in my CSV file. In the end I finally wrote the python script out of frustration of the import CSV transactions not working. I'm hoping that someone on this list understands enough python and the import CSV transactions functionality enough to tell me how to get the mapping correct. # test 2 Transfer Account - FUND Withdrawal - FUND UNITS Account - Transfer Account Transaction Commodity - Currency Shares are correct. Close, need to get the dollars right now # test 3 Transfer Account - FUND Withdrawal - FUND UNITS Account - Transfer Account Transaction Commodity - Currency Deposit - AMOUNT None of the shares or cash lined up. # test 4 Try swapping withdrawal and deposit Transfer Account - FUND Deposit - FUND UNITS Account - Transfer Account Transaction Commodity - Currency Withdrawal - AMOUNT None of the shares or cash lined up. # test 5 Transfer Account - FUND Withdrawal - FUND UNITS Account - Transfer Account Transaction Commodity - Currency price - FUND UNITS Values don't line up with the price # test 6 Transfer Account - Transfer Account Deposit - AMOUNT Account - FUND Transaction Commodity - Currency Price is right, Shares are the dollars Shares times price equals dollars. # test 7 Transfer Account - Transfer Account Account - FUND Transaction Commodity - Fund Commodity Deposit - FUND UNITS Nothing is right, couldn't find the account to transfer from and price is 1. ___ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user - Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
Re: [GNC] Example of importing Stock transactions from CSV
Jack, I have tried using this configuration multiple times to get the import to work. I have a list of column assignments, none of which have worked correctly. Thus the python script that does in fact work. In your screenshot I see column options that don't exist for me. Are you importing transactions or prices? I'm trying to import transactions. Jon On Sat, 2022-08-20 at 11:54 -0400, Jack Frillman via gnucash-user wrote: > I use CSV import almost every day and it works just fine. > You do have to setup a configuration file that tells the importer how > to > interpret your CSV data. I suspect you did not do that. > Here is an example of my CSV data: > > ARSIX,14.82,Ray Jay,2022/08/20,USD > BAFGX,26.47,Ray Jay,2022/08/20,USD > BAGIX,10.07,Ray Jay,2022/08/20,USD > CISMX,16.75,Ray Jay,2022/08/20,USD > GSFTX,29.98,Ray Jay,2022/08/20,USD > > Each column of the separated values above are defined when you set > the > importer up. > I saved this in a file called "ira quotes" and it's selected in the > attached screen shot. The bottom window is the result of mapping of > the > "irs quotes" file to the CSV data above. > > As for your Python script I have no idea what it's supposed to do. > I have a much smaller Python script that scrapes the closing day > prices > off a web page and creates the CSV file I import. > > Jack > > On 8/19/22 10:33 PM, Jon Schewe wrote: > > So I didn't get the CSV import tool to work. However I was able to > > write a python script to do the import. I would think that this > > would > > be possible with the CSV import, but I can't seem to figure out how > > to > > do the right column assignments to make it work. I have attached > > the > > python script and a sample input CSV file. > > > > If someone that knows how the CSV import code works and can compare > > with my script and tell me how to use the CSV import tool for this, > > that would be great. > > > > Thanks, > > > > Jon > > > > > > On Tue, 2022-07-12 at 08:45 -0500, Jon Schewe wrote: > > > That looks similar to what I'm doing, except all of my data is on > > > a > > > single line in the CSV file. The biggest problem seems to be how > > > to > > > convince GnuCash to use 2 different commodities for the > > > transaction > > > when importing from CSV. > > > > > > > > > On Tue, 2022-07-12 at 13:04 +1000, Geoff wrote: > > > > Hi Jon > > > > > > > > This isn't exactly what you are asking for, but it may give you > > > > some > > > > clues. Importing Dividends from CSV:- > > > > > > > > https://lists.gnucash.org/pipermail/gnucash-user/2020-August/092768.html > > > > > > > > > > > > Good luck! > > > > > > > > Geoff > > > > = > > > > > > > > On 12/07/2022 12:35 pm, Jon Schewe wrote: > > > > > Does anyone have an example of importing stock transactions > > > > > from > > > > > CSV? > > > > > > > > > > I have matched columns to Date, Description, Account, Price, > > > > > Deposit, > > > > > Transfer Account. > > > > > The "Account" column is the mutual fund account. > > > > > The "Transfer Account" column is the currency account. > > > > > > > > > > When I start the import process all of my transactions need > > > > > an > > > > > account > > > > > to transfer from, despite specifying the Transfer Account. > > > > > When I > > > > > try > > > > > and select my currency account I'm told that it has the wrong > > > > > commodity. > > > > > > > > > > I have another column for the currency amount, but I don't > > > > > know > > > > > what to > > > > > map that to in the importer. > > > > > > > > > > I tried checkout out > > > > > https://www.gnucash.org/docs/v4/C/gnucash-help/trans-import.html#trans-import-csv > > > > > > > > > > but it doesn't seem to address this. > > > > > Suggestions on where I'm going wrong. > > > > > > > > > > Jon > > > > > > > > > > > > > > > ___ > > > > > 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. > > > ___ > > > 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. > > > > ___ > > gnucash-user mailing list > > gnucash
