• WinformsC#SQLDevGirl (unregistered) in reply to cod3_complete
    <quote> Access will open an exclusive lock to any table that it connects to. </quote>

    Not if you do a passthrough query (to the SPs that you have written on the SQL database, if you are at all sensible).

  • Dirk Diggler (unregistered) in reply to Fred
    Fred:
    ibanker:
    could you guys tell me whats wrong with using excel? Why is Access better than Excel in this case? If I would be chosing between Excel and Access I would go to using Access only when I have a lot of data.

    Like everyone else here, I've implemented Access calculations, and had to explain to the client why the results were different from the Excel calculations.

    So, you tell me: Access is observably better than Excel for this kind of application, but why is it so?

    Concurrency, data integrity and validation are the biggest issues I've had with excel as a data entry tool. Send out a spreadsheet and ask someone the enter their 'numbers' and you get back a spreadsheet in a different format with your formulas mangled or text where you are expecting numbers/numbers where text should be, invalid codes etc. Where my wife worked the vacation schedule was an excel spreadsheet on the network, 600+ employees all trying to schedule their time off. Excel is very flexible and if it was the only tool I had I would use it. Access in this example was merely a front end. Even then it doesn't scale well across a WAN (bandwidth). Bound controls and all that.

  • CGomez (unregistered)

    This WTF made me angry. Maxim was accused of incompetence and upon finding out the root cause (someone else's incompetence) he was shrugged at.

    That would make me angry if that happened to me (and it has, and I've gotten angry enough to quit on the spot over it).

    People are more concerned about being right than fixing problems. Whoever shrugged at Maxim just didn't want to admit he was wrong for calling Maxim incompetent.

  • (cs) in reply to ChrisSamsDad
    ChrisSamsDad:
    I think it was named after the DC Comics hero 'Thor' who had a golden mallet that amazing was the way he solved almost every problem that came along.
    #1 "Thor" is a Marvel comic, not DC.

    #2 Mjolnir is not made of gold, or even gilded.

    #3 The God of Thunder does not use his hammer as a first resort. For example, the competitions against the frost giant Utgard-Loki.

  • Hammockwarrior (unregistered)

    Working in a IT dept. for a financial institution that uses massive spreadheets for mission critical processes... And then they basically ignore the results and go on a hunch... I mean. Really. Why bother? We have been fighting tooth and nail for two years to identify every 'special' excell file and figure out what it does. I have nightmares about someone's laptop being stolen and then realisng that that was the only copy of his 'special' file. At least I am not alone.

  • Dirk Diggler (unregistered) in reply to cod3_complete
    cod3_complete:
    Dirk Diggler:
    cod3_complete:
    'Actually this is still a HUGE WTF because Access will open an exclusive lock to any table that it connects to'.
    I think you are incorrect. If this was a troll carry on.

    I'm not doing hand waving or theorectical talk here Dirk. I'm speaking from direct experience with having broken Access programs locking up ENTIRE tables they connect to and I'VE GOT THE ERROR LOGS FROM DATABASE CONNECTIONS TIMING OUT TO PROVE IT!

    My experience is different. I've used Access to access DB2, Informix, Oracle and SQL Server without locking an entire table. How do connect to the database? I use ODBC. This sounds like an issue with the back end database, was the table created with row level locking?

  • WinformsC#SQLDevGirl (unregistered)

    The database behind a very well known and widely used accounts system uses floats for monetary fields.... (at least as of 2004, they may have fixed this now. I'd hope)

    I know because I had to write code for payroll to interface with the database.

    I have previously had to demo the Floating Point/Decimal problem by writing a suitable calculation in MS SQL, Oracle SQL Access VBA, Excel VBA, VB (and getting it written in C++ and some kind of Unix script by the peeps that knew that) to prove that we shouldn't be using floats for (in that instance) bond dividend calculations. The plethora of tools used was purely to prove that it's the datatype not the tool or even operating system...

  • Eric (unregistered)

    It would have been more of a Dilbert WTF if they asked him to put the bugs back in.

  • (cs) in reply to Dirk Diggler
    Dirk Diggler:
    cod3_complete:
    Dirk Diggler:
    cod3_complete:
    'Actually this is still a HUGE WTF because Access will open an exclusive lock to any table that it connects to'.
    I think you are incorrect. If this was a troll carry on.

    I'm not doing hand waving or theorectical talk here Dirk. I'm speaking from direct experience with having broken Access programs locking up ENTIRE tables they connect to and I'VE GOT THE ERROR LOGS FROM DATABASE CONNECTIONS TIMING OUT TO PROVE IT!

    My experience is different. I've used Access to access DB2, Informix, Oracle and SQL Server without locking an entire table. How do connect to the database? I use ODBC. This sounds like an issue with the back end database, was the table created with row level locking?

    Dirk the problem is actually with how the front-end Access programs are accessing SQL Server.
    From http://support.microsoft.com/kb/275561 :

    A database can be opened in one of two modes: Page Locking Mode Record/Page Locking Mode Page Locking Mode is essentially the former method of locking pages, that is, locking the entire 4 KB page whenever a user updates a value in a record.

    I don't maintain or control the programs in question but as far as I know they are all older Access programs that are using Page Locking Mode. I've already suggested they use row level locking but they've been unable to fix the issue. I fundamentally believe that this kind of locking behavior shouldn't even be allowed when connecting to a database at all.

  • (cs) in reply to dpm
    dpm:
    ChrisSamsDad:
    I think it was named after the DC Comics hero 'Thor' who had a golden mallet that amazing was the way he solved almost every problem that came along.
    #1 "Thor" is a Marvel comic, not DC.

    #2 Mjolnir is not made of gold, or even gilded.

    #3 The God of Thunder does not use his hammer as a first resort. For example, the competitions against the frost giant Utgard-Loki.

    Ah, well, you see the difference is that I HAVE A LIFE.

  • EatenByAGrue (unregistered)

    And of course the real WTF is that they trusted something that mission-critical to a guy right out of college, and provided what sounds like no testing support whatsoever.

  • Engywuck (unregistered) in reply to ChrisSamsDad
    ChrisSamsDad:
    dpm:
    ChrisSamsDad:
    I think it was named after the DC Comics hero 'Thor' who had a golden mallet that amazing was the way he solved almost every problem that came along.
    #1 "Thor" is a Marvel comic, not DC.

    #2 Mjolnir is not made of gold, or even gilded.

    #3 The God of Thunder does not use his hammer as a first resort. For example, the competitions against the frost giant Utgard-Loki.

    Ah, well, you see the difference is that I HAVE A LIFE.

    The one true answer would have been sth like here: Have you ever kissed a girl?

  • rixed (unregistered) in reply to Zomby

    The problem is not the tool but the pointy haired dumbs that took the decision to use it for another purpose.

  • airdrummer (unregistered) in reply to Eric

    more dilbertian;-)

  • pong (unregistered) in reply to ChrisSamsDad
    ChrisSamsDad:
    dpm:
    ChrisSamsDad:
    I think it was named after the DC Comics hero 'Thor' who had a golden mallet that amazing was the way he solved almost every problem that came along.
    #1 "Thor" is a Marvel comic, not DC.

    #2 Mjolnir is not made of gold, or even gilded.

    #3 The God of Thunder does not use his hammer as a first resort. For example, the competitions against the frost giant Utgard-Loki.

    Ah, well, you see the difference is that I HAVE A LIFE.

    This is coming from the guy who wasted three paragraphs trying to explain that the maxim "To the man who only has a hammer, everything he encounters begins to look like a nail." was something out of a comic book.

    You pasted the link. Now go read it: http://en.wikipedia.org/wiki/Golden_hammer

    captcha: paratus. Because you should be.

  • Jean Naimard (unregistered)

    We do precision guesswork.

  • (cs) in reply to pong
    pong:
    ChrisSamsDad:
    dpm:
    ChrisSamsDad:
    I think it was named after the DC Comics hero 'Thor' who had a golden mallet that amazing was the way he solved almost every problem that came along.
    #1 "Thor" is a Marvel comic, not DC.

    #2 Mjolnir is not made of gold, or even gilded.

    #3 The God of Thunder does not use his hammer as a first resort. For example, the competitions against the frost giant Utgard-Loki.

    Ah, well, you see the difference is that I HAVE A LIFE.

    This is coming from the guy who wasted three paragraphs trying to explain that the maxim "To the man who only has a hammer, everything he encounters begins to look like a nail." was something out of a comic book.

    You pasted the link. Now go read it: http://en.wikipedia.org/wiki/Golden_hammer

    captcha: paratus. Because you should be.

    A) ONE paragraph, and B) I didn't paste that link, I put one in to the Anti-patterns website (and I did it properly so that it actually was a link). See, I can do pedantic too! But weren't we talking about spreadsheets not comic books?

  • NeverYouMind (unregistered)

    I work at an insurance broker.

    Amongst other products, we handle performance bonds and similar bond products.

    We have one client who literally has hundreds of bonds to cover their work locations (cell phone towers). Each of this must be renewed annually.

    Years ago, someone from the client gave the account manager an Excel spreadsheet to help track the bonds for certain auditing purposes. The account manager gave this spreadsheet to someone on his support staff to actually keep track of it. She does the actual work of entering and removing the appropriate bonds from the spreadsheet each month.

    You may have already guessed where this is going. Every month, IT gets a phone call... a formula is messed up, usually because of how she has inserted and/or deleted rows.

    She no longer has a copy of the original spreadsheet, and neither does the Account Manager, nor the guy who replaced the guy he got it from. She never saves an old copy for reference. She has never had Excel training. Of any sort. She refuses to get Excel training. When we schedule her for mandatory Excel training, she gets out of it. She insists she doesn't need Excel training. Her immediate supervisor refuses to mandate that she gets Excel training "or else" because she insists she doesn't need it... And she insists that it's IT's fault that the spreadsheet gets messed up every month because we never fix it right.

    sigh

    Now, the real WTF:

    The client is about to provide us with a new handy dandy program to do all of this for us (from the description, I suspect it's in Access, but I haven't seen it yet) and it's not in Excel.

    She is refusing to use it because she knows Excel so well.

    sigh

  • Vic Tim (unregistered) in reply to airdrummer
    airdrummer:
    more dilbertian;-)

    I read this too fast-- it looked like Dilbertarian. I think I like that even better. Meritocracy and guns.

  • London Contractor Mart (unregistered) in reply to cod3_complete
    cod3_complete:
    Anonymous Coward:
    cod3_complete:
    Dirk Diggler:
    cod3_complete:
    'Actually this is still a HUGE WTF because Access will open an exclusive lock to any table that it connects to'.
    I think you are incorrect. If this was a troll carry on.

    I'm not doing hand waving or theorectical talk here Dirk. I'm speaking from direct experience with having broken Access programs locking up ENTIRE tables they connect to and I'VE GOT THE ERROR LOGS FROM DATABASE CONNECTIONS TIMING OUT TO PROVE IT!

    Then you're doing something else wrong. Access [configured correctly] doesn't exclusively lock tables when connecting to a [configured correctly] SQL Server, and the suggestion that it does it comical. Maybe it's time to look for a new career?

    You think I'm incompetent but I'm not. The Access program that causes me so much grief ISN'T MAINTAINED OR CONTROLLED BY ME! I would've dumped this monstrosity long ago. Think hard before you assume next time.

    Addendum (2008-12-02 17:34): I just wanted to point out that I already reccomended he attempt to configure Access to use record-level locking but this was to no avail.

    Actually, I think you'll find that's the JET database engine, nothing to do with the Access Front End if ADO is used to connect to the remote database. (SQL Oracle etc).

    Access is not a bad tool for RAD application GUIs... Although I must admit, I'd never use it!!

    I'll start a fight now, for a laugh as your incompetance is amusing!! Hahahahaahha!

  • (cs) in reply to London Contractor Mart

    I guess in London the lack of decent dental plans rots the teeth as well as the brain :-)

  • thorn (unregistered) in reply to tragomaskhalos

    yyyyyyyyyyyyyyyep.

  • thorn (unregistered) in reply to Andy Goth
    Andy Goth:
    This reminds me of Isaac Asimov's The Machine that Won the War (read it here).

    Addendum (2008-12-02 12:27): Seriously, read it! It's an excellent story, rife with delicious WTF material, and it's only four pages long.

    thanks for the story link!

  • (cs)

    The real WTF:

    Maxim (is that a person's name?) is hired to program financial applications and he doesn't even know what yield is!

    I guess Maxim never had a savings account either?

  • JM (unregistered)

    I got a good one.

    A few years ago I was asked to synthesize the FX curve from the two interest rate curves underlying it. (For the layperson, once the spot FX exchange rate is determined by the market the forward rates - ie. those 1 week, 1 month, 3 months ahead etc - are solely determined by the 1 week, 1 month, 3 months interest rates in the two currencies.)

    This is a very simple concept but it is fraught with difficulties in implementation (at least if you want to do it right) because there are all sorts of subtle effects caused by such things as holidays in the two countries and the arcana of how the various interest rates are quoted.

    So, since I had done the same task a few years before hand (and also because I'm not entirely stupid) I - sotto voca - reused some old code from another site that was fully worked and tested but something I should definitely not have kept, as to do so was in violation of the confidentiality clauses in my old contract.

    Viola, 1 day later, delivered code.

    Or so I thought.

    I was working with a junior trader who I will call Josef (not-his-real-name).

    One month later, after many days of Josef saying "it's not right, you gotta do it this way" and me employing many polite versions of "are you quite sure you want to do it this bloody idiotic way that will work only when the moon is in the right phase?" I was sitting next to Josef on the trading floor while he tested my latest version.

    For those familiar with FX markets it happened to be Thursday 30 June, with the weekend on the way. Keep this in mind.

    After listening to his latest complaint I asked him to explain why he thought it was wrong, to which he replied "because the old system says so"

    I asked him to show me and he did.

    I looked at the screen of the old system. 6 currencies were displayed all showing a maturity ladder (spot, 1 week, 1 month, 3 months etc) [Really old FX hands can probably identify the old system at this point, let me just say I think it may have been misconfigured but the maturity ladder in that system does have this all-spot-dates-are-the-same flaw].

    My next words were: "Josef, why is 4 July spot in all 6 currencies? Particularly since 5 July is also a holiday in Poland and that means spot for the zloty is 6 July?"

    A small technical diversion. "Spot" means the day on which the currency trade settles, which means the banks have to be open - in both countries. Since the USD is the base currency, 4th July can never be spot, even in crosses like GBP against PLZ as they must settle against USD when crossed and it is required that the US banks also be open.

    Got that? 4 July is the ultimate impossible date, yet there was the old system happily using it.

    Further since 5 July was (that year) a holiday in Poland the spot date for any trade involving the zloty had to be moved a further day.

    My original code took care (perfectly and with great performance) of all this arcana and I'd managed to preserve that behaviour through all of the hacks Josef had forced on me for the previous month.

    Josef asked me what I meant and I spent about 10 minutes explaining my understanding of how all this stuff worked.

    Josef's response was "no, no not like that, like this" explaining a very simplistic view. Then he thought for a moment and called his head trader over and repeated his view asking for validation.

    The head trader's response was priceless. "Well if you wanna knock it out for yuks in a spreadsheet you might do it like that, but ...... [repeat of my view]"

    I went back upstairs in triumph and restored the month old original. Josef didn't last long (but that's another story).

  • Anon (unregistered) in reply to Ollie Jones

    [quote user="Ollie Jones"]

    Y'd think a bank might have an auditor on staff. [quote]

    LOL.... you must never have worked at a bank.

  • ich (unregistered) in reply to ShatteredArm
    ShatteredArm:
    What's so hard about Yield? Isn't it just the dividend divided by the share price?

    Yes and no. The above is true for stocks (shares) but not for bonds. In that case

    • The nominal yield or coupon yield is the yearly total of coupons (or interest) paid divided by the Principal (Face) Value of the bond.

    • The current yield is those same payments divided by the bond's spot market price.

    • The yield to maturity is the IRR on the bond's cash flows: the purchase price, the coupons received and the principal at maturity.

    • The yield to call is the IRR on the bond's cash flows, assuming it is called at the first opportunity, instead of being held till maturity.

    So there is some level of complexity to yield calculation

  • eric bloedow (unregistered)

    this made me think of a Tom Clancy novel, "debt of honor": there's a scene where someone causes a big stock market crash by selling lots of Citibank stock. since Citibank just happened to be one of the stocks that "trend analysis" programs watch double-closely, that made ALL the auto-trading programs think there was a general downward trend, so they all started selling EVERYTHING...

Leave a comment on “The Great Excel Spreadsheet”

Log In or post as a guest

Replying to comment #:

« Return to Article