Google Sheets cannot be transferred much data at all. That’s annoying!
Yeah, I probably should have rebooted or something.
I’ve been looking at mortality data too much.
maybe it got depressed and needed a break?
1 Like
Uh oh. Very big spreadsheet crashed and now cannot open without crashing in the process.
Lots of VBA & Macros?
Yes, but…those were in a different workbook.
This one has 447 sheets along with 441 volatile functions.
I rebooted the ol computer. All is working now.
1 Like
Wow. Just because you can, doesn’t mean you should.
4 Likes
Are 6 of those sheets feeling a bit miffed that they didn’t get a volatile function?
1 Like
Yup
They’re acting up a bit, yeah.
wtf
if you are an American actuary doing actuarial work, might I recommend you peruse ASOP 56?
and think deeply about your life choices
1 Like
So, this would be a bad time to mention that 1.3TB xlsb file I have…?
2 Likes
want to share any monster formulas inside?
this may be a good time for me to reboot that feature for SOA publications…
The explanation for that 1.3TB spreadsheet is a lack of R proficiency within our team and the folks who review the work.
It has 15 tabs, some of which are as large as 130 columns x 100010 rows.
The review/audit process ends up being:
- Review the calculations when populated with 1000 rows.
- Perform basic tests to confirm the validity of the 100k row long data files about to be input
- Run the macro that loads all the data and walks through the calculations
- Confirm that the output matches what was claimed.
…because opening the thing on my server (1TB RAM) takes too damned long.
I REALLY wish the tool that generates the numbers had a more robust query function, or that it didn’t take an act of Congress for non IT-folks to get IT approval for a SQL database, or that my auditors didn’t like to be quite so hands-on in confirming our work.
Yes, I could insist that corporate audit designate an auditor that knows R to review the work, or that they accept evidence of independent review as sufficient…but it was easier to just build the thing in Excel and be done with it.
I’m all for abusing Excel, but this file is ridiculous even by my standards.
Q: Where do that data come from?
What you need to do is get IT folks to get IT Approval, then make them in charge of the database, so that you (and others) can query and compute from it.
Until then, start documenting the time everyone takes to do tasks on the file. Including crashes. Oh, and when someone quits because of the file.
2 Likes
I can’t help wonder if it doesn’t cost more to get a server that has 1Tb ram than to set up a suitable database.
Depends on the server. I suggest that the IT folks recommend something, and actuarial department pay for it, if it is exclusive to actuaries. Funny thing though: once other employees learn about it, they will want to play with it. Then, you make those departments pay for it.
I work at a multinational carrier, and I’m the only person in my team who is US-based, works with US data on US systems in an IT environment where there are annoying firewalls at the national borders.
The US IT folks find it simplest to just give me a BIG Windows server and a spare, and to install updates to the modeling tool I’ve been told to use when I ask. And for most things, this arrangement suits me fine (even if it put my traditional goal of having a better computer at home than my work computer out of realistic reach).
The big server is big because of the modeling work I do. It would be more efficient to make use of an AWS-hosted version offered by the vendor, but our security paranoia won’t permit that. It would also be more efficient to do the modeling on one of our non-US servers…but there are other IT issues. Even a virtual server in the US would be more efficient…but apparently there would be issues given the demands of my software (“sure you could have a TB of RAM when you need it…but you’ll have to call so we can allocate it / you might need to run only when other jobs with priority aren’t running – so a couple of weekends, 2 months each quarter”).
The data behind my big spreadsheet is the output of queries run by my modeling tool. The tool’s built in query function isn’t robust enough to handle the aggregations and calculations I need to do with the output…at least not in a way that I feel comfortable validating its results. Basically, I’m doing things with this tool that the vendor didn’t really contemplate.
The good news is that there are changes in the works that mean the situation should improve (if we can get over the firewall at the border headaches). I probably could jump through the hoops to get a SQL database provisioned for the next update cycle, but the time required for that, and then rebuilding processes to use it doesn’t make sense given the changes on the horizon.
But it would have been nice to have a SQL database provisioned quickly when I started getting requests that required this number-crunching. I could have built it right (or at least right-ish) the first time. But the experience is on my, and my teammates’, minds as we work through those changes.
And, knock wood, my oversized Excel files mostly don’t crash…because I take the approach of building/validating with smaller sets of data, and writing macros to handle recalculation in a specified order, rather than trusting automatic recalculation or F9. I’ve abused Excel enough to have a good handle on how to abuse it successfully.
It’d be better news if we had the resource that would allow slack time to rebuild things in not-Excel, and to train the necessary folks in those not-Excel tools…but that’s hardly a unique problem.
Yeah, but then you have to share it