Connect with us, free of charge, to explore how we can help you, and to give you an idea of cost.

No obligation, no spam.   Ask us ✉

Create bank-grade internal reconciliations and complex Market Risk reporting tools using Excel.

The Reserve Bank of NZ has decreed that all foreign owned banks in NZ must be able to separate from their parent company, should a major financial contagion event cause the reserve bank to trigger BS11 separation. The big international banks have centralised their systems to a single instance, which runs somewhere in their global network of companies. Should BS11 separation be triggered, these functions will need to be replicated back to NZ, resulting in huge costs and enormous duplication of systems and effort. Since the risk of invoking BS11 is deemed low, one of the large banks decided to use Excel to build tools for Reconciliations, Market Risk warning and others thereby minimizing costs.

Excel handles this easily. For example, we built tools to reconcile two trading systems, containing 300,000 daily records with some 30 fields each, in just over a minute. Yes, you read that right, such a large volume of data, in so little time (and half of that time is for loading the files). If you’d like to know how to write code to achieve this type of performance, have a look at our Excel vba performance tips. Also, did you know that you can deal with many more records than the 1 million row limit of Excel? Simply use Data Queries (it is in Excel, no need for Ms-Access).

Best of all is the high productivity that coding in Excel brings. It is phenomenal how much functionality can be delivered in record time using Excel, if you know what you’re doing. Using a technique called Function Point Analysis (quantitative software metrics) this article shows how high the productivity was, and how much better it is compared to Python (and Python is more productive than many other languages).

Do you have a similar issue? Do you need reconciliation tools?
Need financial forecast modelling tools? Are you having Excel performance issues?

Save time & cost. No obligation, no spam.   Ask us ✉

Rolling up 330 monthly Excel project reports in a program summary in minutes. Without automating the process, this would be impossible.

The Programme Office (PO) for the largest Telecoms program in New Zealand, needed a reporting tool. No on-line tools were available at the time, but even if there had been, it would have taken at least 18 months for the IT-department to roll it out. At the start, the PMO team of 4 had the daunting task of having to roll up and report on 130 projects in 4 days following month-end, and write the synthesis. We automated this, and collating the reports was as simple as dropping the excel reports in a folder for the month, clicking on a button, and within a few minutes it was collated. In addition, the Excel automated tool loaded and combined the Oracle Financial data of actuals and forecasts for each project. Can you imagine the team's sigh of relief? What time saved, and one version of the truth. 18 Months later the same solution was processing 330 reports! In the mean-time the IT department took another 3.5 years to develop an MS Project server solution, while this simple tool just carried on.

Do you need roll-up many spreadsheets a month?
Need reporting tools?

Save time & cost. No obligation, no spam.   Ask us ✉

Generating several million records pricing information for a bus company in 15 minutes, or having to create and enter this manually?!

A bus company was implementing a contactless Tag-On/Tag-Off (TOTO) card system. The nature of TOTO is that for every bus route of N-stops, you end up with N*(N+1)/2 TOTO price combinations. For example, on a route with 5 stops, you have 15 such combinations; on a route with 40 stops you have 820! For all of Wellington (NZ), across all routes, there were close to 900,000 such records. The contactless card system vendor suggested to enter this by hand. Yes, you read it right. So, we built an Excel VBA tool, which loads in the GIS route/stop information, crafted 140 configurable rules to cater for special zoning constructs devised by the various political masters over the past 50 years, and pressed the button. The error rate was 1 defect per 150,000 records (5 Sigma; 6 Sigma is 1 in 250,000). With some tuning, second and subsequent runs had no more errors. For each pricing or GIS route update, it was just re-run, and literally 15 minutes later the new data entry table is ready for uploading into the card system. The best came when the card system was rolled-out to Auckland, where nearly 4 million such records are needed. We generated that in 5 batches in just over an hour. Can you imagine the dread the operations people felt of having to enter this by hand? Can you imagine the relief when changes to prices or routes routes are needed?

PS: With the performance tricks we now know, this application would run in maybe 2-3 minutes max, for all 4 million records.

Do you need to generate configuration, pricing, tarifing or similar data?

Save time & cost. No obligation, no spam.   Ask us ✉

Selecting specific honey drums out of thousands, to create a consistent flavour per batch - in 4 minutes by automated Excel, versus 9 hours by hand by a human expert.

A honey producer had an issue that it took their operations manager 9 hours to determine and select a set of drums from their rolling collection of over a thousand drums filled with raw honey, to create a constant tasting batch. This became untenable. But how to solve this? The mathematical combinations were 55... followed by so many zero’s, that no computer could crunch it in the time that the universe has existed. We were asked to have a look at this, and came up with the solution to randomly select combinations, which stops when the results are within a 1% margin. As it transpired, within 20,000 randomly selected combinations of drums, the variance stabilizes at around 1%. It took 17 days to build this, and each run now takes 4 minutes. At the end of the run, the whole administration of which drums were selected and used, is automatically recorded, including the date, time, and user performing the run. Furthermore, the code considers sugar level regulations for the different target markets, as well as optimum aging of the honey in the drums. Can you imagine how the operations manager’s life changed! And consider what this means for the company, to have this embedded and part of their processing assets (instead of in staff's heads, and who may seek other work) !

Do you have a business or other optimisation problem?

Save time & cost. No obligation, no spam.   Ask us ✉

Confronted by a showstopping event at the start of a roll-out of new busses, routes, and road-side information, we created a workaround data bridging solution in days, averting a public relations drama from happening.

In 2018, the Greater Wellington Regional Council rolled out a new radically overhauled bus services, new busses, new routes, and thus new roadside information. To do this, they used a new system to provision data to a broad range of systems supporting the bus services. After the first stage went live, the team hit a show stopper snag in the data conversion, preventing them moving the next stage of roll-out. The issue would take months to fix. What to do? We offered to give it a go in Excel. Two long days later, Excel with VBA automation had saved the day, reading the corrupted data and spitting out the correct data. Imagine the relief for the CIO and the development team! The roll-out could continue and a few months later, once the system was fixed, the Excel solution could be retired. What a win.

Do you have a data conversion issue? Have a showstopper that needs an urgent solution?

Save time & cost. No obligation, no spam.   Ask us ✉

A rapidly scaling 25 person start-up, reached a crunch, when monthly invoicing started to take nearly a month. We automated their Excel based billing, cutting invoicing time to hours, providing breathing space for the next three years.

Recently a start-up company with 25 staff, hit the snag that they could no longer grow, as invoicing was a single person process which had become a bottleneck. Processing the invoices took almost a month, by which time the next month arrived. Clearly further expansion of the business was in peril.

This company's invoicing is done using Excel, by copying and pasting records into billing statements, while remembering any of the many client exceptions and specials.

Well within a month, we built them a VBA automated solution, including the configuration data describing all client exceptions and specials. Generating the invoices now takes 15 minutes, and human assessment and tuning can be done in a few hours. Adding exceptions is as simple as creating a record in a lookup table, and final entry in the accounting system is helped by only having to enter summary numbers for each type of service. Thus, the whole process now takes less than a week, and further improvements can reduce this to just a few hours. All the complex rules are now included in the Excel program, and are no longer required to be in the heads of individuals. Processing can now be done by more than one person in paralel. Can you imagine the stress this removed from the billing peron and management, and the time this has freed-up to attend to growth?

Drowning in spreadsheet work? Need to free-up time?

Save time & cost. No obligation, no spam.   Ask us ✉