Create Optimal Specs for Building a High-Volume Excel, Data Processing, VBA, and Data Analysis Application?
Create Optimal Specs for Building a High-Volume Excel, Data Processing, VBA, and Data Analysis Application?
My current setup is a Frankenstein configuration I assembled from various workstations, and it stops working whenever I try to process monthly data. The specifications are decent—an i7 7700 and 24GB of DDR4 RAM—but I could easily grab more from older machines.
Recently, the company switched to surface devices and docking stations a few years back, and they don’t seem to grasp why a data specialist needs a permanent desktop with over 8GB of RAM. I need a solid reason to justify their spending on me.
Worse still, the systems are built around very outdated proprietary software (Windows 98/XP), which only outputs messy Excel files. My VBA macros often take about 30 minutes per report to clean and format the data into something usable, which is really slowing things down. I try to use Power Query to organize the data, but even simple copy-paste operations break the system.
I’ve been asked to prepare a parts list for a new setup, but I’m unsure where to start. I could replace my midrange CPU and chipset with something modern, but I want the best value long-term, especially for handling large yearly datasets.
A GPU isn’t a problem yet, so I might stick with an integrated or low-power card, but the situation here is confusing—so I’m open to a basic 40-series processor.
My main needs are:
- Improved single-thread performance
- Data often stored as text in huge numbers, forcing it into single-thread tasks line by line
- Excel’s single-thread limitation when using Power Query, causing slowdowns
- Ability to refresh data without losing functionality
- Strong multi-core support for Power Query and VBA macros
- Capacity to handle millions of lines in monthly reports
- Budget is manageable; the real challenge is getting the most value from what’s available.
I built this around four months ago for our CAD team at work.
https://pcpartpicker.com/list/LyMwfy
I chose a Corsair PSU and a Quadro A4500 GPU, besides that the systems have performed very well.
Visually, you just need a potato to generate a signal; the Intel Arc still stands out for its value, or you can skip it and rely on the integrated graphics built into the CPU.
I recommend choosing an Intel i5-14600K or i7-14700K. The only review I know about that checks Microsoft office is on their site, but it seems pretty general and you'd just have to trust their assessment. Instead of making it yourself, if your organization has an IT team, consult them about the best option. Unless your company is small—under 10 people—and you handle all technical support yourself, I wouldn't suggest building a custom machine for them.
The company IT operates entirely through an MSP that handles remote access and management, while my current setup is a custom-built workstation I upgraded myself. This should be fine for IT since it integrates well with their management tools. The IT firm doesn’t manage system purchases; I need to approach the CEO directly. The CEO chose Surface Pros during the pandemic due to better deals with Microsoft, possibly limiting options to prebuilt OEM models. I plan to insist on a custom-built unit, even if it means hiring a local shop for warranty coverage.
I envision pairing either of those CPUs with an X/Z series chipset and a solid 32GB RAM. For reliability, the RAM controller should be robust, and I’d prefer a B-series CPU instead.
I built this around four months ago for our CAD team at work.
https://pcpartpicker.com/list/LyMwfy
I chose a Corsair PSU and a Quadro A4500 GPU, besides that the systems have performed very well.
Visually, you only need a potato to generate a signal; the Intel Arc still stands out for its value, or you can skip it and rely on the integrated graphics built into the CPU.
This looks like the optimal path, I only need integrated for data tasks and could likely have a systems builder handle it, ensuring a warranty to keep me satisfied.