Wouldn't think so. Each transaction ID would represent a unique transaction?
>Other double payments made by mistake included times that the state received multiple invoices.
I would assume each paid invoice was a new transaction ID. The real problem seems like there are two invoices being paid. Not that there are two transactions (which seems like just a symptom). It's possible that each invoice even has it's own ID.
This presumes all of the transaction data is centralized one place. 99% of the battle was probably getting that data aggregated, then performing the analytics, which is chalked up to this simple SELECT statement.
This is exactly the issue here. I worked as a contractor for the State Department for a couple of years on software that basically held the record of every dollar spent over the last 20 years.
What seemed like a very small and simple problem initially revealed itself to be massive problem that even a team of 35 struggled to maintain. The purchase history for any given item spanned multiple systems with completely different topologies of data glued together by, literally, tens of thousands of lines of SQL.
The project had been around for ~17 years when I worked on it and while data was landed in a final format where a query like the above could have been done, I wouldn't bet any serious money that the calculations were correct.
Some five or six multi-million dollar rewrites had been attempted, but could never be done.
Not to say Ohio's system is that this level, but I doubt there's a giant table sitting somewhere that such a simple query could be applied to.
I like to think that somewhere in all that SQL was an Office Space-esque line of SQL syphoning off a few cents per transaction, but was never able to find one :)
A recent Masters graduate trained in data science with Pandas, and also accounts payable work experience, got hired very very fast on good terms when the hiring department found out how facile and fast the Pandas python approach was to finding duplicate or other problematic payments. Maybe the novelty will wear off, but right now, this is absolutely practical.
Would love to see this skill set put to work automating away a lot of accounting functions. I know too many accountants (Controllers, specifically) doing mind numbing work.
The mind numbing work is what give them a salary to provide for their families. They might not like unemployment as an alternative.
I've met certified accountants whose daily job was to take an excel spread sheet, add 1% to every cell with a number, then return to the client. It's not automated but not because it can't be automated.
Automation is coming regardless. Political and economic systems must adapt to soften the blow. People will need to find purpose and meaning elsewhere than a job that can be automated. I suggest family, community, and self development, but to each their own.
As a disclaimer, I have recently become enchanted with automating accounting work due to hearing about the problems accounting functions face. I'm a tad biased.
You'd think that but nope. Well, not as quickly or as strongly as one would anticipate.
Large banks, automated trading shops and assimilated have automation, because it's their jobs. Other things don't.
Recruiting chartered accountant is easy and cheap. Recruiting developers that can deliver is not as easy or as cheap. Bear in mind that it's not a tech environment so no source control in place, no servers to deploy to, won't be easy to execute.
I worked in finance too, trading platforms in investment banks and prop trading shops. There hasn't been accountants there for a long time. Places that still rely on manual labor are a very different kind.
Agreed. In those cases, where the business is stubborn, you might have to consume the entire business [1] [2] to be able to enact automation in the underlying functions. That's also the question, right? What tools can you develop (Similar to Brex and Stripe) that make it exceedingly more simple for upstarts to compete against the entrenched? If I can't sell to your internal divisions, maybe I go get some VC and compete against your whole business. Lots of margin hiding as inefficiencies in the economy still, just have to go get it.
Remind me, saw a fintech company with almost a billion in VC funding earlier this year. Basically a bank doing loans.
The whole business was based low cost labor taking whole days to evaluate stuff manually and write risk reports. Crazy thing is they were hiring loads of developers and data scientists, but the core of the business is all manual.
As someone who passed the cpa exams, worked at a large bank, and now does data science elsewhere, I strongly disagree. Many large banks are stagnant with legacy tech and processes. Many people do pointless excel work and will defend pointless excel work to the death.
There were multiple people, of varying skill, willing and able to automate significant chunks of this with introductory R/Python/VBA skills. It’s not a cool shiny front end app, but a few scripts here and there could save an FTE easily. But politics prevents these things from being adopted.
Cost and scarcity of tech talent is not a real issue for light officework. Servers, Source Control, etc. are missing the picture. You just need people with light programming capability and a little bit more agency and voila they’ll be 10x as productive as everyone else.
> I've met certified accountants whose daily job was to take an excel spread sheet, add 1% to every cell with a number, then return to the client. It's not automated but not because it can't be automated.
Well, that's just dumb. We can do better. Those accountants you met can do better!
At one company I worked with our external auditor knew SQL. My colleagues loved him because he would actually do useful queries that proved something instead of just verifying random bus tickets from Asia as certain others would do.
If you instead considered this an audit of the state's expenses, rather than a money-recouping initiative, then it's an impressive feat the state's money goes where it should be.
I'd be surprised if they audited 100% of state expenses. This seems to focus on invoices and not, for example, payroll. Even so, if it was $1b and only 117k of mistakes, that's just .0117%, which seems pretty good to me.
This was 117K was found in transactions during 8 out of 12 months in the year. The article doesn't specify the amount of money transacted during that time. It doesn't give us the total amount of transactions either.
It gives an estimate of 18 million in the total year so the closest estimate is 117k out of 12 million. Which is ~1% and seems like a win win. State employees and folks should feel good that they aren't fucking up very often and the analysts should feel good because functionally this number should go to 0. Operationally no idea what it would cost to maintain though. cough
So the article is a bit confusing, as it says in a year the state processes over 18 million dollars in transactions.
However the pilot program only analyzed transactions from Jan to Sept. For all state agencies (which seems impressive from an aggregation and governance stand point). With in that time period it found 117k in duplicates, so assuming that dollar/transactions are at a constant rate through out the year (they aren't).
117,000/(18,000,000 * (8/12)) ~ .975 % loss in state to vendor(?) transactions.
Put another way, with in the time period they studied assuming 12 million dollars in transactions were made, they can potentially recuperate roughly 1% of the total value transacted.
They process 18 million transactions per year, total, not 18 million dollars of transactions. And they only found 56 duplicates, i.e. a 0.0004% rate. (56 / (18e6 * 8/12))
For comparison there's a Kaggle dataset of 2 days of European credit card transactions from 2013. It contains 284k transactions of which 0.147% are fraudulent.
This 1000x difference can either be good since it means the state is mostly paying appropriately or bad since the detection is not as good as the credit card company's.
Whatever. I discovered and solved an orders scanning problem where blank pages were being paid for 5-8X in each order passing through the OCR scanner machinery. I saved that company well over $1M per year just by changing how they delimit scanned orders. Crickets.
The ultimate slap in my face came when some twit received an award because she created some awful MS Access DB for the CEO. She got extra money for it, too, like $5000. It pays to work in the C-Suite and generate tech debt.
I know the comments are all "how much money did they spend to find $117K in duplicate payments" and I am one of them. But if this system helps in identifying process/audit flaws then its a good thing.
48 comments
[ 2.9 ms ] story [ 62.1 ms ] threadI'll take 10% of savings
I'll take 9% );
8%?
Back to 10%
>Other double payments made by mistake included times that the state received multiple invoices.
I would assume each paid invoice was a new transaction ID. The real problem seems like there are two invoices being paid. Not that there are two transactions (which seems like just a symptom). It's possible that each invoice even has it's own ID.
What seemed like a very small and simple problem initially revealed itself to be massive problem that even a team of 35 struggled to maintain. The purchase history for any given item spanned multiple systems with completely different topologies of data glued together by, literally, tens of thousands of lines of SQL.
The project had been around for ~17 years when I worked on it and while data was landed in a final format where a query like the above could have been done, I wouldn't bet any serious money that the calculations were correct.
Some five or six multi-million dollar rewrites had been attempted, but could never be done.
Not to say Ohio's system is that this level, but I doubt there's a giant table sitting somewhere that such a simple query could be applied to.
I like to think that somewhere in all that SQL was an Office Space-esque line of SQL syphoning off a few cents per transaction, but was never able to find one :)
"Don't be snarky."
https://news.ycombinator.com/newsguidelines.html
I've met certified accountants whose daily job was to take an excel spread sheet, add 1% to every cell with a number, then return to the client. It's not automated but not because it can't be automated.
As a disclaimer, I have recently become enchanted with automating accounting work due to hearing about the problems accounting functions face. I'm a tad biased.
Large banks, automated trading shops and assimilated have automation, because it's their jobs. Other things don't.
Recruiting chartered accountant is easy and cheap. Recruiting developers that can deliver is not as easy or as cheap. Bear in mind that it's not a tech environment so no source control in place, no servers to deploy to, won't be easy to execute.
Give me a lever long enough.
[1] https://a16z.com/2011/08/20/why-software-is-eating-the-world...
[2] https://techcrunch.com/2016/06/07/software-is-eating-the-wor...
The whole business was based low cost labor taking whole days to evaluate stuff manually and write risk reports. Crazy thing is they were hiring loads of developers and data scientists, but the core of the business is all manual.
There were multiple people, of varying skill, willing and able to automate significant chunks of this with introductory R/Python/VBA skills. It’s not a cool shiny front end app, but a few scripts here and there could save an FTE easily. But politics prevents these things from being adopted.
Cost and scarcity of tech talent is not a real issue for light officework. Servers, Source Control, etc. are missing the picture. You just need people with light programming capability and a little bit more agency and voila they’ll be 10x as productive as everyone else.
Well, that's just dumb. We can do better. Those accountants you met can do better!
I'm curious of it's a net win, though, once all the costs (and benefits) are considered.
This was 117K was found in transactions during 8 out of 12 months in the year. The article doesn't specify the amount of money transacted during that time. It doesn't give us the total amount of transactions either.
It gives an estimate of 18 million in the total year so the closest estimate is 117k out of 12 million. Which is ~1% and seems like a win win. State employees and folks should feel good that they aren't fucking up very often and the analysts should feel good because functionally this number should go to 0. Operationally no idea what it would cost to maintain though. cough
However the pilot program only analyzed transactions from Jan to Sept. For all state agencies (which seems impressive from an aggregation and governance stand point). With in that time period it found 117k in duplicates, so assuming that dollar/transactions are at a constant rate through out the year (they aren't).
117,000/(18,000,000 * (8/12)) ~ .975 % loss in state to vendor(?) transactions.
Put another way, with in the time period they studied assuming 12 million dollars in transactions were made, they can potentially recuperate roughly 1% of the total value transacted.
For comparison there's a Kaggle dataset of 2 days of European credit card transactions from 2013. It contains 284k transactions of which 0.147% are fraudulent.
This 1000x difference can either be good since it means the state is mostly paying appropriately or bad since the detection is not as good as the credit card company's.
god damnit you're right. whoops
To be fair, I wouldn't be surprised if there are additional benefits beyond the $117k. For example:
- Reducing the attack surface of fraudulent invoicing.
- Retaining staff/skill that are useful in other projects not discussed in the article.
- Seeding awareness/interest for similar projects in other departments of the state government.
The ultimate slap in my face came when some twit received an award because she created some awful MS Access DB for the CEO. She got extra money for it, too, like $5000. It pays to work in the C-Suite and generate tech debt.
https://en.wikipedia.org/wiki/Reconciliation_(accounting)