This story begins in 2008, every month since we introduced currency rates in the Data Warehouse I had to manually copy the rates from an excelsheet pubished on a crap web site. Around 2008 I was fed up with this and created a procedure that use CURL to grab the Excel sheet and updated the Data Warehouse. This worked fine until I was on vacation when a summer stand-in at the finance added comments and rearrage the rates on the Excel.They call me from the office when I was on the autobahn in southern Germany, that is when I invented speed debugging. Back at the office I had a meeting with the finance and made them promise never ever change the Excel format of the currency rates without telling in advance. (It turned out noone else progmatically grabbed and interpreted the Excel sheet.)
Later at 2016 when I was the enterprise lead information architect, I together with a finance guy created a system grabbing the currency rates from Bloomberg and published them in MS sharepoint, which was and is the company's platform for data interchange. We were ready to go into production when the finance department responsible for currencies was moved over Seas. The new department manager stopped my new 'rate system'. (They created a currency system of their own. Better? My system you can see, the new system you will never see). Anyway the rates were published on Sharepoint and I created a Sharepoint API import system for the Data Warehouse. At that time I had been removed as an IT architect. The new information architects decided it was forbidden to fetch data directly from Sharepoint! Data must only be extracted by APIs developed by a consultant. I found it a bit strange not to use Sharepoints own API for sharing, but OK I can use the new APIs. But the management of the Data Warehouse did not allow me to use the new API. I find it a bit comic I who was behind the idea and developed the APIs for programatically extract the currency rates was in the end the only one who was not allowed to use the currency rates API when it was finally put into production.
So here we were at the end of 2023 failing to update the currency rates using my old semi automatic system from long time ago. First problem, my old system transfers the rates to the data warehouse with FTP. Now at the end of year someone has firewallblocked the FTP transfer to the Data Warehouse. It took quite some time to realize this. If FTP does not work I had other ways to transfer the data to the data warehouse. Now we run the insert procedure and it failed, now the Data Warehouse was blocked, if there is a problem with the currencies the Data Warehouse is blocked. W-T-F was the reason for the failure. After a long time I realised there was a problem with the input. At this point a colleague come to the rescue. Before sending the currency rates to the Data Warehouse the excelsheet is converted to a CSV file. My colleague told us you must use Swedish locale when doing the CSV conversion, something I had forgot, the (swedish) colleague sent me a correct CSV file. Now when we tried to insert the currecies we were told the Data Warehose was blocked due to invalid currencies. Of course we have a force option, I use the more colorful 'gogogo', adding gogogo we were able to insert correct currencies.
Now in hindsight, the 'currency procedure' is not that bad, but it is brittle. We have some work to do until next month's end.
p.s.
The result of last currency intake, (the last row is the important one).
| STATUS | |
| DISTINCT_VALUES | 1: AVG AND EOP HAVE DIFFERENT VALUES FROM LATEST PERIOD |
| NEW_RATES | WARNING: TOTAL NUMBER OF EXCHANGE RATES IS DIFFERENT FROM PREVIOUS PERIOD |
| MAX_EOP | 1: EOP SINCE LATEST PERIOD HAS NOT INCREASED MORE THAN 20% |
| MIN_EOP | 1: EOP SINCE LATEST PERIOD HAS NOT DECREASED MORE THAN 20% |
| MAX_AVG | 1: AVG SINCE LATEST PERIOD HAS NOT INCREASED MORE THAN 20% |
| MIN_AVG | 1: AVG SINCE LATEST PERIOD HAS NOT DECREASED MORE THAN 20% |
| MAX_EURX | 1: EURX SINCE LATEST PERIOD HAS NOT INCREASED MORE THAN 20% |
| MIN_EURX | 1: EURX SINCE LATEST PERIOD HAS NOT DECREASED MORE THAN 20% |
| DISTINCT_DATE | 1: EOP,AVG,EURX ALL HAVE A NEW DATE DIFFERENT FROM LAST PERIOD |
| ACAB_DATE | 1: ACAB HAVE NEW DATE FROM LATEST PERIOD |
| ACAB_VALUE | 1: ACAB HAS DIFFERENT VALUE FROM LATEST PERIOD |
| BUD_DATE | 1: BUD HAVE NEW DATE FROM LATEST PERIOD |
| BUD_VALUE | 1: BUD HAS DIFFERENT VALUE FROM LATEST PERIOD |
| CURRENT_PERIOD | 1: LATEST PERIOD IN EXCHANGE_RATE IS CORRECT |
