Fun with exchange rates and broken software


  • Trolleybus Mechanic

    The setup

    One of the accountants comes up and asks if I could help her out. She has a number of foreign currency transactions she needs to input, but the exchange rate dictionary in the database doesn't have data for the dates she needs - she's working on past periods, probably prior to the accounting database being set up for the client.

    Step 1: Try the obvious

    "Have you run the updater?"

    For an extra fee, the software provider offers the service of providing exchange rate updates - it's a service we subscribe to, naturally. Yes, she has tried it and no - it didn't work.

    Step 2: Try the fancy management software

    The accounting package we use can be centrally managed with a separate program that performs bulk operations on many databases (each client has a separate DB). One of the operations involves updating the exchange rate dictionaries. I do it - does not solve the problem.

    Step 3: Consult the internet

    "Maybe we're doing something wrong..." I think.

    I pull up the official website and am informed that the exchange rate update mechanism only provides rates for 30 days prior. The sole support forum for the software does not offer any user-discovered workarounds either.

    I had been hoping that the central management mechanism I mentioned in step 2 would perform a bulk update from a source DB to the target DB, but it turns I was overly optimistic. There is no way to automatically update exchange rates older than 30 days.

    Step 4: What if we throw SQL at the problem?

    "Fine," I think, "if that's the way you want to play it, I'll just dump the data from where I have it to where I need it."

    I pull up the schema for the exchange rate table and, wouldn't you know it, identity fields are for wimps. Sure, the table has a bog-standard, incrementing integer id field - except, it is not actually specified as an identity. The software only runs on SQL Server, by the way. At this point, I could develop a data transfer solution that would integrate the import without breaking anything (which would be an excellent opportunity to find out how their update mechanism dervies its PK values; and just how braindead the solution is) - or I could shrug and say that manual input is the way to go.

    Bonus

    It is pretty much an immutable principle here in Poland that whenever you're dealing with a foreign currency transaction, the amount should be recorded in PLN, at the rate published by the National Bank of Poland for the working day directly preceeding the date of the transaction (the NBP doesn't publish rates for non-working days, incidentally). This requirement is specified in both income tax (PIT and CIT) Acts, the VAT Act and anywhere else you damn well please. It's been like this forever and shall remain so in the foreseeable future.

    Guess what date our accounting software takes the rates for? The date of the transaction, of course! Why on Earth would it do anything different? And no - you cannot set it up to act otherwise.

    In trying to solve my immediate problem, I came across this issue being raised on the aforementioned support forum - five years ago. Apparently, the company was going to fix it in one of the upcoming updates, but eventually decided not to, because it turned out to be too hard to implement.

    Seems that "WHERE ExchangeRateDate < TransactionDate" was too difficult. Sure, some checks for missing data would be in order here, but even without them the software would perform as expected 90% of the time (and you're meant to perform manual sanity checks anyway; that's what accountants get paid for). To make matters even funnier, the software also does HR and payroll and therefore the DB has calendars with all weekends and holidays built in - whether the actual HR module is enabled or not.

    I am left wondering if it's a case of them having really crappy developers, or was it simply that they couldn't be arsed. Either way, the way their software handles exchange rates continues to be wrong, five years on.



  • It sounds like your accounting convention is TRWTF.


  • BINNED

    @Captain said in Fun with exchange rates and broken software:

    It sounds like your accounting conventions is are TRWTF.


  • Discourse touched me in a no-no place

    @GOG said in Fun with exchange rates and broken software:

    I am left wondering if it's a case of them having really crappy developers, or was it simply that they couldn't be arsed.


  • Trolleybus Mechanic

    @Captain I take it you are referring to the date issue? Au contraire, my friend.

    I must point out - again - that the NBP only publishes rates for non-working days: Monday through Friday, excepting holidays. There is no requirement that business transactions take place only on such days. See where this is going?

    Moreover, whilst we normally deal with recording stuff on the books following the end of the month when it took place, it should be possible to record events as they happen. In order to do so, you would need to know the exchange rate and you will not know it until it gets published. Here's where using previously published rates comes in handy, no?

    I'll readily concede that there are some accounting conventions that are dumb, but most of them are surprisingly sane. This is one of latter.



  • @GOG said in Fun with exchange rates and broken software:

    Seems that "WHERE ExchangeRateDate < TransactionDate" was too difficult. Sure, some checks for missing data would be in order here, but even without them the software would perform as expected 90% of the time (and you're meant to perform manual sanity checks anyway; that's what accountants get paid for). To make matters even funnier, the software also does HR and payroll and therefore the DB has calendars with all weekends and holidays built in - whether the actual HR module is enabled or not.

    I am left wondering if it's a case of them having really crappy developers, or was it simply that they couldn't be arsed. Either way, the way their software handles exchange rates continues to be wrong, five years on.

    Maybe the tech debt for the software has been accumulated to be level that implementing that will be too risky and it'll hopelessly breaks all the existing reports that they'll be in no way become "sync" again. The situation got worse when you have customers that have 7+ years of data to be converted if you need to change the schema. (Our government mandates all companies need to make up to 7 years of financial records accessible on request)

    This is one of the reason that some improvements of the software I've been writing got recalled.

    Btw, in one case the company decided to branch the source in order to make "breaking-improvement" to freshly installed system only, and let the old customers use the old code path. Trying to sync bugfix between two versions of source is torture.



  • @GOG said in Fun with exchange rates and broken software:

    I am left wondering if it's a case of them having really crappy developers, or was it simply that they couldn't be arsed. Either way, the way their software handles exchange rates continues to be wrong, five years on.

    Does it have "Comarch" written anywhere in the credentials? If it has, then I bet on both.


  • Trolleybus Mechanic

    @wft Surprisingly, no.

    That said, the contract for maintaining the Social Insurance system over the next X years (six, I believe) is coming up for tender and guess who's entered a bid...


  • Trolleybus Mechanic

    @cheong said in Fun with exchange rates and broken software:

    Maybe the tech debt for the software has been accumulated to be level that implementing that will be too risky and it'll hopelessly breaks all the existing reports that they'll be in no way become "sync" again. The situation got worse when you have customers that have 7+ years of data to be converted if you need to change the schema.

    I'll grant it's possible, however - based on my knowledge of the software - I think it unlikely (I've written several apps interfacing with it, including an electronic bank statement parser that deals with the exact issue on hand and - surprise, surprise - works as expected.)

    In this particular case, we are talking merely about a suggested date that may be altered by the user and is stored as a plain old DATETIME value - the default suggestion is simply wrong and must be altered, by hand, for each foreign currency transaction.

    Whilst I haven't had the privilege of examining the codebase, I see little reason why a different implementation of the same mechanism - suggest a date based on a user-provided date - could not be introduced either as a replacement or option. This really isn't rocket science, nor does it involve arcane inter-module dependencies (unless the software is really broken).

    Actually, the most likely reason is that in the meantime the company has introduced a new line of the same software (when in doubt - rewrite; amirite?) and their motivation for maintaining the old line is dwindling.

    In truth, we have little motivation to move to the new line - which does not immediately appear superior tech-wise, has worse licensing terms and will break our exisiting solutions - as yet. We will eventually, once other firms have had the opportunity to discover the really interesting bugs.



  • @GOG you work for ZUS?


  • Trolleybus Mechanic

    @wft Nah. I just like to stay on top of things, especially if I might be called upon to deal with the fallout.

    Given how well the new "interactive" version of Płatnik (for those watching at home: that's the government-mandated software used to report Social Insurance) is turning out - literally not a month goes by without critical errors making it impossible to send the mandatory reports - I shudder to think what happens if the maintainer is changed.

    Perhaps I should move to greener pastures before that happens.


Log in to reply