3 ms·

At my previous job, one of the accountants asked me on my first week if I could solve a spreadsheet problem they had involving payroll for it's ~250 employee's.

At my previous job, one of the accountants asked me on my first week if I could solve a spreadsheet problem they had involving payroll for it's ~250 employee's. As a 20 year database developer, manager and analyst, I was really hesitant, but after she showed me the amount of time it was taking her to do this every 2 weeks, I sort of felt obliged. All of the data was from several flat files, it consisted of each employee's hours for the period, accrued sick time, vacation time, comp time, etc. She had to manually copy and paste this data into Excel, fix formatting issues, then use it to calculate several totals. It was then fed into another system (that would only accept Excel files for input), that would print the pay checks with all this data summarized. This was clearly critical for the organization, and it was taking her a couple days to do manually. I begged her, and my supervisor, to let me do the whole thing in SQL Server or MySQL, but she wouldn't have it, because it HAD to be something she could adjust manually. I explained I could make it look and feel like Excel, but she wouldn't have it. I realize to anyone in databases (including me), I should have fought harder to do it right. However, as a new employee it was more important to gain trust than show off new technologies. So I compromised. I setup a system that imported all of the data into SQL Server, cleaned it up, then exported it to Excel. The tricky part was working with VBA, and creating recursive expressions in Excel during the export. It took me about a week of time, mostly because I hadn't used VBA in a decade. The end product, from her point of view, looked exactly like her original spreadsheet. She could then still manually fix any issues that came about before importing it into the paycheck printer. In the end, it saved her 2 days a week, or 8 days a month or 96 days a year. I became her new best friend. I was always the first to get my paycheck, and never had any issues when I needed support from finance. Probably the smartest move I made early in that job. ;)

emeril 4y ago
fwiw, one can import the contents of a list of excel files into one big excel file using some relatively vanilla vba without having to involve sql I personally usually only involve a database if there's some significant amount of data involved and/or if it's significantly more efficient than the above I don't get the hate on excel in this thread compared to rdbms's; i.e., just use a few vlookups for some joins which are usually sufficiently performant especially if you don't have that much data in which case you shouldn't be using excel in the first place... (though, if you're "clever" and want higher performance vlookups, just use it's often maligned indexed lookup feature more carefully to allow for both exact and indexed lookups which can be like >100x faster...)
- webdoodle 4y ago
  The import wasn't the hard part, as you say, pretty vanilla. It was the export back to Excel that was time consuming. It involved recursive cell references, some that referenced other worksheets and one that reference another Excel file altogether. When I tried a direct export, the system would interpret the cell references as values instead of parts of a formula. I had to parse each formula using CHR(). It also got tricky when I wanted to format the cell, say for currency. The final VBA script was barely 100 lines... I may have some of this wrong, as it's been almost 9 years now.

emeril 4y ago

fwiw, one can import the contents of a list of excel files into one big excel file using some relatively vanilla vba without having to involve sql I personally usually only involve a database if there's some significant amount of data involved and/or if it's significantly more efficient than the above I don't get the hate on excel in this thread compared to rdbms's; i.e., just use a few vlookups for some joins which are usually sufficiently performant especially if you don't have that much data in which case you shouldn't be using excel in the first place... (though, if you're "clever" and want higher performance vlookups, just use it's often maligned indexed lookup feature more carefully to allow for both exact and indexed lookups which can be like >100x faster...)

webdoodle 4y ago
The import wasn't the hard part, as you say, pretty vanilla. It was the export back to Excel that was time consuming. It involved recursive cell references, some that referenced other worksheets and one that reference another Excel file altogether. When I tried a direct export, the system would interpret the cell references as values instead of parts of a formula. I had to parse each formula using CHR(). It also got tricky when I wanted to format the cell, say for currency. The final VBA script was barely 100 lines... I may have some of this wrong, as it's been almost 9 years now.