Exporting payments to a Quickbooks compatible format

Handling payments and adjustments, running reports, doing your accounting with Moneylender.
Post Reply
mark415
Posts: 26
Joined: Tue Sep 28, 2021 10:17 pm

Exporting payments to a Quickbooks compatible format

Post by mark415 »

Hi,

I would like to be able to export my payments into a format that can be imported into Quickbooks as general journal entries (like "IIF"). Quickbooks will not import journal entries as CSV files.

I need to do this so I can reconclie payments logged in Moneylender as received from the borrower with payments actually deposited into the lender's checking account.

Without this reconciliation payments could be accidentally deposited but not logged in Moneylender, or payments could be credited to the borrower in Moneylender, but never actually deposited.

How can I do that? Or, alternatively, how are other people doing their reconciliations. I do not want to enter each check twice (once in quickbooks and once in Moneylender).

Thanks,
Mark
wtech_josh
Site Admin
Posts: 105
Joined: Tue Jul 30, 2019 7:17 pm

Re: Exporting payments to a Quickbooks compatible format

Post by wtech_josh »

Hi Mark,

From what I understand, other people are exporting the Payment Reconciliation report, and then importing into QuickBooks to prevent duplicate entry.

If you can't import a CSV file directly, maybe the "Excel and CSV Import kit" from Intuit will make that possible? https://quickbooks.intuit.com/learn-sup ... /00/201366

All reports in Moneylender have customizable columns, so you can edit the payment reconciliation report (or a copy of that report) to have exactly the information you want to import into QuickBooks. Then you record your payments in Moneylender, and have Moneylender spit out the payment list that you import into QuickBooks.

Let me know if you try the CSV kit and it works or not. I'm curious how all that works out. Thanks!
Josh
mark415
Posts: 26
Joined: Tue Sep 28, 2021 10:17 pm

Re: Exporting payments to a Quickbooks compatible format

Post by mark415 »

Hi Josh,

I had already visited that link prior to posting and I downloaded the toolkit, which is just PDF instructons and sample files. I worked through the process outlined in the toolkit and it's apparent that imporing general journal entries (or other transactional data) from CSV or XLS into Quickbooks Desktop is not possible.

That point is also made in the pdf guide in the toolkit:
"Import from Excel and Comma Separated Values (CSV) can only import lists. Transactions cannot be imported using this method."

There are some YouTube videos that instruct on how to use Excel to convert a CSV file to IIF format. An IIF file can import transactional data, so that is one possible solution. But it would be my preference not to have to build such a spreadsheet then take that intermediate step each time I export the payment reconciliation report.

If any users have found other solutions I'd be very curious.

Thanks,
Mark
wtech_josh
Site Admin
Posts: 105
Joined: Tue Jul 30, 2019 7:17 pm

Re: Exporting payments to a Quickbooks compatible format

Post by wtech_josh »

Nobody's told me specifics of what they're clicking in QuickBooks to import their data, but people definitely made it sound like it was standard operating procedure.

I looked through the lists you can import from CSV, and among the options were "checks" and "payments". If you import a list of checks or payments, does it not produce the corresponding transactions? What would be the point of importing a list of payments if those payments didn't have any effect on the accounting that QuickBooks is doing?

What happens if you try to import the Payment Reconciliation report as a list of checks or payments?
mark415
Posts: 26
Joined: Tue Sep 28, 2021 10:17 pm

Re: Exporting payments to a Quickbooks compatible format

Post by mark415 »

Hi Josh,

I confirmed that it is not possible to import any type of transactions , such as checks or payments, into QB desktop using CSV or XLS files. It is only possible with IIF or QBJ files. CSV and XLS files are only for Quickbooks "lists".

I tried a piece of software called GJE https://www.propersoft.net/products/gen ... al-entries which converts CSV files to QBJ files.

There are some insurmountable problems problem with that software though:
1. The CSV file (as exported from Moneylender) needs to have two rows for each payment - a debit row and a credit row. The Moneylender report generator appears to only generate one row per payment. 2. The software costs $20/month or $150 for a lifetime.

I am very curious how any users are getting their payment data into QB or what alternateive solutions they are using to reconcile their checking accounts with payments received.

Thanks,
Mark
wtech_josh
Site Admin
Posts: 105
Joined: Tue Jul 30, 2019 7:17 pm

Re: Exporting payments to a Quickbooks compatible format

Post by wtech_josh »

Perhaps everyone else is using QuickBooks online.

I looked at the IIF example file for a payment. Doesn't seem like it's the end of the world to build a quick export of payments in the IIF format. The real time-consuming part of that is adding the UI where you specify the date range for the payments to export. It'll probably be something I decide to burn a day adding to Moneylender.

I'm in the middle of building a Moneylender API for another customer (sponsored addition), and I have a bunch of other things that are already on the schedule to build. If there's a day in there where I get burned out on the difficult stuff, I'll see if I can work in an export payments to IIF option for you.

I made a note to post back here when I add that to Moneylender. It probably won't be too soon, though. Some upheaval with the bank for my AutoPay service is finally starting to settle, which means I have a figurative mountain of code to write for that whole system. Moneylender Server and a Portfolio Hosting system are also slated for the immediate future. And there's already a handful of other stuff people wanted me to squeeze into the gaps for free. The to-do list is about 150 items long.

In the meantime, you could go the other direction - post in QB and export the payments to Excel spreadsheet and then import the payments into Moneylender. ??
mark415
Posts: 26
Joined: Tue Sep 28, 2021 10:17 pm

Re: Exporting payments to a Quickbooks compatible format

Post by mark415 »

Hi Josh,

I just typed a 500 word reply that got chewed up and spit out by the forum software. That's the first time that's happened to me in like 5 years so this is the Cliff's notes version of how I solved this.

I export 4 different reports as CSV's then combine them into one CSV then convert them to a QBJ in "General Journal Entries by Propersoft". I'll need to do this for four lenders. So every month I have to run 4 reports 4 times and combine them into 4 CSV's.

Additionally building the reports is problematic because there deosn't appear to be an easy way to have a fixed record value. My workaround for that was to find disused data fields in Moneylender (like "Lender Mailing City") and put my fixed data there.

So I have two hopefully easy fature requests:
1. Create the option for a fixed record value in custom reports
2. Either offer the ability to group reports so they run sequentially and build just one output screen/csv or else offer the option to append a csv to an exisitng csv if you try to export a csv using the same name as an already existing csv.

Thanks,
Mark
wtech_josh
Site Admin
Posts: 105
Joined: Tue Jul 30, 2019 7:17 pm

Re: Exporting payments to a Quickbooks compatible format

Post by wtech_josh »

Hey Mark,

That sounds like quite the rigmarole.

#1 already exists. You can add custom fields to Loans, Payments, Borrowers, and Lenders from the Portfolio > Portfolio Settings > Custom tab. https://www.moneylenderprofessional.com ... ields.aspx

Once you create custom fields, you'll see them on the Settings tab > Loan Settings window, Edit Payment window, and at the end of the Borrower and Lender Wizards. You can add custom fields as columns in the main window, columns on reports, and embed them in statements.


#2 is probably not feasible, but there might be a simpler way to create the file for Propersoft to chew on. You can select and copy the contents of the report viewer to paste it into Excel or an email or wherever. You might open a blank worksheet in Excel, copy-paste the report data straight from the viewer into the worksheet and then save it as a CSV. That might be a little easier than exporting each report to CSV and then merging in Notepad or something.


But your questions make me wonder if you're trying to get all the individual transactions - interest calculations, fees, etc. - out of Moneylender and into QuickBooks. There are two conflicting needs that really compound the difficulty of making QuickBooks understand your loans successfully.

1. An accountant needs to have all the transactions be fixed over time to reliably balance the books periodically.
2. A lender needs the flexibility to accommodate a borrower under a variety of circumstances.

Often, someone using Moneylender will fill both of these roles.

Late fees that the accountant wants to see set in stone are waived retroactively by the lender upon notice of medical or financial hardship. A payment from the borrower was missed by the lender a few months ago, and the lender must credit the payment which has a cascading effect on all the interest calculations thereafter. There are lots of situations where you'll want the flexibility to actually manage your loans without the Accountant's Handcuffs.


My general advice has been to do a monthly or periodic report that gives you all the numbers for interest, fees, escrow, and principal activity for the period, and put that into QuickBooks. Maybe four or eight transactions a month that reflect the aggregate activity from Moneylender. But don't try to make QuickBooks mirror each individual loan. That's what you have Moneylender for - to accurately manage the loan balances. Perhaps with one or two loans it wouldn't be a nightmare. But Moneylender users commonly have 20 to 300 loans (and most are growing that number). Keeping QuickBooks in sync with Moneylender will become increasingly painful, and situations will arise where you'll be chasing your tail to fix something that Moneylender has automatically accommodated but QuickBooks doesn't understand. Use Moneylender to manage the individual loans. Use QuickBooks to do the aggregate accounting for your lending activities overall and to balance the bank account - your net profit, loans out, and payments in.


One of the features that I'm planning to add sooner than later is an accounting system that records the status of the portfolio on a set interval - monthly, quarterly, or annually. The system will balance the current state of the portfolio against the previous, with a pair of values - current period activity and retroactive edits that affect numbers from previous intervals. The current period numbers will match the payments and disbursements accurately, and the retroactive adjustments will give you the adjustment amount to reconcile with the info from last month. It'll provide a rock solid set of numbers that will balance against previous numbers as well as with the payments received and the principal disbursed. This accounting system is roughly fourth in line on my list of what I want to build.
mark415
Posts: 26
Joined: Tue Sep 28, 2021 10:17 pm

Re: Exporting payments to a Quickbooks compatible format

Post by mark415 »

Hi Josh,

All very sage advice. Thank you. I really love Quickbooks (crazy, right?, cause it kind of sucks in many ways). But it forces me to keep my bookkeeping in line with "best practices". That said, I believe in my heart that it's time to "let it go". I'm just having a hard time wrapping my head around the way my acocunting system can change for he better yet still retain the "solidity" of Quickbooks.

Your latest reply really helps me to understand, especially the talk of that upcoming periodic reconciliation report.

I'm going to take the dive and stop worrying about getting evertyhing into QB.

I'm thinking I'll shift all my QB loan assets into one big QB asset acount (no longer individual loans). Then my only "reconciliaiton" with QB (and my actual checking account) will be to export the MoneyLender payments report to QB as a general journal entry that debits checking and credits the newly created general loan asset account.

QB will no longer provde accurate P&L and Balance sheets for lending activities. But it will still track cash flows.

Whew. Thanks for all your help.

Mark
wtech_josh
Site Admin
Posts: 105
Joined: Tue Jul 30, 2019 7:17 pm

Re: Exporting payments to a Quickbooks compatible format

Post by wtech_josh »

That sounds like the perfect blend. QuickBooks knows your bank account to the penny, and ensures every real dollar and cent in and out is accounted for; and Moneylender keeps your loan balances accurate and tells you how much profit you're making (which you pop into QuickBooks from time to time).
Post Reply