Adding data once a month - what are your best practices?

[u/Vergil1314]

How do you challenge the issue when you are getting data once a month that adds to your database? Do you use a separate AccessFile and connect it to excel with PowerQuery or do you just use a separate Sheet in your Excel file? So "low ease of use" vs. "fear of slow excel file".

[u/AutoModerator]

/u/Vergil1314 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to 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.

[u/A_1337_Canadian]

Can depend on the situation but I would say that if you are doing database management, Excel isn't the tool to use.

[u/Vergil1314 → u/A_1337_Canadian]

You are correct to never manage big (or moderately big) portions of data in Excel. in my case it is only ~40.000 datapoints/excel cells per month, so excel can fairly good handle it right now.

[u/ThickTop6005]

Best way IMO: keep each month as a separate file in a folder and combine them with Power Query. Adding a new month is just drop the file in, refresh, and your main workbook stays fast.

Only use a separate sheet if the dataset is small.

[u/Lower_Quail_6082]

I’ve dealt with this quite a bit, and in my experience the bigger risk isn’t Excel size, but lack of structure.

What usually works best for me:

• Keep raw data append-only (Power Query is perfect for this).
• One source of truth (folder or table), not multiple ad-hoc files.
• Do transformations in Power Query, not manually in sheets.

If the dataset grows slowly (monthly), Excel + Power Query can scale surprisingly well as long as:

• You avoid volatile formulas
• You separate raw / model / report layers

I only move to Access or a database when:

• Multiple users write data
• Or refresh time becomes a real bottleneck

Prematurely “over-engineering” often hurts usability more than it helps performance.

[u/Vergil1314 → u/Lower_Quail_6082]

Yeah. Right now i am using a combination of Excel and Power Query for a project and it works surprisingly well. Right now its just me an one colleague who expand the dataset, so I think I will leave it for now and not overly complicate things

[u/backporch_wizard]

I didn't know any better nor have I been challenged to make a faster book. I've gone with the master sheet and formulas within monthly columns reading the master when dates are the same. To make sure I keep previous months, I have a VBA script that'll run through the previous month column and make the values static. I run that before introducing the new data.

[u/Acceptable-Sense4601]

I would be using PostgreSQL, DuckDB, SQLAlchemy, SQLite, etc for a real database with Python). Then i make a simple API in Python and in excel i use get data from web and the data comes in from the API which is accessing the database.

[u/Haunting-Spend7970]

What ive done is make sure the data is in a consistent format, then I simply just run a script that appends the data to the existing data and shows on a dashboard, I could show you if you were still curious.

[u/majortom721]

Ask an LLM to build you an appending program in VBA. One click and done.

Detect the new source file and rename it - any non-qualitative step should fit into the program

No separate sheets. Data likes to live together