One for the IT gurus - MS Excel

[tavas]

I see quite a few guys on here either work in IT or know far more than I about IT related matters.

We use MS Excel for our production reporting, mainly because its easy to manipulate but also because commercial programs can be very frustrating as any changes you require usually mean you need to fly someone out to make modifications.

Our internal audit team picked up that our spreadsheets are open to modification without some form of change management. We lock out cells and use a paper based system, but in reality anyone could manke a change (or mistake) and we wouldn't know who it was.

I was wondering if anyone knows of some program I can overlay Excel to log changes? Our IT dept doesn't and Google gives me version control for programming.

I would rather stick with Excel as transferring across our reporting into another program is going to be costly.

 

[sp0rk]

TBH, my vote is move to Oracle and use APEX for your reporting and whatnot
All changes are loggable and restrictable, SQL and PL/SQL are fairly easy to learn (takes a little while to completely master though)
You can import the majority (if not all) of your data into Oracle via APEX
work can be done remotely if need be by your Database Admin

 

[mxd]

look at setting up a source control type tool, that you have to check the file out then check the file in. You could then have some one who "accepts" the file and puts it in the location of use.

If the spreadsheet is on a network you can do a share and excel may track changes ?

 

[waz_j]

Your IT Dept should take a look at Sharepoint. Comes as part of Windows Server and allows you to store documents in a shared environment but with strict access controls (who gets to read, who gets to modify etc). To modify a file, you check it out so no one else can work on it while you are and it maintains a version history and log of all changes.

If your IT doesn't want to set it up, then Sharepoint Online is a the same thing except hosted by Microsoft and only costs $5 per user per month.

 

[Airgead]

Your IT Dept should take a look at Sharepoint.

If they want to spend the rest of their lives in some nether IT hell cursing the day they ever opened the box.

But yeah...source control is your friend here. Plenty of document management systems out there without resorting to the black art that is sharepoint management.

Cheers
Dave

 

[tavas]

We have Sharepoint, never thought about using it as version control for this application, but that "could" work. Its a shitful program though. Our production reporting relies on data sources from PI Process Book, DCS and assays from external labs fed into an SQL database. I am not sure if Sharepoint would allow me to run the metal balance without having to check it out everytime. I need to be able to run the calculations every day, just not change the calculation formulae.

I will look into source control stuff and see if there is something that does Excel. most oif my brief look into it was for progarmming code which is above my head.

 

[bum]

Any point in asking your auditors what they'd like to see? There's tonnes of solutions with varying levels of complexity and flexibility but it seems your issue is one of compliance rather than actual needs you require to be serviced.

 

[tavas]

@ bum. You are exactly right. They audit a process without any idea of how that process should work. Hence my reluctance to spend more than I absolutely have to. They only tell you what's wrong but don't provide any suggestions on how to fix it.

 

[anthonyUK]

@ bum. You are exactly right. They audit a process without any idea of how that process should work. Hence my reluctance to spend more than I absolutely have to. They only tell you what's wrong but don't provide any suggestions on how to fix it.

For auditors it is easy. Just highlight an issue and walk away. They will say themselves that they cannot offer solutions. The bastards are the bane of my life.

 

[Fat Bastard]

Tavas, what is it exactly you're trying to do?
I've just given up after three years trying to satisfy the internal regulatory crowd that an excel spreadsheet can be locked to their satisfaction. It can be, but it also makes it impossible for the average operator to use on a variety of different computers with a variety of different security settings. It was supposed to be a dynamic form that changed from machine set up to the next set up, but we've had to go back to simple locked PDF format forms that the document control system can deal with.

I don't know what your spreadsheet is for or what business you're in, but if its in a strict regulatory environment, you're probably flogging a dead horse with excel.

 

[pk.sax]

I'd agree with FB there.

For all our internal reporting, I'd setup an adobe form rigged to submit itself on a click as a fully filled out form attached to an email. Adobe professional required to make them. Worked fairly well for 1-2 page document submissions.

For our external reporting, being just me, I'd setup an excel database that drew from the maintenance database and manual entry and formed printable pages out of excel. Didn't have an issue with that.

There is also 'helpdesk' softwares. Essentially web interface ticketed support management systems. You can rig them to do anything you want. A bit better and more functional than just 'web forms'. Depends what you need them for though. Look up webhelpdesk.com, my favourite that one.

 

[sp0rk]

We have Sharepoint, never thought about using it as version control for this application, but that "could" work. Its a shitful program though. Our production reporting relies on data sources from PI Process Book, DCS and assays from external labs fed into an SQL database. I am not sure if Sharepoint would allow me to run the metal balance without having to check it out everytime. I need to be able to run the calculations every day, just not change the calculation formulae.

I will look into source control stuff and see if there is something that does Excel. most oif my brief look into it was for progarmming code which is above my head.

If you're already running an SQL database, why not just use Oracle Application Express as a front end for reporting and generating spreadsheets?

 

[Airgead]

If you're already running an SQL database, why not just use Oracle Application Express as a front end for reporting and generating spreadsheets?

Because they will be running MS Sql server and probably don't want to take out a second mortgage to get oracle licenses...

Office has a forms thingy these days. I think its called InfoPath. Might be worth a look. You can design forms, lock down the form design and users can fill them in and submit to a database or spreadsheet.

Cheers
Dave

 

[tavas]

Thanks for the help so far guys. I work on a mine, so what we do is take the production physicals (such as tonnes milled, densities, flow rates etc) from our data historian (PI Process Book) and combine it with assay data from our lab (LIMS). We import both sets of data into Excel and calculate our production stats, from which we generate a daily, weekly and monthly report. What the auditors have picked up on is the calculations we use in Excel could be modified with no change log or history. A the moment we have a change log but it is essentially an honesty system, there is no automatic logging of user name etc.

So what I was looking for is something that overlays Excel so I don't have to move my calcs to another program. I don't know if such a program exists. I am not sure I need to go to Oracle for this but will look into it.

 

[bum]

I know of at least one that does what it seems like you want. I can't recommend it because I know pretty much nothing about it apart from the fact that it exists. Might help you get started on looking into such products though.

http://www.litera.com/Products/Resources/Whitepapers/Implementing-Strong-Controls-for-Excel.pdf

 

[tavas]

Thanks bum. I'll check it out.