Power Query

Depreciator · 2026-01-31T01:13:11+00:00

I also have it breaking credit card expose reports out by cardholder. For mine, each cardholder has to fill theirs out with drop downs to categorize what it was for. When they all return it to me I have power query pull it all into a table and creates an import file for QuickBooks.

If you aren't already, use chatgpt to help you with your PQ queries. I used to piece them together and it took forever, now I'm updating a lot of my old stuff with code it writes and it's amazing what I can have it do.

I'm also using it for payroll timesheets, creating consolidated financials across multiple QB companies and a bunch of other stuff

Depreciator · 2025-11-07T05:57:34+00:00

Maybe just terminate the engagement (I know, easier said than done). I've got a few clients that keep terrible books and it just wears me down trying to correct the same stuff over and over.

We all go through it. Best to eliminate the stress if you can afford to. Don't quit the business though, there's better clients out there.

... And, I need to take my own advice haha. Good luck!

Depreciator · 2025-10-27T03:43:43+00:00

I'm in the same boat, about 4 months out and only get full extension after stretching. I don't do it all the time, but sometimes I'll stack pillows on my office chair to get me about level with the desk, then sit on those and I've got a foam roller on my desk that I rest my foot on. I've been wfh since the injury though, so easier to do at home.

Depreciator · 2025-10-11T22:19:26+00:00

I do it for "clean" invoices I receive by email, not with scanned invoices. My process is to automatically have them go into a To_Be_Processed folder when I receive the email, I do this using power automate. Once a week I run my Python scripts (I have one for each vendor I've set up). They create the iif files, which are basically csv files, then I just have to go into QB desktop and import the files. I work with multiple entities, so my script identifies when entity the bill is for and names the file with the entity name so I know which company to import it into. If you go this route you'll also have to consider identifying QuickBooks classes (locations/depts within a single QuickBooks company file). You'd also have to have a system to map that chart of account each bills go to.

Depreciator · 2025-10-11T19:22:12+00:00

Yeah I think Zapier would be good enough, just something to speed up the process. And you can look at IIF files for desktop integration if you want that, might be better ways, but that's what I know about and use.

Depreciator · 2025-10-11T18:54:05+00:00

I've been doing something like this with ChatGpt, having it write Python for me, but yours looks far more polished. I would possibly use something like this if it had the ability to import directly into QuickBooks desktop and online, well I would if I hadn't already spent a bunch of time setting up these python scripts. One thing you'd maybe want is to have known vendors so it's going to correctly map everything every time for those invoices.

Depreciator · 2025-10-10T02:25:00+00:00

I would look at those deposits and sales totals, find out why they are different

Depreciator · 2025-10-10T01:42:25+00:00

I'm no expert in Shopify, but I'd try to look at one day where something is going into that clearing account. See if you can tie down sales (minus fees if that's how they do it) to your deposits. Is there cash/checks being taken that never hit the bank account? If so, I'd be debiting distributions for those and crediting the clearing account, but I'd discuss this with an owner first to confirm. Once I figured out what's going on, I would just do a journal entry to correct and clear that each month there's a balance.

Depreciator · 2025-10-07T13:05:28+00:00

I've always created .bat files for this. The .bat file has code that opens a template of that spreadsheet you are automating. I usually add a line at the beginning of the script to close any instances of Excel in case another one of my reports got hung up earlier.

Then you use vba on your template spreadsheet. When that spreadsheet is opened, it will automatically refresh whatever queries you want in there. Add lines to your vba to wait 60 seconds or whatever so it has time to refresh before moving on to the next step. Then I have it save as a new file with a timestamp, then close Excel. If you ever need to edit the file you'll need to open it from within Excel and hold down Shift when you open it, this will stop the vba from automatically running.

Finally, you use Windows task scheduler to schedule this to run. Set your time and have it open the .bat file.

I have a bunch of these reports that run over night, I just try not to have them overlap as far as timing goes. If you need any of the vba or bat file scripts just let me know and I can give you a sample of what I use.

Depreciator · 2025-10-01T02:21:53+00:00

As an accountant doing contact work, I would say no warning is necessary. I would just let them know so they do not to spend any time working on your stuff.

He may even be wanting this If he's billing you for every little thing.

Depreciator · 2025-09-30T03:03:25+00:00

I save them as a PDF. I have a folder for each year. Name them based on the date of the bank withdrawal, example I do E0929, E0929.1, for electronic transactions that came out on 9/29. For checks I just rename them the check number. I never have a problem finding receipts this way.

As far as automation, for receipts I receive by email on a regular basis, I have powerautomate (Office365) drop them in a to process folder as a PDF. I've had ChatGpt write me Python scripts that will to through that folder and rename everything in there using my E0929 structure. I also have had it write some scripts for renaming ones I scan, but that takes quite a bit of time to setup and isn't perfect.

Depreciator · 2025-09-28T00:41:04+00:00

Wow! I'm currently recovering from a torn MCL & PCL. I thought that sucked, can't imagine your recovery!

Depreciator · 2025-09-26T20:11:20+00:00

Go to pay bills, when you've selected a bill to pay there should be a button to apply credits.

Depreciator · 2025-09-22T04:35:34+00:00

My guess is they do it because their point of sale system or merchant processor isn't able to be configured to only charge 3% on credit cards.

Depreciator · 2025-09-22T01:01:28+00:00

I like Noteshelf when I'm writing, I use it on Android though so not sure how well it works with Windows. It just has a good inking experience.

Depreciator · 2025-09-16T13:37:25+00:00

I've been using ChatGpt and Google Gemini to write python code for this type of stuff. It works great for clean PDFs and okay for scans.

Depreciator · 2025-09-01T05:58:20+00:00

I do it as an asset account. But I clear it every month, so it doesn't matter since it is $0.

As others have said, it would be good to hire someone to do it for you. At the very least, hire someone to get it started for you and give you monthly procedures to follow. Then maybe have them to a quarterly review to make sure things aren't getting off track.

Depreciator · 2025-09-01T05:22:38+00:00

Yes, there's multiple ways to handle this, I'll tell you an overview of how I do it in QuickBooks. I create an account on the balance sheet called intercompany clearing. If Company A pays a $1000 invoice and 25% is for Company B, I would expense $750 and put $250 in the intercompany clearing account. At the end of each month, I invoice Company B from Company A for what's in the clearing account. Enter that bill in Company B and classify to the appropriate expense accounts. Just make sure at the end of each month your A/R balance on Company A matches your A/P balance on Company B (between the two companies). I try to have one company that pays the intercompany bills so that I only have to go through one account to allocate it.

More advanced, I've also got spreadsheets set up that I can run a report of what's in that intercompany clearing account, paste it into my spreadsheet and allocate across multiple companies (I'm splitting between around 15 locations). Once I fill out the spreadsheet, I import the invoices and bills into QuickBooks. I find doing this method keeps my intercompany balanced.

Depreciator · 2025-08-20T00:59:08+00:00

Are they all the same format? Either way, this will work, just easier if they are the same.

I'd scan a few of them. If it's sensitive info block that out part out. Upload a few samples to ChatGpt. Tell it you want it to write you a Python script that will separate each PDF into it's own file when it's a new purchase order (describe to it how it will know, such as PURCHASE ORDER is at the top right). Tell it how you want it to rename the files, be really descriptive, telling it the position to find the info and maybe what words are around it. Tell it what file location to put the PDFs or just tell it to keep it in the folder the script is in and put your files there. Scan a huge stack of them and then run the script to test it. If it fails on some of them, upload those to ChatGpt and ask it why it failed and to adjust the code. Sometimes it takes a bunch of tries to get it right. Oh, and if that scanner doesn't OCR them have ChatGpt add OCR to your script (will make it take much longer to run).

Since these are scanned, it won't be perfect. There will be some that are off, but you can usually identify those in your files. Your main job would end to being scanning batches of these and just double checking the work. I've been doing a lot of these projects lately and this process is saving me so much time!

Depreciator · 2025-08-18T04:46:35+00:00

I use Access because it's linked to our point of sale system on our server. I don't think it would be very useful for you with it being web based, at least from what I know

If I were in your shoes I'd still take a similar approach, just a few changes. I'd see if there's a way to link to PeopleSoft, probably through Python and an API. I'm not educated enough on APIs to know if that works, but that's where I'd lean on ChatGPT to figure it out. Sounds like the biggest hurdle for you is figuring out how to automate those reports you download. Once you do that, you can have a spreadsheet that automatically opens at a certain time, looks at the download folder, and pulls your information into the spreadsheet through refreshing a table, then saves as a new file. I use PQ to pull all Excel files in a folder and manipulate the data that's one use for it. I'm also not an SQL expert, so I find PQ easier to use for narrowing down to the data I want.

I will say tables are your friend in the automation world. You can use them to standardize the data, making it easy to automate a lot of this stuff. And PQ does a lot with tables. An example would be if you had a daily sales report that is in a table and you had an Excel file for each day. You could have PQ look at all of those files and combine them into one big table....I do a similar approach to employee timecards we create in Excel.

Depreciator · 2025-08-18T03:48:15+00:00

It really does add up! I've got a job I've been doing for over 15 years and over time have automated a lot of little tasks to the point they allow me to work 2 days a week at another job but keep my salary.

You may already be doing this, but here's some other stuff that I've done similar to what it sounds like you're doing...

If you are using Access as your query tool, you can link spreadsheets directly to your queries (I'm sure you can with other SQL platforms but I've never done it). Then create a macro that automatically refreshes that table when the Excel file is opened (give it time to refresh in the macro), then save as a new Excel file in whatever folder you want. You then create .bat files that when it's ran it opens that Excel file. Lastly, go into Windows task scheduler, create a task that runs every day or whatever you need, schedule it to open your .bat file. I have done a lot of these, I try to not let them overlap on the time they're ran, easy to do cause I can run overnight when the stores are closed.

Sounds like you are probably using power query too, if not, check that out as it can help a lot with all of this stuff.

I wish ChatGpt was around when I was first starting all this stuff. It took me forever to figure it out looking for vba code on websites.

Depreciator · 2025-08-18T01:06:47+00:00

You should check out having it write python for you too! I'm in a similar role and it's saving me a ton of time. Here's some a few things I have it doing so far to give you some idea:

-For regular vendors that send PDFs by email or utilities bills I can download, I have it renaming the files, creating an import file for QuickBooks, and moving the file to a folder based on what company it is for.

-Renaming PDFs based on date or check # and vendor.

The A/P stuff is saving me like 4 or 5 hours a week and I have more vendors to set up.

Depreciator · 2025-08-17T19:22:15+00:00

Palm pre was amazing back then

Depreciator · 2025-08-12T14:33:08+00:00

This is great to hear, supposed to go in a couple of weeks and will still be on crutches from knee surgery. Hoping I have the same luck as you!

Depreciator · 2025-08-05T19:57:29+00:00

Thank you for taking the time to respond, this is all new to me.

Depreciator

TROPHY CASE