eQualityTime / Public

1 stars 0 forks source link

Import all Bank Transactions into Quickbooks #61

Closed joereddington closed 5 years ago

joereddington commented 5 years ago

Background

Comic Relief funded us to get a review done of our processes. That was done in #12 and one of the recommendations was that we start using a system like (but we could have used others) Quickbooks.

I've setup an account with Quickbooks Online and I need to move the set of transactions there before I can carry out the rest of the recommendations in the report.

Resources

Plan

The System

So here's the system I went thought.

First I connected Unity to Quickbooks, which gave quickbooks access to the last 90 days of unity tranactsion. I downloaded each statement from unity seperately and imported them until Quickbooks had ever tranaction - all untouched by human hand. I've since been thought and attached them to the correct donors (in quickbooks, anyone who pays you is a donor, even if they would more properly be called a customer).

As a result QB looks like this: screenshot 2018-12-05 10 41 20

...which is nice.

Unfortauntely many of the ringfenced projects have their roots in transactons made when we were using Natwest, and when we closed the account we lost the ability to import transactions.

So that we are on a firm footing, I've added all the Natwest Transactions by hand. After each entry I checked it with the statments in the Bank statement folder to check the balance (I've added a bunch of intermediate reports so you can compare for yourselfes. After each transaction was added, I added the folio numbers and cheque numbers from our old accounting documents, and, where possible, attached the relevent claim form files.

for future I haven't done any checking on the folio or cheque numbers, so there are likely to be some missing. Regardless, we should now have every transaction that equality time has ever made in QB, and we have a much better ability to see what has been spent on what.

There is a lot of work left to do. For example, one of the major actions to do go through each project and check that each transaction has the right code within that project - so that we know if we've spent the budget on staff or on cake.

joereddington commented 5 years ago

Starting setup.

I've used joe@equalitytime.co.uk as the Login and there is a strong password in lastpass.

screenshot 2018-11-22 11 15 13

I've tried to create the report I want, but couldn't make it work. Ringing Support.

joereddington commented 5 years ago

I attempted to import expenditure by CSV file: I've got a file called 'sample created from cashbook' that I created from eQuality Time cash book ye 2018.xlsx by removing all the zero amounts and the corrections (I think they should be there, but I think those aren't expentirue) and I'm going to upload it.

Unfortunately that imported bills rather than expenditure - I rang support and they helped me delete them. I've also come to the conclusion that I should work from the bank statements rather as the prime source in case there are any errors in the spreadsheets - working from the bank statements means everything gets double checked.

joereddington commented 5 years ago

I've imported everything from Unity by downloading the statements and then uploading them again. Did need to call the helpline occasionally.

We should think about what to do about Natwest. I could try and import everything by hand. That would be... interesting, but potentially doable in some sense. I could certainly do the incomes and then work through the rest of the folios.

Here's the THING. There CAN'T be more than 200 folios, because I've done about a hundred and we're only on about 300. So it must be possible to do a direct import.

But that can wait for the moment. The next action is to let the Trustees know how far we've got. Tomorrow I've got a report to write.

joereddington commented 5 years ago

05/12/18 09:44,

Okay, doing Natwest today. What's the plan?

10:00 to 17:21, pretty much constant with the occasional bit of work.

screenshot 2018-12-05 10 25 23

Wait, first of all I need to work out why the banking is different... hmm.

After a lot of faffing I rang the helpdesk: 2018-12-05 11 38 05

The nice person on the other end helped with a bunch of problems, and helped me understand the underlying mental model more, which was good. Also one of our problems (that the bank balance is wrong in the chart of accounts) turns out to be beyond her and the 'Tier 2' team that she could ring. It's been escalated somewhere and the error number is this: 52 87 11 30 4

Once that was done I spent a bunch more hours working thought each bit of Natwest in turn - I'm up to 9/9/16

joereddington commented 5 years ago

06/12/18 11:58,

Today's Sprint.

screenshot 2018-12-06 12 18 41

There are more details of the entries on the top of this issue.

12:19,

Okay, we are now on Folio 28.

12:34,

Done 39 (althought it's the first)

12:52,

Ticked off another couple, and have now reached my first major test - I've got the 'return unspent funds to A4A' entry, and I want to make sure that it matches up properly and zeros out the account.

14:26,

Have reached 3rd Feb 2017. Can't be that many left. We closed the account in about December 2017.

06/12/18 16:58,

Shuting down for the day at 28/07/2017. Hmm. That it pretty close to the end... ah, no. It turns out we were using the Natwest account up to about September 2018. Another year to go...

joereddington commented 5 years ago

07/12/18 21:16,

Another solid day and we are getting somewhere. Next actions are:

joereddington commented 5 years ago

12/12/18 09:34,

Boom! screenshot 2018-12-12 09 34 00

Actions from now:

11:35,

Okay, now to do the expenditure by donor... :)

12:04,

Hmm. I've run into the same problem I had before. How do I show ring fenced money?

If I use donor, then I have two problems:

If I try and use accounts, then I have the problem that it makes money leave the main bank account when it hasn't.

If I use sub accounts then I have a proble with projects that existed accross two different bank accounts

If I use classes, then it's unclear if I can show the subdivided ring fencing.

12:50,

Calling now.