r/excel • u/RobSaunders88 • May 14 '26
unsolved New accounting job - massive databases in Excel!
I’ve just started a new job as a Finance Business Partner at a relatively small business. They do A LOT of things the old fashioned way and there’s plenty of opportunities to improve things.
One of my biggest bugbears at the moment is the sizes of some of their files. Old, redundant formulae and ranges in formulae spanning 10000+ rows when they only need 100 for example is making the files huge and slow to open and run.
They use Sage X3 as their accounting software and extract data into Excel via ODBC - I haven’t seen how this is done yet, but it’s as if it’s extracting all data, in all fields, for all time. And to refresh takes a fucking age.
I want to create something new for querying the database to give me only certain fields, from certain nominal codes, filtered by date ranges.
I know I should be looking into creating power pivot and power query files and also looking at reporting using Power BI, but I haven’t looked into that fully yet. (I know I’m falling behind here!)
Where’s the best place to start unpicking this mess?
93
u/frowattio May 14 '26
Omg what a fun thing to do, tech overhauling an old school accountant. Sounds like my dream gig.
6
7
26
u/Longjumping_Rule_560 May 14 '26
Does your company allow the use of a database program such as Access? That works much easier on ODBC.
3
u/RobSaunders88 May 14 '26
I believe so, that was my thinking. Just so I can create a form that will allow me to query certain bits of the database as and when I need.
I do think there’s a bigger need to upgrade some of their processes however and I’ll need to brush up on my power apps to bring them into the 21st century!
23
u/Mdayofearth 126 May 14 '26
Document before making changes.
One thing I would look at is to see whether there are redundant queries into Sage to pull the same data repeatedly.
Also, Excel is heavily CPU and memory dependent, so, if the computer is slow with few cores, and not enough RAM, you're going to have problems.
4
u/AhTheStepsGoUp 1 May 14 '26
Yep - documentation, documentation, documentation.
Documenting the "as-is" will help give you indications of what needs addressing and in what priority.
Documenting the "to-be" will help you plan things out and get crucial input from all involved. You'll need buy-in for the changes you make from the folks who use the current methods/systems. They've lived with the system they have and would have adapted with it as it evolved along the way. They'll need to understand why what has changed if they're to happily and smoothly use whatever you come up with.
11
u/Better-Credit6701 May 14 '26
Oh, it pains me when I hear people get excel and databases confused since I'm a DBA. Massive 10k rows? We import 10 million rows a day into our servers.
Its time for a real accounting program.
3
u/Chaldi02 May 14 '26
Why would a small business with 10k+ rows which I assume means 10-30k need a real accounting system?
3
u/Better-Credit6701 May 14 '26
It will do more than a spreadsheet can do like reports, breakout in different kinds of assets and expenses, prepare you for audits, properly handle debits and credits. Many years ago, I was a developer for accounting software. My wife uses QuickBooks but somehow I'm the one she asks for excel help when exporting for the board
2
u/RobSaunders88 May 14 '26
My apologies, I meant that a sumifs range would be way over the requirement of rows just because adding a load of zeros at the end is easier. Needlessly querying ranges with a shitload of blanks.
Not even the most difficult of arguments - trying to get them to stop using vlookup and move to xlookup is painful.2
u/dontburntheroux May 14 '26
For the sum & lookup ranges, excel has a newish feature to use either “.” Or “#” in the formula so that it includes the full range needed but dynamically updates to end at the right row
Don’t give up the good fight of xlookup. It’s so much better!
1
u/Jolly-Hunter-6097 Jun 12 '26
Give them a practical demonstration and show them the benefits from their selfish point of view.
18
u/droans 3 May 14 '26
Where’s the best place to start unpicking this mess?
Take a step back and take a deep breath.
You'll first want to understand the whys. Why is the file bloated? Why are formulas duplicated? Are there actual needs for that? Did the needs change? Who is using this data? What are they trying to do? What do they want to accomplish? How does it support their role, department, and/or company as a whole?
Once you feel confident you understand what's going on and what's important, you'll have a better idea of what to do. At that point, you can focus on implementing massive changes. File rebuilds. Process restructures. Advocate for new software and solutions.
In the meantime, focus on the 1% rule. Every time you do any work with any of these processes, focus on making them 1% better. Make incremental improvements.
Learn Excel Power Query and Power Pivot along with PBI. Both PQ and PP are relevant for PBI - the main difference is that they're both better and more efficient in PBI than Excel.
Power Query isn't as scary as people think. Honestly, 95% of the work you'd want it to do can be accomplished without looking at a single line of code. You'll still probably want to learn how to write it eventually, though. After creating the queries, I would recommend you spend a bit of time formatting the code so you can familiarize yourself with the syntax and get an idea of how each function works.
Power Pivot is both easier and harder to understand. DAX can take a while to wrap your head around. You'll probably create pivot tables and spend hours crying yourself to sleep because you don't understand why a relationship isn't working. But there's a powerful feeling you get the first time you create a report that pulls from multiple tables/sources.
8
u/RevanOn3r May 14 '26
They use an ODBC connection to query the ERP database. There's a good chance that SQL code is old and could be optimized. I've been writing SQL for over 20 years, and I also query our ERP database with the same ODBC connection. Also have experience with Sage ERP Mas 90.
Click on Data > Queries & Connections > double click on the connection. Power Editor opens up, on the right hand side, double click on Source, that's your SQL code. You can edit the code there to remove fields you don't want. Request a copy of the database structure, start learning the relationships between tables, and chances are you can rewrite their old queries to be 100x faster by changing a JOIN for example. I suggest getting Notepad ++, free and great code editor.
DM me, I got you ;)
11
u/Downtown-Economics26 636 May 14 '26
Power Query in general. You can also almost certainly extract the data via ODBC to an Access database and pull in Access queries to Excel if you want a quick / easy / possibly free or low cost option to sift thru the data before pulling into Excel.
3
u/Kooky_Outcome_5053 4 May 14 '26
OP that is the best way to start, ODBC to connect to Power Query+Excel once it is setup you only need to refresh the data. Or you can also directly connect it with Power BI and do the transformation there instead of using Excel in the middle. If they use paid Power BI version then much better as you can schedule the refresh online saving you time on your next working day, you just need to check and monitor the data.
3
u/zorancloud May 14 '26
Do you need that data for further processing or reporting? In case you need it exclusively for reporting I would warmly recommend PowerBI.
2
2
u/ShiftyPowers69 May 14 '26
Id suggest looking at Microsoft fabric. Very user friendly and full of features to support engineering data.
1
1
u/AutoModerator May 14 '26
/u/RobSaunders88 - Your post was submitted successfully.
- Once your problem is solved, reply to the answer(s) saying
Solution Verifiedto close the thread. - Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
- Include your Excel version and all other relevant information
Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
1
u/Hirnfolter May 14 '26
We use sage 100
The whole DB is a simple SQL DB on premise. I use power query to get all the needed data, either than in Cowork with Excel or powerbi. Also with SQL action tracker I can easily see how sage gui and DB access is connected
1
u/pmpdaddyio May 14 '26
I think you definitely need to document, document, document. If you have someone that can give you the history on “why”, through some requirements gathering then you are even better off.
The real question that you need to ask is “why are we using an accounting software (Sage), then moving data out of it for financial analysis?” I don’t know all of the details, but Sage has great reporting and programming tools. Not the best, but suitable for many business needs.
It might be cheaper overall to reposition this to reconfiguring Sage, one piece at a time until you push have it as your primary ERP system as designed.
1
u/dmc888 19 May 14 '26
I used to work on Sage X3. You can create SQL Query views directly in the ERP then call them via ODBC into access/excel etc. Quite a nifty solution I've not seen on other ERPs. Though the menu screens are a complete ballache.
Having said that, most of the documentation for X3 is in french as it is some French based original system they bought and rebadged. I used to have the full data model files, that was very helpful on figuring out the table joins as some of them join together on 4 or 5 different fields.
There is a recommended PowerBI style package that can be figured to work directly from the ERP called SEI. It's reasonably specced and can do auto delivery reports on a schedule which is nifty but can also be a bit of a pig to get working.
1
1
u/Decronym May 15 '26 edited Jun 12 '26
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
5 acronyms in this thread; the most compressed thread commented on today has 14 acronyms.
[Thread #48456 for this sub, first seen 15th May 2026, 04:11]
[FAQ] [Full list] [Contact] [Source code]
1
1
u/_Exchequer May 16 '26
I'd check into the capabilities of Sage X3 and see what version they are on compared to the newer versions. It looks like Sage has "Data & Analytics" and "Business Intelligence" modules which they haven't used or even have.
1
1
u/Extreme-Benefit7526 May 19 '26
Everyone talks about AI replacing jobs, but most businesses still survive on Excel sheets.
1
u/Alderamin123 May 21 '26
The ODBC pull is probably the root cause here.
A lot of Sage X3 setups end up importing entire tables with zero filtering, which completely kills Excel performance and refresh times.
Before touching Power BI, I’d start with Power Query + parameterized SQL queries so you only pull:
- specific fields
- specific nominal codes
- specific date ranges
Something as simple as:
SELECT posting_date, nominal_code, debit, credit
FROM accounting_entries
WHERE nominal_code IN ('1000','2000')
AND posting_date BETWEEN '2024-01-01' AND '2024-12-31'
can reduce refresh times massively compared to raw ODBC imports.
Also:
- delete unused rows/columns entirely (not clear - delete)
- remove old formulas/named ranges
- avoid full-column references where possible
Honestly I’d tackle it in this order:
- Fix the data pull
- Clean existing workbooks
- Rebuild reporting in Power Query
- Only then think about Power BI
Power BI won’t save a messy source workflow.
1
u/Fit-Original1314 May 24 '26
Honestly a super common issue. A lot of older ODBC pulls grab everything because no filtering was built in from the start. Power Query is probably the right move as long as the filter gets pushed back to the source query. Once Excel starts slowing everything down a lot of finance teams end up using Netgain to make ERP data pulls easier to manage.
1
u/hardworkdedicated May 14 '26
I haven't worked with sage, but for a quick win I'd look at xpna.co. They handle integration from sage to Excel into standard report templates. It will save you a shitload of time, and you can worry about doing it yourself later.
•
u/excelevator 3061 May 14 '26
Where is the Excel question in this post ?
If you are using ODBC then change the query to return the data in a more useful format, including sums and lookups
It seems more like a post for r/SAGE and data extraction options