BOFH: You can take our lives, but you'll never take OUR MACROS

Never mind the blood, Mr Beancounter: MAGIC BEANS!

  • alert
  • submit to reddit

Application security programs and practises

Episode 6

"...And I can't seem to import all of the data I need," the user explains.

"And you're importing into Excel from what... a CSV file?" the PFY asks.


"And the import fails?"

"I just stops. It says something about resources."

"So perhaps you should get rid of some of the data in the spreadsheet?"

"It's an almost empty spreadsheet. All it has is about eight macros and a three extra tabs which are used by the macros."

"So it's probably the data then..."

"But I import the same data every month."

"What data?"

"Just the financials data."

"What, like monthly General Ledger summaries, etc?"

"No, the transactions."

"For accounts receivable, accounts payable or something?"

"For AR, AP as well as GL, Fixed Assets..."

"So pretty much all transactions?" the PFY asks.


"For that month?"

"No, just for the tax withholding period."

"So you're replicating the entire financials system for what, 7 YEARS? Or is it 10?"

"I'm just exporting the transactions!"

"Yes, that's what the financials system is. Transactions."

"Well, Anyway, I do 11 years - just to be safe".

"Into Excel?"


"So that's most likely your problem. Too much data."

"But it's been working up until now!"

"Yes, because now you've gone over some limit. How long does the export take?"

"I don't know, I just set it going before I go home."

"Why the hell would you need all the data?"

"I like to be able to plot trends."


"It is if I wanted to know what the average sales per annum is over the last 11 years. I use it to make graphs."

"So why don't you have a separate spreadsheet with 2003, 2004, etc. to 2014. Then you'd only need to export this year's data."

"Our financial calendar is July to July."

"So label it by the financial period."

"I do - it's in one of the macros. I do a left substring on the date in a macro and move everything with a year and month before July into the previous year's tab, and everything with a year and a month after July into an..."

"Sorry, I think I just slipped into a coma, but I'm back now. Save previous years' data in separated spreadsheets."

"I can't - we don't close off the financial years immediately."

"You don't close them off for 10 years."

"No, but it's possible that we might find a historic transaction that would affect the numbers."

"And, in the event that this happened - an event with a probability in the same far-fetched region as the SETI program ever hearing something or intelligent life being found in Luton, you could just reimport that one year's spreadsheet."

"But that number might affect all subsequent spreadsheets!"

"Reimport them!"

"But my macro does this all autom..."

"That's what it's about isn't it - your macro?" the PFY asks insightfully."

"I don't follow."

"Bloody beancounters," the PFY says. "You'd write email in excel if you could figure out how to do it in a font you like.

"I hardly think that's a deserved comm..."

"Okay, TELL ME you don't have a spreadsheet at home with all your DVDs and CDs in it which orders them by some star rating, the price you paid for them, where you bought them, who referred you to it, what else they referred you to, a convoluted SUM function to tell you the total cost of them all and then an algorithm to tell you their current value, based upon rarity, purchase price, depreciation and movements in the US dollar."

"I need a record for insurance purposes!"

"And what about the macro that tells you when you last watched/listened to them?"

"How did you know about that?"

"I didn't - I just said the saddest thing I could think of. No, wait, a movie rating algorithm based on a weighted calculation of... uh.. guns, fast cars, tits... and... uuuuhm... people getting dropped into helicopter blades."

"It's a more accurate method than R ratings!!"

"So's the bloke at the video shop!"

"Look, this isn't getting my data imported," he snaps, getting back to the point.

"Because you can't import it," I say. "You've run into one of Excel's many limitations."

"The main limitation being that it has an inbuilt stupidity limiter," the PFY explains. "When the data reaches a certain point, the stupidity limiter kicks in and you have to do it a proper way."

"Like learning SQL and using that," I add. "Or using the database's reporting system. Or a third-party reporting system which talks to the database."

"Perhaps you could use Access?" the PFY suggests.

"Access?" he asks, no doubt in similar tones to those used to say the words "Musket?" and "Shiny beads?" in the founding years of the Empire.

"Yes. It's a Microsoft product. It's a database. You could import all the data into that and then talk to that with Excel."

"So I could still use my macro and make graphs?"


Sometimes, just briefly, my conscience pricks me. But then I suffocate it and bury the corpse deep in my subconscious where no one goes. Sure, I'll have to do it again next week, when we have the SQL Server lite conversation, but for now, I'm at peace...

Bridging the IT gap between rising business demands and ageing tools

More from The Register

next story
Auntie remains MYSTIFIED by that weekend BBC iPlayer and website outage
Still doing 'forensics' on the caching layer – Beeb digi wonk
Attack of the clones: Oracle's latest Red Hat Linux lookalike arrives
Oracle's Linux boss says Larry's Linux isn't just for Oracle apps anymore
THUD! WD plonks down SIX TERABYTE 'consumer NAS' fatboy
Now that's a LOT of porn or pirated movies. Or, you know, other consumer stuff
Apple fanbois SCREAM as update BRICKS their Macbook Airs
Ragegasm spills over as firmware upgrade kills machines
EU's top data cops to meet Google, Microsoft et al over 'right to be forgotten'
Plan to hammer out 'coherent' guidelines. Good luck chaps!
US judge: YES, cops or feds so can slurp an ENTIRE Gmail account
Crooks don't have folders labelled 'drug records', opines NY beak
Manic malware Mayhem spreads through Linux, FreeBSD web servers
And how Google could cripple infection rate in a second
prev story


Top three mobile application threats
Prevent sensitive data leakage over insecure channels or stolen mobile devices.
Implementing global e-invoicing with guaranteed legal certainty
Explaining the role local tax compliance plays in successful supply chain management and e-business and how leading global brands are addressing this.
Top 8 considerations to enable and simplify mobility
In this whitepaper learn how to successfully add mobile capabilities simply and cost effectively.
Application security programs and practises
Follow a few strategies and your organization can gain the full benefits of open source and the cloud without compromising the security of your applications.
The Essential Guide to IT Transformation
ServiceNow discusses three IT transformations that can help CIO's automate IT services to transform IT and the enterprise.