[GNC] Small business accounting importing transactions
David Cousens
davidcousens at bigpond.com
Sat Nov 30 15:31:15 EST 2019
Rob,
The basic idea is OK but you are not creating 2 additional transaction, but adding the additional splits to the
transaction recording the sale, or at least that is what you will need to be doing. A single transaction can have 2 or
more entries to different accounts referred to as splits in GnuCash documentation and that is what Stephen and I had
suggested. GnuCash has a multisplit multiline CSV format for importing Data like this. I am currently updating the
documentation on using this so you won't find it completely described in the Help manual or Guide yet although the Help
manual is the most recently updated.
The wiki https://wiki.gnucash.org/wiki/CSV_Import/Export has a very brief description of the multiline multisplit CSV
import process but it does describe the fields. I have attached a copy of a test file for a multiline-multisplit export
form GnuCash (Gnucash Export Format) which I am using to document the import process. It will give you the basic csv
format for the second and subsequent lines ofthe multisplit format. The wiki seems to suggest that the transactionID is
essential to import multisplit data but I have yet to test whether that is the case and just which fields I can leave
out and still manage to import the data in mutlisplit format. The wiki does have a list of the mandatory fields which
have to be present in the record. The transaction ID as AFAIK is a Globally Unique ID or UUID as described in this
wikipedia article (version 4, I think). Python has a UUID generator(https://docs.python.org/2/library/uuid.html) which
should be OK for creating them in the hex format used in the import. Hope this is enopugh to get you headed in the
right direction.
I had originally intended to get the documentation done earlier this year but a couple of overseas trips and a couple of
bouts of illness delayed my plans.
David Cousens
On Sat, 2019-11-30 at 12:01 -0500, Robert Slippey wrote:
> David and Stephen,
>
> I wanted to circle back to this again and reach out directly to get your thoughts on this. I was going through this
> issue again in my mind and admittedly I'm a bit lazy. I'm also learning Python slowly, so of course I put two and two
> together. Was curious your thoughts.
>
> I might have missed something here but here is how I envisioned the script working.
> The script takes each transaction and splits it up into the following:
>
> Income from your sales account
> Transfer to your bank account
> Expense to your expense account
>
> Basically, I've turned 1 line in the CSV into 3 per transaction.
>
> https://github.com/rwslippey/square_transaction_parser
>
>
> As previously discussed, this probably wouldn't help for multiple sales on a single day and refunds are probably a no
> go.
> I know this is for square and we were talking stripe.... I'm moving things off of square so everything is in square
> right now... I'll need to make this for stripe if you guys think it's right.
> Your thoughts?
>
> Thanks
> On Wed, Nov 27, 2019 at 6:20 PM David Cousens <davidcousens at bigpond.com> wrote:
> > Rob,
> >
> >
> >
> > Not sure of the details of how Stripe operates but presume it is somewhat
> >
> > similar to paypal.How you record it will depend upon the detail of the
> >
> > procedure they use. I treat my Paypal account as a Liability account as I
> >
> > can also make payments from it. In my case it is directly connected to a
> >
> > bank account and my Paypal account has direct debit/credit access to that
> >
> > bank account and unless i have a positive balance in my Paypal account it
> >
> > will debit funds from my bank account as required. It is so long since I
> >
> > sold anything using it I cannot remember if it automatically credits funds
> >
> > received. If I remember correctly I had to manually transfer funds as
> >
> > required. As the balance in a Paypal account always has to be >=0, I should
> >
> > really treat it as an asset but I set it up as a Liability and as the
> >
> > balance is at or near 0 most of the time, so I am happy keeping it that way.
> >
> > My transactions are all imported as OFX from my bank and CSV from Paypal.
> >
> >
> >
> > My bank records only have the amount of any debits and/or credits to the
> >
> > paypal account and hence have no information pertaining to fees and charges.
> >
> > I only receive this when I download a CSV statement from Paypal which
> >
> > details the amounts received and fees and charges. I keep separate Income
> >
> > sub accounts and Expense subaccounts for the Paypal transactions. A sale
> >
> > would be recorded in the Paypal account as follows:
> >
> >
> >
> > Liability :Paypal Dr zzzz
> >
> > Income:Paypal Sales Cr xxxx
> >
> > Expenses: Paypal Fees and Charges Dr yyy
> >
> >
> >
> > where xxxx=yyy+zzzz or you could consider this as two transactions
> >
> >
> >
> > Liability:Paypal Dr xxxx
> >
> > Income:Paypal Sales Cr xxxx
> >
> >
> >
> > and
> >
> >
> >
> > Expenses: Paypal Fees and Charges Dr yyy
> >
> > Liability:Paypal Cr
> >
> > yyy
> >
> >
> >
> > As the CSV record from Paypal usually contains the fees and sales as
> >
> > separate line item,s the second format is a better description of what is
> >
> > imported and recorded for me. The import matcher assigns credit entries to
> >
> > the Paypal account as Fees and Charges and Dr entries as Income (after
> >
> > initially manually setting these accounts during import and importing the
> >
> > data to train the matcher process). The matcher uses other tokens in the
> >
> > description to make this assignment and not the credit/debit status of the
> >
> > transaction AFAIK, and generally handles payments I might make from the
> >
> > account as well now although I often have to manually select the correct
> >
> > expense account. I preprocess the CSV file before import (Paypal supplies a
> >
> > lot of extraneous information I don't need but I now have a set of stored
> >
> > import settings which simply ignore the unwanted fields supplied by Paypal
> >
> > and just selects the relevant columns) and arrange it to import easily
> >
> > (usually only copying the foreign currency conversion info to the
> >
> > description field and deleting the currency conversion records supplied by
> >
> > Paypal. My Paypal account is in AUD and I sometimes have international sales
> >
> > in other currencies. I don't treat the foreign sales in a separate account
> >
> > for the other currencies but simply copy the currency conversion info into
> >
> > the Description field before import so I can locate the foreign currency
> >
> > amounts if I need to. I keep the raw downloaded files as well as any files
> >
> > modified for import just so i have a complete trail and can track any
> >
> > errors.
> >
> >
> >
> > The import matcher usually recognizes duplicate information in the bank
> >
> > record imports which match transfer transactions from the Paypal import (or
> >
> > vice versa depending on the import order) and flags them as matched
> >
> > duplicates not to be imported. I usually check that Gnucash has done this
> >
> > correctly before completing the import but once setup it usually gets
> >
> > things right.
> >
> >
> >
> > Can't tell you how to handle Stripe as I don't know how it works and what
> >
> > data it supplies but perhaps the above description may help you set it up.
> >
> >
> >
> > David Cousens
> >
> >
> >
> >
> >
> >
> >
> > -----
> >
> > David Cousens
> >
> > --
> >
> > Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-User-f1415819.html
> >
> > _______________________________________________
> >
> > gnucash-user mailing list
> >
> > gnucash-user at 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.
> >
--
Dr David R Cousens
B.Sc, M.Prof. Acc., Ph.D., G.C.Ed
-------------- next part --------------
A non-text attachment was scrubbed...
Name: CheckingAccount_Multisplit_MultiLine.csv
Type: text/csv
Size: 4039 bytes
Desc: not available
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20191201/8a067372/attachment.csv>
More information about the gnucash-user
mailing list