Excel, Access or other..?

[baitmaster]

I'm creating a database of costs for use in a very large financial model. The product lifecycle is around 200+ years, some (but not all) sections of which I'll need data at a monthly level of detail; I don't yet know how many cost lines will be in there but as a working assumption I'll say 10,000. That may be reasonably accurate, it may be wildly inaccurate up or down. It would be useful to track how cost forecasts change over time, so a version history would be of benefit for every cost line, i.e. the same costs will exist multiple times, e.g. the Jan 2016 version, Mar 2016 version etc.

The financial model itself exists and only requires summary cost totals to be entered into it, perhaps 10 or 20 rows by 2000 columns. All costs in the database are just that, no underlying calculations need to be stored although there will be a number of additional fields e.g. unique key, owner, assumption, date stamp, source file, multiple categorisation fields etc.

To me, Excel is too cumbersome for this. Version control could work by having multiple iterations of data files, but we'll quickly become overwhelmed with the sheer quantity of files and will start to lose data. Looking back to older versions will become awkward as we won't know which version is which without trawling through old files. Knowledge will be lost as people leave the company and new people arrive. Calculations and data mining will quickly struggle with the volume of calculations.

I've comfortable using Access as a data store, passing data in, updating and pulling totals out again using SQL. I find this good for fairly large amounts of data but I've only used it for around 250k records. I like it because it works well with Excel and I'm comfortable writing SQL within VBA. I haven't really used Access via the Access software, only as a "flat" data store via Excel / VBA. I'm unfamiliar with other databases and any issues experienced as data grows.

Plan would be to hold data in Access, but pass it in using Excel files configured to push data in / update old data from "active" to archive". Separate Excel files would provide managers the ability to pull summary totals out, produce reports etc.

Access is my current preference, but I'm interested in recommendations of other software. Future-proofing should be considered, and budget is currently zero, must be something I can develop in-house at least as a concept

Any suggestions greatly appreciated

 

[Rijnsent]

Hi Baitmaster,
given the "zero cost" approach, an Excel front-end with an Access back-end sounds pretty reasonable. The limitation of Access is 2 Gb per file, but depending on the data you could split it over multiple files. And to give you an idea: I have an Access file of 1,1 Gb here with 1 table: 61 columns and about 1 million rows. If Access is/becomes to small, you could upgrade to e.g. SQL server: When and How to Upsize Microsoft Access Databases to SQL Server , but for that you'd probably need your IT department, which the Access solution avoids.

"10 or 20 rows by 2000 columns" - that sounds like really bad database practice, databases are normally about tall & small, so few columns, but millions of records are okay. What about this for a structure:
Tbl_Assumption_Master
AM_unique key, owner, assumption, date_stamp, source_file, multiple categorisation fields

Tbl_Assumption_Details
AM_unique key, cost_name, month (201601 format: YYYYMM) or year (if that is more practical, but I'm assuming 200 years and 2000 columns means you have 1 column per month per year), amount

Okay, that's just about the size of the data... For the interaction with it: for reporting purposes I normally use an Excel with pivot tables linked to Access queries/tables, which works like a charm. That allows users to pull in the data and work with the data in a very flexible environment. For the input you probably have to come up with some kind of relational database structure and forms/load/save/input sheets for the users. I can imagine the following sheets:

-User interaction sheet with all data, editable? Buttons to load and save data (new version, archive the old one)
-e.g. report 1
-e.g. report 2
-(hidden) load sheet - raw loaded data - the "load" button on the user sheet does 2 things: 1 pull in the data, 2 process it to the user sheet
-(hidden) save sheet - on the save button, data is 1) transferred to the save data sheet (and formatted) and 2) pushed to the database(s)

I hope there is some stuff in my answer that will help you forward. I've built a couple of those Excel-Access solutions, but most examples I have are owned by my clients (so can't really post them here, but can post some code/things I learned),

Cheers,

Koen