Re: [GNC] Example of importing Stock transactions from CSV
This post is from the https://lists.gnucash.org/pipermail/gnucash-user/2024-July/112463.html thread with discussion moving in a different direction. https://lists.gnucash.org/pipermail/gnucash-user/2024-July/112470.html Greg Julius wrote: > FWIW, I really struggled with this to figure out what was wrong with my > imports. There is a Commodity field and there is a Currency field. I had > to repeatedly fill in fields until I found the issue was Currency. Greg, Are you suggesting this is a Commodity/Currency field issue? I've added the full process from the linked bug report - see RIO near the end. My GnuCash file has transactions from previous years that were imported properly. https://lists.gnucash.org/pipermail/gnucash-user/2022-August/102562.html > >> The three lines below *work* using a Multi-split import - based on >> https://bugs.gnucash.org/show_bug.cgi?id=797450 >> Save *PP-Demo_Transactions_Minimal_Priced2.csv*: >> Date,Description,Action,Account,Deposit,Rate/Price >> 01/02/2021,RIO,,Assets:Broker:Funds,-9912.53 >> ,,Buy,Assets:Broker:Shares:RIO,89,8900/991253 > > GnuCash (brackets at end of line show changes for V5.8) > File, New > Cancel > Tools, Security Editor > Securities: Add > New security > Full Name: RIO > Symbol: RIO > Type: ASX (Namespace) > OK > Close > File, Import, Transactions from CSV > Next, PP-Demo_Transactions_Minimal.csv, Next > Multi-split, DateFormat, Leading Lines to Skip 1, > Date, Description, Action, Account, Deposit, Price (Amount instead of > Deposit) > Next > Map Account ID to Account Name > Change GnuCash Account, New Account > > Manually parse the accounts at ":" Creating new accounts until they are > done. > RIO is Account Type: Stock, Security: RIO, OK (Note security Namespace: > ASX) > Next (If New Book Options is displayed just Close), Next > Ignore UNBALANCED, Apply, Close > If it was UNBALANCED then Import was not successful. Interestingly, this previously working file now has three field errors (Date, Amount, Price): > Save PP-Demo_Transactions_Minimal_Priced.csv > Date,Description,Action,Account,Deposit,Rate/Price > 01/02/2021,RIO,,Assets:Broker:Funds,-9912.53 > ,,Buy,Assets:Broker:Shares:RIO,89,111 + 3353/8900 Regards ___ 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
Did you carefully work through the known working process described in the bug report Comment 3 using the file below? The broker csv file almost certainly needs manipulating. The best approach is to enter the data into GnuCash, and then export the account to csv. You can use the exported csv to see the data required in the fields (except for price). Something like the transaction below should import successfully. You can then add extra fields or try a double split on a single line. If it doesn't import, attach the new csv file and the one you exported to a message in the mail list so the community can troubleshoot it. https://lists.gnucash.org/pipermail/gnucash-user/2022-August/102562.html > The three lines below work using a Multi-split import - based on > https://bugs.gnucash.org/show_bug.cgi?id=797450 > > Save *PP-Demo_Transactions_Minimal_Priced2.csv*: > > Date,Description,Action,Account,Deposit,Rate/Price > 01/02/2021,RIO,,Assets:Broker:Funds,-9912.53 > ,,Buy,Assets:Broker:Shares:RIO,89,8900/991253 > >> ___ 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
My accounts were created before GnuCash defaulted to setting the top level accounts as placeholders. So yes, my Expenses account will allow transactions to use it. When I get to the "Match Transactions" step, none of the transactions have transfer accounts. When click the Add column and then try and select an Asset account (ie. my retirement cash account for a purchase), I get the error: The account "401k" has a different commodity to the one required, 'Alight Equity Fund (S&P 500)'. Please choose a different account. On Wed, 2022-08-24 at 21:47 -0500, David Carlson wrote: > The GnuCash account structure is dictated by the program and it will > not > necessarily exactly match what your brokerage statement shows. As one > of > the previous comments noted, there are some bug reports indicating > that the > csv importer is not able to import some transactions . You may need > to > fudge your data or manually massage it after your import. > > On Wed, Aug 24, 2022, 8:55 PM Adrien Monteleone < > adrien.montele...@lusfiber.net> wrote: > > > As for using the Expenses account, unless you intentionally made it > > *not* a placeholder account, you can't put transactions in it. I'll > > hazard a guess that is why the importer is not assigning it. > > > > Create a sub-account of Expenses for tracking the commissions/fees > > and > > try to assign that one in your CSV following the other tips David > > provided. (making the fee as a separate transaction with splits > > between > > an expense account and a brokerage account - *not* involving the > > security account.) I'm also going to hazard that the Brokerage and > > the > > expense sub-account might need to be in the same currency, else > > you'll > > need Trading Accounts turned on to handle conversion splits so > > things > > work out properly. > > > > As for why the security transaction alone won't import, I'm not > > sure. > > Did you try it all by itself in the CSV? (to eliminate variables) > > > > Regards, > > Adrien > > > > On 8/24/22 8:32 PM, Jon Schewe wrote: > > > I used the parent Expense account because I don't really need to > > > track > > > it separately. The fee is paid directly out of the stock account. > > > I > > > don't see how adding an additional account is going to fit the > > > commodity problem. The second line is a purchase from a specific > > > Asset > > > account and that transaction won't import either. > > > > > > ___ > > 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. > > > ___ > 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. ___ 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 GnuCash account structure is dictated by the program and it will not necessarily exactly match what your brokerage statement shows. As one of the previous comments noted, there are some bug reports indicating that the csv importer is not able to import some transactions . You may need to fudge your data or manually massage it after your import. On Wed, Aug 24, 2022, 8:55 PM Adrien Monteleone < adrien.montele...@lusfiber.net> wrote: > As for using the Expenses account, unless you intentionally made it > *not* a placeholder account, you can't put transactions in it. I'll > hazard a guess that is why the importer is not assigning it. > > Create a sub-account of Expenses for tracking the commissions/fees and > try to assign that one in your CSV following the other tips David > provided. (making the fee as a separate transaction with splits between > an expense account and a brokerage account - *not* involving the > security account.) I'm also going to hazard that the Brokerage and the > expense sub-account might need to be in the same currency, else you'll > need Trading Accounts turned on to handle conversion splits so things > work out properly. > > As for why the security transaction alone won't import, I'm not sure. > Did you try it all by itself in the CSV? (to eliminate variables) > > Regards, > Adrien > > On 8/24/22 8:32 PM, Jon Schewe wrote: > > I used the parent Expense account because I don't really need to track > > it separately. The fee is paid directly out of the stock account. I > > don't see how adding an additional account is going to fit the > > commodity problem. The second line is a purchase from a specific Asset > > account and that transaction won't import either. > > > ___ > 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. > ___ 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
As for using the Expenses account, unless you intentionally made it *not* a placeholder account, you can't put transactions in it. I'll hazard a guess that is why the importer is not assigning it. Create a sub-account of Expenses for tracking the commissions/fees and try to assign that one in your CSV following the other tips David provided. (making the fee as a separate transaction with splits between an expense account and a brokerage account - *not* involving the security account.) I'm also going to hazard that the Brokerage and the expense sub-account might need to be in the same currency, else you'll need Trading Accounts turned on to handle conversion splits so things work out properly. As for why the security transaction alone won't import, I'm not sure. Did you try it all by itself in the CSV? (to eliminate variables) Regards, Adrien On 8/24/22 8:32 PM, Jon Schewe wrote: I used the parent Expense account because I don't really need to track it separately. The fee is paid directly out of the stock account. I don't see how adding an additional account is going to fit the commodity problem. The second line is a purchase from a specific Asset account and that transaction won't import either. ___ 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 used the parent Expense account because I don't really need to track it separately. The fee is paid directly out of the stock account. I don't see how adding an additional account is going to fit the commodity problem. The second line is a purchase from a specific Asset account and that transaction won't import either. On Wed, 2022-08-24 at 09:41 -0500, David Carlson wrote: > Jon, > > My example that I gave earlier deliberately ignored commissions. I > assumed > the simpler method, as described somewhere in the help manual, that > net > prices were used instead. This will better match your broker's > statements > when you review the cost basis. If your first line is supposed to > indicate > a sale that is then applied to a (monthly?) fee, you should split it > into a > sale for cash, then use the cash to pay the fee. It would be > necessary to > import the fee expense payment into the brokerage account rather than > the > security account. > > > On Wed, Aug 24, 2022 at 8:35 AM Adrien Monteleone < > adrien.montele...@lusfiber.net> wrote: > > > Unless I've confused something, purchasing or selling a > > stock/fund/etc. > > should be between 2 asset accounts. Considering a split for a > > commission > > or such, (like the first line of your CSV) it would be a *specific* > > expense account, not the parent and placeholder "Expenses" account. > > > > > > Regards, > > Adrien > > > > On 8/24/22 6:23 AM, Jon Schewe wrote: > > > > > Given the attached CSV file > > > The AMOUNT column is dollars. The FUND UNITS column is in shares. > > > > > > Skip 1 row, use m-d-y for Date format > > > > > > Column Name -> GnuCash field > > > POSTING DATE -> Date > > > ACTIVITY TYPE -> Description > > > FUND -> Account > > > FUND UNITS -> Deposit > > > FUND NAV/PRICE -> Price > > > Transfer Account -> Transfer Account > > > Fund Commodity -> Transaction Commodity > > > > > > When I use those mappings GnuCash has the correct amounts in the > > > "Match > > > transaction" view, however it doesn't have a transfer account > > > specified. When I try and specify an account I'm told that the > > > Transaction Commodity doesn't match the commodity of the selected > > > account, which is Expenses. > > > > > > ___ > > 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. > > > > ___ 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 three lines below work using a Multi-split import - based on https://bugs.gnucash.org/show_bug.cgi?id=797450 Save *PP-Demo_Transactions_Minimal_Priced2.csv*: Date,Description,Action,Account,Deposit,Rate/Price 01/02/2021,RIO,,Assets:Broker:Funds,-9912.53 ,,Buy,Assets:Broker:Shares:RIO,89,8900/991253 > ___ 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
> Does the CSV importer handle floating point values for amount and price or do I need to use integers? Floating point works fine for me in all amounts except price: https://lists.gnucash.org/pipermail/gnucash-user/2022-August/102535.html > e.g you'd write $497.65 for 57 shares as 49765/5700 ___ 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, My example that I gave earlier deliberately ignored commissions. I assumed the simpler method, as described somewhere in the help manual, that net prices were used instead. This will better match your broker's statements when you review the cost basis. If your first line is supposed to indicate a sale that is then applied to a (monthly?) fee, you should split it into a sale for cash, then use the cash to pay the fee. It would be necessary to import the fee expense payment into the brokerage account rather than the security account. On Wed, Aug 24, 2022 at 8:35 AM Adrien Monteleone < adrien.montele...@lusfiber.net> wrote: > Unless I've confused something, purchasing or selling a stock/fund/etc. > should be between 2 asset accounts. Considering a split for a commission > or such, (like the first line of your CSV) it would be a *specific* > expense account, not the parent and placeholder "Expenses" account. > > > Regards, > Adrien > > On 8/24/22 6:23 AM, Jon Schewe wrote: > > > Given the attached CSV file > > The AMOUNT column is dollars. The FUND UNITS column is in shares. > > > > Skip 1 row, use m-d-y for Date format > > > > Column Name -> GnuCash field > > POSTING DATE -> Date > > ACTIVITY TYPE -> Description > > FUND -> Account > > FUND UNITS -> Deposit > > FUND NAV/PRICE -> Price > > Transfer Account -> Transfer Account > > Fund Commodity -> Transaction Commodity > > > > When I use those mappings GnuCash has the correct amounts in the "Match > > transaction" view, however it doesn't have a transfer account > > specified. When I try and specify an account I'm told that the > > Transaction Commodity doesn't match the commodity of the selected > > account, which is Expenses. > > > ___ > 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
Unless I've confused something, purchasing or selling a stock/fund/etc. should be between 2 asset accounts. Considering a split for a commission or such, (like the first line of your CSV) it would be a *specific* expense account, not the parent and placeholder "Expenses" account. Regards, Adrien On 8/24/22 6:23 AM, Jon Schewe wrote: Given the attached CSV file The AMOUNT column is dollars. The FUND UNITS column is in shares. Skip 1 row, use m-d-y for Date format Column Name -> GnuCash field POSTING DATE -> Date ACTIVITY TYPE -> Description FUND -> Account FUND UNITS -> Deposit FUND NAV/PRICE -> Price Transfer Account -> Transfer Account Fund Commodity -> Transaction Commodity When I use those mappings GnuCash has the correct amounts in the "Match transaction" view, however it doesn't have a transfer account specified. When I try and specify an account I'm told that the Transaction Commodity doesn't match the commodity of the selected account, which is Expenses. ___ 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
On Sun, 2022-08-21 at 09:01 -0700, john wrote: > > > > 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. Does the CSV importer handle floating point values for amount and price or do I need to use integers? Given the attached CSV file The AMOUNT column is dollars. The FUND UNITS column is in shares. Skip 1 row, use m-d-y for Date format Column Name -> GnuCash field POSTING DATE -> Date ACTIVITY TYPE -> Description FUND -> Account FUND UNITS -> Deposit FUND NAV/PRICE -> Price Transfer Account -> Transfer Account Fund Commodity -> Transaction Commodity When I use those mappings GnuCash has the correct amounts in the "Match transaction" view, however it doesn't have a transfer account specified. When I try and specify an account I'm told that the Transaction Commodity doesn't match the commodity of the selected account, which is Expenses. VALUATION DATE,POSTING DATE,ACTIVITY TYPE,PLAN,ACCOUNT,FUND,AMOUNT,FUND NAV/PRICE,FUND UNITS,Transfer Account,Currency,Fund Commodity 06-17-2022,06-17-2022,
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] 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] 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
Re: [GNC] Example of importing Stock transactions from CSV
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. Michael ___ 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
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.
#!/usr/bin/env python3
import warnings
with warnings.catch_warnings():
import re
import sys
import argparse
import os
import logging
import logging.config
import json
from pathlib import Path
import gnucash
import itertools
import functools
import csv
import datetime
SCRIPT_DIR=Path(__file__).parent.absolute()
def get_logger():
return logging.getLogger(__name__)
def setup_logging(
default_path='logging.json',
default_level=logging.INFO,
env_key='LOG_CFG'
):
"""
Setup logging configuration
"""
try:
path = Path(default_path)
value = os.getenv(env_key, None)
if value:
path = Path(value)
if path.exists():
with open(path, 'r') as f:
config = json.load(f)
logging.config.dictConfig(config)
else:
logging.basicConfig(level=default_level)
except:
print(f"Error configuring logging, using default configuration with level {default_level}")
logging.basicConfig(level=default_level)
@functools.cache
def find_commodity(book, fund_name : str):
"""
Arguments:
book: GnuCash book
fund_name: name of fund to find as substring of commodity full name
Returns:
list: All commodities that match the name
"""
comm_table = book.get_table()
# chain and * flatten the list of lists
return list(itertools.chain(*[ [x for x in comm_table.get_commodities(ns) if fund_name in x.get_fullname() ] for ns in comm_table.get_namespaces() ]))
def get_usd(book):
currencies = book.get_table().find_namespace('CURRENCY')
possible_usd = [ x for x in currencies.get_commodity_list() if x.get_mnemonic() == 'USD' ]
if len(possible_usd) < 1:
raise RuntimeError("Cannot find USD")
if len(possible_usd) > 1:
raise RuntimeError(f"Found multipl
Re: [GNC] Example of importing Stock transactions from CSV
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.
Re: [GNC] Example of importing Stock transactions from CSV
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.
