Re: [GNC] csv of invoice data
Follow up to: https://lists.gnucash.org/pipermail/gnucash-user/2023-August/108625.html Apologies for not following up until after the month has rolled over... David, yes. Thank you. I think that has put me on the right track. There's a bit of a steep hill to climb, but at least I know now which hill it is :) M I needed to play with the invoices data a while ago and used the query below (gnucash file saved as an sqlite database). I didn't need the full invoice details and only selected a few of the fields. This should be enough to get you started. Note that the invoices are in one table and the invoice items are in another table. SELECT invoices.date_posted, invoices.id, invoices.notes, accounts.name AS acct_name, entries.description, quantity_num, quantity_denom, b_price_num, b_price_denom, quantity_num / CAST(quantity_denom AS FLOAT) AS quantity, b_price_num / CAST(b_price_denom AS FLOAT) AS price FROM entries, accounts LEFT JOIN invoices ON entries.bill = invoices.guid David On Mon, 28 Aug 2023 at 16:24, Morgan Read via gnucash-user wrote: Hi again :) In addition to wanting to deal with some old invoices, I'm also wanting to generate a csv of invoice data. There's this post a few years back, but I feel there must be better. I've discovered the page on 'ledger-cli': https://wiki.gnucash.org/wiki/Ledger-CLI - which seems complicated (perhaps not) but, I'm using a sqlite as a back-end anyway. It says here: https://wiki.gnucash.org/wiki/Published_tools That bad things will happen if accessing the GnuCash data outside of GnuCash - but, one of the reasons for switching to sqlite a while back was accessing the db to pull things out - and no harm can be done if the db is accessed read-only, can it? Further down the page there's some links to export invoice data under 'Export invoices' - the GIP invoice printer says something about a csv template: https://lists.gnucash.org/pipermail/gnucash-user/2008-June/025615.html I've set up access to the db via unixodbc and from there opened it in LO Base - but having got there, I got to wondering if there's an easier way to get a csv of invoice data? Thanks -- Morgan Read Grande Bretagne Em: OpenPGP_signature Description: OpenPGP digital signature ___ 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] moving unpaid invoices between businesses?
Follow up to: https://lists.gnucash.org/pipermail/gnucash-user/2023-August/108616.html And, apologies again for not following up until after the month has rolled over... Assume this is the year 1950 and so you are keeping your books the way they did back then, pen and ink on accounting ruled paper. How would you record this transaction? Assuming you had that clearly laid out, what difficulties are you having doing that using gnucash... Michael, there's a lot of assumption in there... And yes I agree, similar to selling a receivable to a factor or debt collection service. The fact that you might be the owner of both is irrelevant. However the state/condition of the first business might be relevant. The state of CONTESTED invoices when a business id closing down or in bankruptcy proceedings will probably differ jurisdiction to jurisdiction. So, invoices not contested; businesses both in business (on going). Then, what do I put on the invoice for the invoice/debt that I'm selling to the other business? I mean, what *are* the unpaid invoices that I'm selling? And then, I should probably raise a bill in the receiving business, which will create a hole in need of filling - to be filled by a new invoice issued from the receiving business to the errant clients? This feels like it must be a fairly common circumstance, but none of the account websites I've dragged up seem show how it's done - perhaps my google fu is rusty. Many thanks M. -- Morgan Read Grande Bretagne Em: OpenPGP_signature Description: OpenPGP digital signature ___ 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] moving unpaid invoices between businesses?
On 9/2/2023 8:58 AM, Morgan Read wrote: Follow up to: https://lists.gnucash.org/pipermail/gnucash-user/2023-August/108616.html And, apologies again for not following up until after the month has rolled over... Assume this is the year 1950 and so you are keeping your books the way they did back then, pen and ink on accounting ruled paper. How would you record this transaction? Assuming you had that clearly laid out, what difficulties are you having doing that using gnucash... Michael, there's a lot of assumption in there... Whenever I do this (refer to knowing how the transaction would be entered back in the days of pen and ink on paper) it is because essentially all that gnucash is doing is automating parts of that process (and allowing "journal less entry" (aka "cashbook") for all accounts as long as the transaction only involves two accounts**). In other words, if you haven't a clue what it would look like "pen and ink on paper" (what accounts involved, which debit, which credit, etc.) then you don't have a gnucash problem as much as an accounting/bookkeeping problem. In your case, a bit more complex since involving invoices/receivables, but you know, they were doing that back in the days of pen and ink on paper. Michael D Novack ** when more than two accounts and you enter the transaction as "split" you ARE creating the transaction entry in the (virtual) journal and when you hit "enter" (finish this transaction) you are "posting" this journal entry to the ledger accounts. Mind, the similarity is less obvious to those of you who didn't learn bookkeeping in the days of pen and ink on paper. ___ 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] Advance portfolio report between two dates
That doesn't seem to be something the Advanced Portfolio report is made to do. It is meant to provide a snapshot of things at a particular point in time, based upon the cost/earnings/increase in value of investments. I think, if I wanted to do this, I'd approach it thus, unless someone else has an easier way: (1) Set the ending date of the report to the start date of the timespan of interest. (2) Export the report for the start date. (3) Set the ending date of the report to the ending date of the timespan. (4) Export the report for the ending date. (5) Use a text editor or any other means you might have of editing an HTML file, to "convert" the HTML code to a format that is acceptable for a spreadsheet program. Probably CSV or something like that. Do this for both files. (6) Read the two files into a spreadsheet program, then do whatever you like with the data. It's not an elegant one-button approach, but it will get you the info you need. Again that's my "first guess" as to a method. Maybe someone more conversant in such activities will provide a more efficient method. ___ 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] Import question
On Thu, Aug 31, 2023 at 10:15 PM Nicolas de La Chaise via gnucash-user < gnucash-user@gnucash.org> wrote: > Hello all, > > First of all, I want to take this opportunity to thank all the posters > and developers who dedicate so much of their time to help the ungrateful > mass I belong to. You guys rock. > > Now, my silly problem... > > My bank only offers 2 formats when exporting transactions: either QIF or > CSV. > > If I choose to import in QIF, there is no way to see the amount of the > transaction that is being imported. I find it quite useful as some > online shopping transactions can fall into many different accounts. > Would it be possible to see it added in an upcoming version? > > Now, to go around this I was hoping to use the csv format. Only to hit a > different problem. The CSV I import has French number formatting > (decimal separator is ','). And when the import tool runs it seems to > get awfully confused by it. In the screenshot below, amounts should be > (for UK/US readers) -50.8 / -40.0 / -5.7 / -1.6 > I already poked around with encoding and currency format to no avail. > The amount is split in two and cannot be glued back together. > Do anyone has a workaround? > > Hi. The issue may be with the CSV (which stands for COMMA separated values)... if the currency contains commas, it will confuse anything that thinks it is separated by commas. If you open the CSV in a spreadsheet, is it confused? Can you use something other than a comma to separate the values? Perhaps bring the file into a spreadsheet, fix any problems, and then export with a different separator? I don't know if GnuCash will work on content separated by something other than a comma. -- _ Richard Losey rlo...@gmail.com Micah 6:8 ___ 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] Import question
On 02 September 2023 at 11:07, R Losey said: [...] > Hi. The issue may be with the CSV (which stands for COMMA separated > values)... if the currency contains commas, it will confuse anything that > thinks it is separated by commas. No. A field in a .CSV file that contains a comma will be enclosed in (single or double) quote marks, so the program reading it will not have any such problem. Otherwise it isn't a .CSV file but a text file that happens to have been given a .CSV filetype. ___ 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] Issues
Hi Adrien, I offered him several times, I would install it "correctly." No, he insists on doing it himself. He figures, if I can do it, then he can do it. But, thank you for giving him more ideas to try. We're both still working on a custom "Weekly Payroll Report" that I designed and created with 2.4.13, but it disappeared. It worked great! It was a fantastic template - all you had to do was change the date each week. I showed (taught) him about the style sheets. I also showed him about the accounts tab under options in the transaction reports. - Yes, Windows 10 will tab. However, I am talking about the separate account register tabs displayed within GNUcash, that you can display: top, bottom, left, or right. (Which gives him more options) His screen resolution is x1080, My screen resolution is 4K (3840x2160) with NVIDIA display. Does that matter? -- Greg On 8/29/2023 11:37 PM, Adrien Monteleone wrote: Greg, Glad to hear you are liking 5.3! I originally played with 2.4.x way back in the day, but never became a serious user until the midst of 2.6. The app has improved significantly since then, with several very 'productivity oriented' improvements. I look forward to the continued progress. I know the dev team is tiny, but quite dedicated, and all of them are insanely patient, understanding, courteous, and helpful to the rest of us mere mortals. - Now, on to the errant installation: I'm on a Mac, so I can't test this, but does Win10 have the ability to 'tab' separate windows as a function of the OS? (Mac does) If this is the case, by 'tabs' do you mean "within the gnucash interface" or do you mean "within the Windows OS"? The first case is separate tabs like in a web browser or spreadsheet. The second case is roughly similar, but the UI is a bit different and each 'tab' is really a separate window of the app. (the window title bar appears tabbed, or else a separate tab bar above/below it, but clearly different from what GnuCash provides out of the box otherwise) Of note, check "Preferences > Register Defaults > Other Defaults > Register opens in new window", and make sure it is *not* checked. (meaning you get 'GnuCash' tabs within the app - not separate windows, tabbed together or not) And if Win10 doesn't have this ability, consider having him play with checking that preference as a workaround. (he'll have to manage separate windows, but the ability to open more than 4 might be worth it, and managing them with the keyboard, mouse, or the Windows menu which shows a list of open windows of the app) There is also "Preferences > Reports > Location > Report opens in a new window", which will spawn a window instead of a tab for reports, which can help further testing. My next question would be, "Does the limitation of 5 total tabs also include reports, a mix of reports and accounts, or just accounts?" And then my only other question would be, "What is his screen resolution?" My recommendation regardless of that answer (it is just for my curiosity, as I know some really small screens might have issues - there is a really reasonable design minimum, 800px I think) is to please file a bug with all of the relevant details, especially anything that is unique to that laptop that isn't working properly. *Also Note - he shouldn't have to shorten account names. The built-in solution should be to substitute ellipses for longer names past the charcter preference limit. If that is not happening - definitely file a bug. *Last Gasp — does he by chance have a custom UI stylesheet on that machine? There are ways to directly alter UI elements via CSS, at least as of 2.6. I'm not sure if that was possible in 2.4. But if so, an old display rule might be at play causing havoc. See the Wiki about GTK customizations: https://wiki.gnucash.org/wiki/FAQ#Customizing_the_Appearance Regards, Adrien On 8/29/23 11:14 AM, Greg wrote: Hi Adrien, I called the other person last night, on the phone, and we played with Edit/Preferences/Windows/Width, changing the character spacing. He was elated that it "worked", not entirely. He had to shorten the name on the accounts. But it gives him some ideas to play with. I'll have to have him try to put the tabs vertically. How version 2.4.13 started acting flaky with Windows 10 on both our machines, suddenly, all the reports, just disappeared. Well, now we're using 5.3. At least with mine, everything seems to work fine. I'm very elated with version 5.3 on my machine. There are many new features that work GREAT! Now, to get 5.3 properly working on his machine. ___ 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. ___
