My 10 Favorite Apps - Week 7: CData Excel Add-in

by

As part of my mission to create synergy and utilize time saving technology the CDATA QBO Excel Add-In is this week’s App favorite. CDATA's QBO Excel Add-In allows you to connect to live data from QuickBooks Online with full read/write capability. You might have used other CDATA utilities because there are a lot of them including ones for QuickBooks Online and QuickBooks Desktop versions.

Please remember, my 10 favorite Apps are in no particular order so this is not a racking or hierarchy, just a list of the Apps our firm LOVES!

Because CDATA extracts live QBO data into Excel you can easily EDIT, DELETE or ADD data. CDATA is super handy when performing batch edits to QBO!

Getting Connected with CDATA

CDATA is an annual subscription which lives on your hard drive and can connect to an unlimited number of QBO files. 

1) Connect CDATA through the QBOA Apps tab

2) Search for CDATA and choose “Learn More” to launch website

3) Once CDATA website is launched choose either to Download app for the free trial or Buy Now

After CDATA has downloaded open Excel

1) Select the CDATA tab

2) To connect a QBO file select “From QuickBooks Online”

3) Add a Connection name (I recommend the QBO file name)

4) “Connect” and “Authorize” CDATA

Table or View

1) After Connecting to QBO

2) Select which Table or View form the QBO table drop down

3) Refresh list

4) Increase Max Rows if more rows than 100 are needed

5) Click “OK” – that’s it a table is extracted from QBO then you can Update, Delete or Insert date

Because CDATA can extract most tables from your live QBO data there are endless uses for making batch edits. In my experience you do need to have an understanding of the basic inner workings of QuickBooks in terms of tables and table design, then you can play around with CDATA to locate the table with the data you need. But you should also know that both CDATA and QBO have great support articles.  

Example of How CDATA saved me a ton of time and headache.

A new client called and asked for help catching up bank deposits in QBO. Turns out since 2002 invoices had been created, received payments and were sitting in undeposited funds – over 9000 invoices totaling more than 9 million dollars!  Here is how I used CDATA:

1) I extracted the Payments Table from QBO

2) Then, I elected to view only the 2002 and 2016 Payments

3) Next, I selected only the Undeposited Funds Payments. (If you don’t know the numerical value of your QBO file’s Undeposited Funds you can find it by going to the Chart-of-Accounts, then opening the register and looking for the number listed in the URL.)

4) Then, I changed the deposit to account to a clearing account using the QBO numerical number (You can find the numerical value of the account by opening the QBO check register and viewing the URL number).

5) Lastly, I selected “Update Rows” to push the updates to the live QBO file.

That’s it! In my case it took some time since I had 9173 transactions which equaled 9 million dollars! The big win is I was able to push the Update and then do other work while CDATA was processing my changes to QBO.

My end results were great! I kept the customer transaction history but was able to remove the transactions from old years and from the bank deposit screen allowing for client training so they could move forward properly. 

Other Examples of CDATA  

My example of CDATA was used to clear out Undeposited Funds but I have also used CDATA to move deposited funds into Undeposited Funds. This works great if you have a client who receives payments directly to a bank account instead of Undeposited Funds where the received payments can be batched to match the bank account.   

I just love how much time is saved by using data utilities like DATA!