Microsoft Excel

There's no mention of spreadsheets anywhere in the link you provided to SOX 404. A better link would have been to the PwC whitepaper on it,

http://www.pwcglobal.com/extweb/service.nsf/8b9d788097dff3c9852565e00073c0ba/cd287e403c0aeb7185256f08007f8caa/$FILE/PwCwpSpreadsheet404Sarbox.pdf

but maybe acting as a Microsoft employee you were prevented from providing a link to PwC documents without Microsoft having to pay PwC for it. Maybe a link in a blog response won't trigger royalty payments, but that's no my concern.

As for Microsoft's whitepaper, the implication is that Office 2007 would be unusually helpful with the following.

• Preventing unauthorized access to spreadsheets

This is more of an OS matter. Put all xls files on a server capable of providing selective access by file and user or group, include udfs in the xls files that check for connection to an approved server and otherwise return errors, and PROTECT the xls files, and you have effectively prevented unauthorized access. If only Excel had robust INTERNAL passwords, this would have been relatively easy to have done in XL8. Convenient that Office DRM involves a tie-in to Windows 2003 Server.

• Managing and monitoring spreadsheet changes

So XL12 will come with the equivalent of CVS (or name your preferred code control system) for spreadsheet cell formulas as well as VBA code? I've been using macros to write cell formula listings and VBA modules to text files for years, and I've been using RCS to check in, check out and compare revisions to previous versions. Will this be built-in in XL12?

• Retaining and archiving spreadsheets

This has been possible for decades. It's called backup. Maybe a best practice more often honored in the breach, but definitely not new nor something for which any application would prove more supportive than any other.

• Developing robust spreadsheet models

Maybe XL12 promotes this. Maybe it'll prevent unintended errors, but there's no hope for well written implementations of faulty algorithms.

If you want true robustness, then you NEED much stronger validation rules than Excel currently provides, **AND** it should ****NOT**** be possible to bypass validation by pasting plain text into cells. Does XL12 address that? If not, XL12 is just as much a validation nightmare as previous XL versions.

To Harlan:

Harlan, I've been lurking on this blog for months, and you appear to be an expert in spreadsheet programs.  You know Excel, Lotus, and other programs inside and out (I gather from the tone of your posts, that Excel isn't exactly your favorite, and you're forced to use it due to its prevelance).  As an expert on Excel, you have described lots of shortcomings of the program, and always ask, "Has this been fixed in Excel 12?  If not, then Excel 12 is no better than the previous versions. (Or if Excel 12 does fix the problem, then Excel is merely catching up to Lotus 1995, so Excel still underwhelms LOL)".  I'm wondering if you've reported your ideas for improving/fixing excel to Microsoft in the past so as to improve the chances that they are indeed fixed in Excel 12.  As it is, it seems that Excel 12 isn't going to address your particular concerns because nobody (or, not enough) people voiced those concerns in the past.

To Peter M,

If anyone at Microsoft made a habbit of reading the Excel newsgroups other than *.programming, they'd have had the opportunity to have read all the suggestions I've made, which have been made by hundreds if not thousands of users over the years (yes, years!).

To clear things up, Excel 8/9/10/11 have all been at least as good as all their competitors IN GENERAL, but there have been areas where it took a LONG TIME for Excel to catch up to the competition and some for which it still hasn't.

Some of those odd elements simply defy rational explanation, e.g., Excel's MOD function which has a limit of about 29 binary bits. *IF* Excel were using hardware floating point, its MOD function would work up to 52 binary bits, the limit of IEEE double precision floating point. Putting this in stark perspective, the formula

=MOD(2^29,3)

returns #NUM! in Excel (and will continue to do so in XL12) but correctly returns 2 when used as a formula in a Word table. When, if ever, will the Excel programmers seek help from the Word programmers to fix this?

[This is a clear sign that Excel's MOD function doesn't just wrap a call to floating point hardware ops or to standard math libraries' mod functions. No, it means some programmer many years ago wrote a special purpose MOD function that assumed more limited binary precision, and no one on the Excel development team has had either the motivation or the guts to scrap it. That isn't my idea of a software company devoted to producing quality products.]

Maybe my perspective is skewed, but development platforms have NO EXCUSE to get any of the simple things wrong. And anything in the way of calculations that Word can do right Excel should damn well also be able to do.

And all this has nothing to do with my first response to this blog post. XL12 isn't uniquely able to support the new compliance mandates. When it comes to anything involving security, process and archival, most of the technology has been around for decades, especially with respect to version control and backup. And these are mainly OS features.

The development angle is appropriate, but if adopted by most companies would mean most of the Excel user base shouldn't be allowed to develop anything more than personal, ad hoc spreadsheets because they're just plain ignorant of basic software development practices. And that won't happen for departmental applications which have been developed as spreadsheets in the past precisely because the documentation steps were bypassed because no one had the time to do them nor the training to know how to do them properly.

Centralized development following established development practices should produce better software, and would certainly better meet regulatory compliance mandates. However, I'm a cynic and an empiricist. From what I've seen in my working life, companies' practices exhibit inertia and don't change unless sufficient force is exerted. Until there've been several successful, high profile lawsuits against companies specifically and mainly for relying on spreadsheets with lax controls and scanty documentation, little will change. If fines are cheaper than what it'd take to avoid the fines, rational managers will choose to pay fines.

BTW, on page 17 of Microsoft's own whitepaper there's an image of a portion of a spreadsheet. I know it's there to demonstrate formatting. It was obviously thrown together by someone who hasn't a clue about financial calculations. It shows a 48 month loan for $248K at 6.5% interest. Then it shows total interest of $650K and monthly payment of $1,750. Taken together, these numbers don't make sense whether the 6.5% interest rate is annual or monthly.

If annual, total interest is overstated by more than a factor of 10, and the monthly payments amount to less than $30K of principal payments. If monthly (ignoring usury issues), the monthly payment of only 1,750 leads to negative amortization, i.e., it doesn't even cover monthly interest, and total interest incurred but unpaid is about 6 times higher than $650K.

No one in Microsoft's financial department available to help put together this whitepaper?

A fine illustration of why technical writers shouldn't be left on their own to make examples of concepts they don't understand, and a sad and hopefully unrepresentative example of poor interdepartmental peer review within Microsoft.

Also, on page 18: "an author can password-protect selected cells." Did you forget to mention this new XL12 feature - individual cell-level passwords?

Did no one on your team proofread the whitepaper?

Harlan, as always, thanks for the comments.  We did read the paper, and we did a lot of peer review, myself included.  With regards to your question about password-protecting cells, cell protection dates back a long way, and allowing users to edit only certain ranges dates to Excel 2002, so no, these are not new features, but ones that have been around for some amount of time.  If you are interested, there is info on Office Online.  http://office.microsoft.com/en-us/assistance/HP052010591033.aspx.

With regards to the PWC paper – there is no policy about what we can link to; it is simply that this paper covers guidance around spreadsheets and compliance from the MSFT perspective – the process outlined in the white paper, for example, is how the MSFT SOX team operates (and yes, they were on the peer review).

With regards to “preventing unauthorized access to spreadsheets”, “managing and monitoring spreadsheets”, or “retaining and archiving spreadsheets” … these areas are all significantly improved in Office SharePoint Server 2007, in ways that an empiricist such as yourself would appreciate, so I would encourage you to look at some of the improvements mentioned in the whitepaper in more detail as additional material comes available over the next 6 months.  For example, wouldn’t it be nice if administrators could provision a spot for users to save their work where files users have selective file access based on their role.  And wouldn’t it be nice if every time a user took an action on a document (save, change, print, etc.), that was logged?  And wouldn’t it be nice if every time users made a change to a file, the file was automatically versioned, so that there was full audit trail available of who changed what when.  And wouldn’t it be nice if for certain document types (e.g. something that produces a number that ends up in the GL), whenever a user made a change to a file, workflow was automatically triggered so the file received proper testing and approval before it was “checked back in” to the document management system.  And wouldn’t it be nice if, to the user, they didn’t even notice all this, since the place they saved their document just looked like a file share?  Etc.

There are a couple of goals behind the improvements that were made in this area (which, by the way, are not improvements to Excel, but our document management feature set), which include (but are not limited to) making it very easy to set all this up (my perception is that companies don’t necessarily need “force”, but better tools that are easier to set up and administer), allowing users to work as they normally work (too often, solutions we see to auditing and logging require users to do all sorts of extra work, which they generally do not have the incentive to complete), and providing this in a unified place (so that you only have one tool to set up and work with).

On the Excel side, our work with Tables (especially calculated columns) addresses some of the most common errors seen in spreadsheets.  No doubt, there is more work to do in the area of robust models (like fixing data validation).  If there are specific requirements you would like to pass on on that or document management in general, please feel free to use the link to send an email.

Hi,

You know, I must say that David Gainer is one of the best people at Microsoft that I have ever dealt with.  Harlan is a well respected Excel figure - he's well known in the newsgroups.  On a professional level I respect
both of them for the work they do.

So why do I write?  The statement "an empiricist such as yourself" is really not on.  It's the whole arrogance of that statement - basically you would lead most readers to think that you are dismissing Harlan as a tinkerer, a mere user, not a developer.  And this is endemic to Microsoft as a firm.  Read the work of people like Raymond Chen and you'll not see him be so dismissive of the people who write the software that makes big firms buy Microsoft software.  

You fail to address the issues that he raises and just continue on and on.  What about addressing the issues he raises - perhaps explaining the crazy sounding MOD function that he raises?

I posted a message a while back asking if anyone at Microsoft had a Bloomberg or Reuters terminal to test RTD - never got a proper answer to that one.  I think not, because the implementation of RTD that is available is simply not very useful in the real world of a trade floor when multiple real time data sources will be in use.  You can see more about that in the newsgroups - just search for my surname and you'll see the suggestion.  I was contacted by two firms that actually offer the functionality I suggested as a wrapper to RTD, so the demand is there.  My humble suggestion is that while you should be proud of Excel 12 you should listen and act on what you hear more.

Finally, I am not some linux geek microsoft hater, I work with microsoft software 95% of the time and am offering all of this in a friendly way, not to beat up on microsoft.

Best regards,

John

With regard solely to SharePoint servers, there are still a lot of companies using Lotus Notes and other groupware products that provide finer grained access and execution control than plain vanilla Windows file servers. Notes has been able to provide security and authorization capabilities claimed for SharePoint since Notes 4 came out in the mid 1990s. THIS FUNCTIONALITY ISN'T NEW.

In Notes one could design a form that was primarily an embedded document with a few extra fields added. It was possible, programatically, to use certain information in the embedded documents as form-level fields. Finally, separate read, write and modify access could be granted to different users and groups, groups could contain other groups, departmental managers could be given Editor access to their department's Reader, Author and Editor groups, which would already be included in the databases' respective Reader, Author and Editor groups, so departmental managers could have complete control over who in their own departments would have different types of access to company databases without having Manager access to any database. And to top it all off, macros in the embedded documents could be fed the current user's access level to determine what parts of the document when loaded would be visible and/or unprotected. Granted developers had to program visibility and protection, but this capability has existed for a decade. It appears SharePoint services makes the process a bit more automatic, but it's refining existing capabilities not introducing them.

Specifically in re cell protection, OK, I'm wrong. Tools > Protection > Allow Users to Edit Ranges... does provide range-level access passwords.

That said, they only work when the worksheet containing the ranges is protected, and worksheet-level passwords are simple to remove. Thus, range level access passwords are simple to disable.

If the goal is preventing innocent, unwitting users from corrupting worksheets, why are passwords necessary? If the goal is preventing intentional changes, worksheet-level passwords as implemented at least through XL11 are inadequate.

Will XL12 finally provide robust internal passwords?

Hi John, thanks for the feedback.  First, I meant absolutely no disrespect to Harlan (who called himself an empiricist in his post above) – I respect and appreciate Harlan’s participation in this forum (and everybody else’s too), and I try to answer any and all questions as sincerely as possible.  If it sounded otherwise, my apologies.  In this case, I was simply agreeing with Harlan that organizations don’t always change their behaviour easily, and I was suggesting that the work done in Office 2007 will, in my opinion, help, because it provides IT with a good set of easy-to-manage tools designed precisely to solve these sorts of problems, and because it is largely transparent to users, so it does not require changes in the way they work.  To be clear, a large part of the work Office has done in this area is external to Excel, and there is always more we can do, but the customers and regulatory bodies with whom we have discussed our our work in this area seem enthusiastic about what is coming in Office 2007.

Second, with respect to answering questions, I try to answer every question that comes my way, many of which are in email and not comments.  Once in a while I miss, and for that I apologize.  With respect to RTD, yes, we use it at MSFT, with financial market data feeds in some of our finance groups.  The most common request I have personally heard around RTD is the ability to specify formatting along with data, but I have certainly made a note of your request for consideration in the future.  Also, I can tell you that our work on multi-threaded calculation improves the calculation performance of sheets with RTD from what we have seen with our testing.  With respect to MOD, the problem is that when you use two numbers that are *very* far apart, MOD returns an incorrect result.  This is likely a vestige of performance optimizations in the very early versions of Excel.  We do improve functions based on customer feedback (see http://support.microsoft.com/default.aspx?kbid=828888&product=xl2003), and I have made sure that MOD is on the list for us to think about next time.  One note that may or may not be interesting – when we make these sorts of changes, we inevitably hear from customers that do not want to see the changes, generally because they do not want to see any slowdowns in functions, and increasing accuracy generally has some sort of performance effect, even if it is only a few %.

And now to version control.

Version control is nothing more than dedundant file storage unless the users making changes provide detailed descriptions of the changes they make OR it's possible to provide comprehensible difference listings of different versions. With binary files, the latter doesn't hold. Maybe with plain text XML it would, but that remains to be seen.

With repsect to general ledger, most financial services companies, the sector in which I've spent most of my working life, still use mainframes for the bulk of transaction processing. Other businesses may use network databases. Payroll and compensation, due to their complexity, are generally handled by dedicated packages. As are taxes, benefits and many other high transaction count expenses. Few large companies would use spreadsheets for any of this.

For the general ledger, spreadsheets primarily come into play for maintaining records of investment assets and determining asset valuations. There *should* already have been procedures in place in companies for multiple levels of review and approval of the summary figures that would find their way into the companies' financial reports. Dumping this into SharePoint won't necessarily fix anything, it'll just make the process more consistent and mechanical. But if the logic behind the process or its execution is flawed, it'll still be flawed in SharePoint. Automating errors doesn't correct them, it only generates more errors in less time.

Finally syntax. New table referencing may help prevent some kinds of formula errors, but it won't help with 2D (as in both top row *and* left column containing identifying labels) or more complicated data structures. And if you were really serious about making table referencing more robust, you either need to update SQL.REQUEST or the list functions like DSUM in order to handle complex criteria more cleanly than the SUMPRODUCT and array formulas that will still be needed despite the addition of SUMIFS, COUNTIFS and AVERAGEIFS.

Hi David,

Thanks very much for your reply, honest and intelligent.

With respect to RTD, the majority of Investment banks are still using DDE to pull data from Bloomberg and Reuters - that is what I see every day and part of the reason that they do not adopt the RTD technology is that it does not work in a fine grained manner.  If anyone at Microsoft had ever worked on a trade floor they would instantly know what I am talking about.  RTD is an excellent technology for some cases, I have evangelised it's use and promoted adoption in several Investment Banks, but when you have multiple feeds it's not suitable for task.  

Bluntly I have to say that I do not believe that anyone from Microsoft actually understands the imperatives of the trade floor environment.  Now, I realise that this is a tiny part of your user community - you probably have more teachers, dentists and nurses (for three examples) using Excel than traders, but I am pretty sure that the trade floor is where you will see Excel pushed to the limits and beyond. So, by understanding that community I think you will improve your core product.  

You really face a complex problem, Bloomberg and Reuters both beta test Excel 2007 in isolation, but since both firms passionately hate each other you the only way you can test both in combination is to get a Bloomberg (c $12,000 per year) and a Reuters (c $10,000 per year) on site in Redmond (or wherever) and actually test for yourselves.

One last thing - I'd love to see a post on where Microsoft is going with user produced code for Excel - xla, COM addin, Automation addin, xll, .net code.  At the moment I work with all of the above, but I'd really like to know what is the Microsoft roadmap for this.

That's my $0.02 worth (or €0.02) but I'd be very happy to discuss this offline.

Hi

Harlan, thanks for all the excellent feedback.  Also, you can encrypt passwords - does that answer your question?  Here is some info (note step 4): http://office.microsoft.com/en-us/assistance/HP011124101033.aspx

John, I would love to hear a bit more - feel free to email me using the link.  Also, I will add that to my list of topics.  Thanks.

Your link is old news. Yes, it's possible to protect the entire workbook with a file open password, and that alone among all the passwords that could be used in Excel workbooks is encryted and not easily defeated. None of the others are. Why not?

If one group of users shouldn't be using A.xls but another group should, is it better to save A.xls with a file open password, then tell the latter group only the password *OR* put A.xls in a folder for which the former group doesn't even have List Folder permission? When it comes to REAL security, it's file system access and permissions/rights that matter. Application-level password protection is crude and usually ineffective.

What sort of security is there to protect the file against the people who *DO* need to use it, and so have the necessary file system permissions and/or the file open password? If I can open a workbook, I can use File > Save As to change the General options, remove the file open password, and save it under a different name. That effectively eliminates the file modify password as well.

If I have Write permission for the original file (I shouldn't if I don't have the file modify password), I can use CMD's copy command in a console window to overwrite the original with the modified copy. So Excel file modify passwords, even if they were encrypted, won't actually provide security by themselves. It's the file system that provides security again.

But if I don't have either the file modify password or write permission, then I must only need to view the file and perhaps print parts of it. If I can open the file, I can use one of several widely available tools to remove worksheet and workbook protection passwords. Once that's done, I can alter anything I want in the file, then print doctored exhibits. Likely doctored exhibits would eventually be detected, but how long could it take?

I could also find out all the oh, so valuable business logic the lazy company put into way too vulnerable spreadsheet files rather than some more secure application delivery medium, such as compiled binary executables.

Excel's security features on their own are weak. Only file open passwords are robust, but they're no help against users who're supposed to use such password-protected files since those users would have the passwords. Once an Excel file is open, it's EASY to disable all the internal passwords as well as remove the file open password when saving copies of the file. Maybe SharePoint changes that, but Excel on it's own is an electrified fence (file open passwords) surrounding a fortress of toilet paper.

Either you're ignorant of Excel's practical lack of security features, or you're constrained to parrot marketing BS in this blog.