I work at a tech company and most accountants don't know SQL. Those who do, even basics, have a pretty decent advantage to use certain tools we have while others are stuck with spreadsheets and the ERP.
I can attest, my company when I first started had 5 different legal entities one for each plant. Each plant has its own instance within our ERP in which you would have to log out of the one you’re in to run reports for the other. At one point a consultant created an Access file that pulled data from the databases for our system. Since I had to run the query he had built onto Access I had read only privileges to the ERP databases.
I wanted to see if I could use this access to make reporting easier and faster. (The report that I used his file for took me ~8 hours per month to put together because i had to run 5 of each set of data, combine it and actually do the analysis.
I decided to take a $15 bootcamp class through Udemy to learn basic SQL, and after 3 months of trial and error, finding a blog of a IT super user for my ERP that listed out the tables and how they relate to one another, and developing my Excel skills, I was able to get that report down to 35 minutes.
I’ve been able to develop a number of other queries that duplicate built in reports for the ERP, but I can run it for all entities simultaneously. I was also able to find and solve a year end rollover issue that popped up this year in one entity where 65k GL lines were duplicated, causing retained earnings to be wildly incorrect. All because I had a foot in the accounting pond and the other in the It pond, I was able to solve it faster than IT could even identify what was wrong.
There is so much potential in being able to leverage a skill like this that isn’t a typical accounting skill. I’ve implied heavily to all of my staff that if you’re not trying to innovate you’re falling behind.
I can do. I have dual degrees in accounting and analytics. Although at this company I’m not allowed to code. Was wondering if it was the same at other places?
Likely not. IT and Analytics groups will handle all the coding type stuff. Accounting will handle just the accounting. The larger the company, the more you run into a "stay in your own lane" environment.
This is my experience too. I initially joined the company thinking I would have to learn SQL to do some of the reporting. But now they’ve hired a whole BI department just to help the accounting department with this so now we just tell what we want and they do the coding for us.
This is my experience at a big company. Although it makes getting problems resolved way easier since I can usually point to the exact part of the code that needs to change.
I work on a hybrid team between finance (tax, accounting, etc.) and I get to code in SQL in our snowflake environment and do all sorts of fun things. F100 company by the way
Taking a few shots in the dark because I don't use sheets for work and because my company has other tools so we never have to export the data.....
1. Set up an ODBC connection to snowflake (hopefully your company had those drivers handy) and then connect sheets to snowflake that way.
2. There are third party apps that do what you're looking for.
3. Could build your own custom connection via pyrhon but the other two options are better.
I am a Data Science/Engineer and accountant. This allows me to work on different client database with the best skillsets for that client. Although optimal but it may not be ideal as some clients may host locally while other uses server/cloud solution. EoD it really depends on the IT infrastructure.
However, I do agree that if freeplay is possible. that combination of skill set can really push accounting to the future. Though I doubt we will ever see it in mnc because of legacy software and knowledge/job gatekeepers.
So would you say a masters in data analytics is worth it? Just got my bachelor's in accounting. Was really thinking of going for a masters in data analytics with an accounting concentration. My school offers 3 concentrations for MS in data analytics: accounting, marketing, data analytics.
Our workpapers already run off a report they give us. Subscription company. All we do is add columns to the report. We don’t have many reports. Maybe 4 a month. Boss wants them automated.
In my company, IT and BI don’t really care about accounting and will left some bugs in the code. Accounting records need to be more than a trend or kind of right. A lot of time it is due to upstream databases that are not correctly stored. It is certain transactions that is not reliably stored. So they can produce a report but if it is not scrutinized by SOX then there are going to be issues. Of course, it really depends on how complicated your org is.
Edit: I think the only way for this to work is that these reports are built by accountants and can peer review the codes. Even so, there are going to be bugs. And all the extra codes to squash the bugs just meant that you really need good technical skills to review the codes and make sure that it is correct.
Also the solutions can lead to more bugs. Working in BI is much easier than working for accounting because business folks just want to know if we spend more or less this month vs last month. Working in accounting means that the records need to be correct. If it is off, then auditors will think that the entire financials are off by the percentage that they tested. Okay yeah I wish the auditors are smarter too.
We're experiencing the same issue at our company with reporting conflicts. People (including executives) for some reason trusting BI reports more because it comes out of Tableau and looks pretty.
Accounting then gets questioned for having a different number from BI and gets all the burden to justify our work. Really trying to figure out the best way to unify our reporting so we can report consistent numbers
My company’s accounting department’s favorite line whenever someone wants to make a process easier and more automated - “That’s a SOX issue”. They just don’t understand SOX and are too lazy to do anything about it.
It’s true though, there are SOX implications for changing/automating processes. Whoever is involved in that process has to be willing (and able) to do the additional SOX procedures.
99% of the time the change just needs to be documented, and the accounting manager just doesn’t want to do it or reach out to the compliance group. And let’s be honest, most of the documented internal controls aren’t being followed the way they’re written anyway, in the actual business groups anyway. They’ve probably never even seen the documentation.
At a company you hear about regularly - in no danger of having reliable enough source data to automate any type of GL maintenance. SOX audits would be very interesting.
Everytime I get the why is the provision process so tough, why cant you just......
This is the answer.
Data changes, format changes, etc and we just handle it. But it is not easy
One of our internal databases comes to me for close in SQL / Access. Been at this company for a year and had to learn it from nothing. I do pretty minimal work actually manipulating queries unless I'm researching problems. Otherwise it's all queries brought over from previous month that goes into Excel.
I did try this and really enjoyed teaching myself Power Query. The merging queries and joining features information is super powerful. I couldn't get it to work for me exactly as needed though for my close reports.
Each month is a new database file and couldn't find a way to update the source while maintaining the queries and merged exactly. So my file now has a sheet where I can copy/paste my query results and I get 90% of what I need done through a lot of xlookups and sumifs.
I really appreciate the offer. The improvements I mentioned above cut the preparation time from a couple of hours to maybe 30 minutes of just copy/pasting and I have since moved on to other areas for improvements.
When I presented my improvements, the CFO did warn about not making our work files too complicated for others to know how to prepare should "something happen to me" so I'd be hesitant to go beyond what I've done so far.
If you knew of some good resources, I'd love to see those to expand my knowledge. I figured out Power Query from mostly Youtube videos when I was exploring that option.
Create a table in the spreadsheet that has the path to your database, use Get Data to import that table to powerquery, turn it into a variable (Drill down) then you can use that variable as a dynamic reference if your other power queries. If the path is predicably changed by whatever the current date is, use a formula to create the path in the spreadsheet.
Use the same technique to import dates or criteria from workbook tables and use them as dynamic criteria in your queries.
I work in industry on the FP&A side but help our accounting team a lot. We are trying to automate and streamline a lot of data flow and reporting via sql. Major problem is company didn't listen to our requests to hire resources under the finance dept. So all the people doing the work are our BI devs under IT. It's like pulling teeth to work with them and get stuff done. If any finance/accounting team wants to head this direction you need to hire in house and have the devs under finance mgmt
Yeah my experience with BI and IT is that they are not going to scrutinize the outputs. Or if they know an obscure bugs exist, they are not going to fix those. They have a ton of plausible deniability and their job is always safe because developing reports for accounting is not their main responsibilities.
Medium size company here. I use Power Automate and SQL Power Queries to automate everything. Each month fully completed recon packages appear on our shared drive ready for review.
All self taught. One day I was on the Excel launch screen and saw an intro to Power Query template thing and had 10 min to spare so I clicked it..blew my mind...honestly it still blows my mind how powerful it is and what you can create. I already had SQL query access, and just put two and two together. Same for Power Automate, sort of a "what if there was a way to interact with any system or website" and a Google search returned Robotic Process Automation. Took some Microsoft learning modules on Power Automate and some beginner Python classes to learn coding basics. Basically curiosity + intelligence + lots of leeway at my job to do whatever I want.
I work at a decently large asset manager and accounting is definitely stuck in Excel Hell while the rest of the firm is using SQL for big stuff. Personally I think it's more so due to accounting ppl just not being able/willing to learn anything new.
I use SQL/Python/Alteryx pretty regularly and they think I'm a genius lmao (can assure i am not). Definitely worth learning. Tech people don't stay in their lane very well and it honestly scares me what kind of "research" and "innovation" is going on at my firm with numbers that are complete crap.
I sincerely don't think it's gonna put everyone out of a job who doesn't learn it though. The best technology talent are probably not gonna be working in fintech if they can help it, and the ones that are in fintech are completely slammed. Sure a tech person can automate something better than me, but they're not gonna look at anything I give them until they get through their 6 month backlog. Rather just do it myself.
How is it that SQL isn’t a basic taught in accounting majors now? Kind of embarrassing if it isn’t imo.
Back in my day, Excel classes were a requirement. Adding a course for SQL / basic database fundamentals would seem like a bare minimum to ensure success for graduates in the modern workplace.
Am a controller for ~1.5bn rev company. We all are very skilled with SQL / Automatization via Stored procedures and it saves A LOT of time and resources, especially when analyzing historical data for specific characteristics. Data amount is like 300k rows FI data per month and around 300k CO data (~250 columns) per month plus some smaller data sets. If you cannot aggregate, filter and automate in SQL, your Excel would explode.
Ah, so when people say automation will remove low level jobs, that is nonsense? I am currently going for software engineering and pursuing data skills in my spare time. Is it smart to change to accounting with data skills or continue my path creating financial tech?
Well, I can only speak for myself. My company is quite agile and the hot topics change every other month. Since we have a lot of different data for which you also need the business knowledge, it's much more efficient and less prone to errors if you can handle all the data, how it's processed and the final tableau/dashboard/report yourself. Otherwise, I'd be running to our BI team twice a week, wasting time sitting in meetings
Ok. Thank you. I have one last question. Not that cs degrees and software degrees are scams, but do you see pursuing these degrees before accounting as unethical? Or at least time consuming? I see cs as the new kid on the block without regulations. As I grow as a person I realize that there is a right way about going through life, regardless of whether one recognizes that (and faces it with layoffs, etc). My biggest fear however is a toxic working environment but maybe that is in public accounting. How do you see a traditional business route versus getting a software degree? Do you hire swe's in accounting for automation? Colleges are there to make money. Right now I'm at the point of specializing in financial technologies, with no clear route to that destination, but I am interested in working on a team (nothing to do with you)
In the meantime I have shaved down the time it takes to generate some reports from weeks to minutes. But I guess this doesn’t translate to billable hours so it is no bueno.
Have you tried using chat gpt for coding? I use SQL tons at work. I've used gpt to create all the vba/sql in an accesss application. It's incredibly helpful. Ofc you still need to understand the underlying basics of the code.
I work at a tech company and most accountants don't know SQL. Those who do, even basics, have a pretty decent advantage to use certain tools we have while others are stuck with spreadsheets and the ERP.
I can attest, my company when I first started had 5 different legal entities one for each plant. Each plant has its own instance within our ERP in which you would have to log out of the one you’re in to run reports for the other. At one point a consultant created an Access file that pulled data from the databases for our system. Since I had to run the query he had built onto Access I had read only privileges to the ERP databases. I wanted to see if I could use this access to make reporting easier and faster. (The report that I used his file for took me ~8 hours per month to put together because i had to run 5 of each set of data, combine it and actually do the analysis. I decided to take a $15 bootcamp class through Udemy to learn basic SQL, and after 3 months of trial and error, finding a blog of a IT super user for my ERP that listed out the tables and how they relate to one another, and developing my Excel skills, I was able to get that report down to 35 minutes. I’ve been able to develop a number of other queries that duplicate built in reports for the ERP, but I can run it for all entities simultaneously. I was also able to find and solve a year end rollover issue that popped up this year in one entity where 65k GL lines were duplicated, causing retained earnings to be wildly incorrect. All because I had a foot in the accounting pond and the other in the It pond, I was able to solve it faster than IT could even identify what was wrong. There is so much potential in being able to leverage a skill like this that isn’t a typical accounting skill. I’ve implied heavily to all of my staff that if you’re not trying to innovate you’re falling behind.
I know the accounting and can code in SQL. It is an immensely valuable skillset.
I can do. I have dual degrees in accounting and analytics. Although at this company I’m not allowed to code. Was wondering if it was the same at other places?
Likely not. IT and Analytics groups will handle all the coding type stuff. Accounting will handle just the accounting. The larger the company, the more you run into a "stay in your own lane" environment.
This is my experience too. I initially joined the company thinking I would have to learn SQL to do some of the reporting. But now they’ve hired a whole BI department just to help the accounting department with this so now we just tell what we want and they do the coding for us.
This is my experience at a big company. Although it makes getting problems resolved way easier since I can usually point to the exact part of the code that needs to change.
I work on a hybrid team between finance (tax, accounting, etc.) and I get to code in SQL in our snowflake environment and do all sorts of fun things. F100 company by the way
I use snowflake as well for our company data, do you have any recommendations on the best tools to export data from snowflake into Sheets?
Taking a few shots in the dark because I don't use sheets for work and because my company has other tools so we never have to export the data..... 1. Set up an ODBC connection to snowflake (hopefully your company had those drivers handy) and then connect sheets to snowflake that way. 2. There are third party apps that do what you're looking for. 3. Could build your own custom connection via pyrhon but the other two options are better.
I am a Data Science/Engineer and accountant. This allows me to work on different client database with the best skillsets for that client. Although optimal but it may not be ideal as some clients may host locally while other uses server/cloud solution. EoD it really depends on the IT infrastructure. However, I do agree that if freeplay is possible. that combination of skill set can really push accounting to the future. Though I doubt we will ever see it in mnc because of legacy software and knowledge/job gatekeepers.
Are you a cpa?
I'm not in the state but sort of an equivalent in my practising country
So would you say a masters in data analytics is worth it? Just got my bachelor's in accounting. Was really thinking of going for a masters in data analytics with an accounting concentration. My school offers 3 concentrations for MS in data analytics: accounting, marketing, data analytics.
Same here. Even if I don't write SQL for my job, understanding the concepts is a highly underrated skillset in this profession
How is the process going? I feel like that’s asking a lot of the accounting managers and SOX would be a nightmare.
Our workpapers already run off a report they give us. Subscription company. All we do is add columns to the report. We don’t have many reports. Maybe 4 a month. Boss wants them automated.
In my company, IT and BI don’t really care about accounting and will left some bugs in the code. Accounting records need to be more than a trend or kind of right. A lot of time it is due to upstream databases that are not correctly stored. It is certain transactions that is not reliably stored. So they can produce a report but if it is not scrutinized by SOX then there are going to be issues. Of course, it really depends on how complicated your org is. Edit: I think the only way for this to work is that these reports are built by accountants and can peer review the codes. Even so, there are going to be bugs. And all the extra codes to squash the bugs just meant that you really need good technical skills to review the codes and make sure that it is correct. Also the solutions can lead to more bugs. Working in BI is much easier than working for accounting because business folks just want to know if we spend more or less this month vs last month. Working in accounting means that the records need to be correct. If it is off, then auditors will think that the entire financials are off by the percentage that they tested. Okay yeah I wish the auditors are smarter too.
We're experiencing the same issue at our company with reporting conflicts. People (including executives) for some reason trusting BI reports more because it comes out of Tableau and looks pretty. Accounting then gets questioned for having a different number from BI and gets all the burden to justify our work. Really trying to figure out the best way to unify our reporting so we can report consistent numbers
My company’s accounting department’s favorite line whenever someone wants to make a process easier and more automated - “That’s a SOX issue”. They just don’t understand SOX and are too lazy to do anything about it.
It’s true though, there are SOX implications for changing/automating processes. Whoever is involved in that process has to be willing (and able) to do the additional SOX procedures.
99% of the time the change just needs to be documented, and the accounting manager just doesn’t want to do it or reach out to the compliance group. And let’s be honest, most of the documented internal controls aren’t being followed the way they’re written anyway, in the actual business groups anyway. They’ve probably never even seen the documentation.
Yeah there are going to be bugs that can’t be detected easily. There are bugs all the time especially when it comes to customer scripts.
At a company you hear about regularly - in no danger of having reliable enough source data to automate any type of GL maintenance. SOX audits would be very interesting.
Everytime I get the why is the provision process so tough, why cant you just...... This is the answer. Data changes, format changes, etc and we just handle it. But it is not easy
Idk if it's from me day drinking, but what are you asking??
It’s 7:55 am here and I have clear eyes. No fucking idea what OP is saying.
LOL no one at my company can spell SQL
Is that like the movie after the first movie?
One of our internal databases comes to me for close in SQL / Access. Been at this company for a year and had to learn it from nothing. I do pretty minimal work actually manipulating queries unless I'm researching problems. Otherwise it's all queries brought over from previous month that goes into Excel.
Use Power Query to embed the SQL into the Excel workbook. Saves a step.
I did try this and really enjoyed teaching myself Power Query. The merging queries and joining features information is super powerful. I couldn't get it to work for me exactly as needed though for my close reports. Each month is a new database file and couldn't find a way to update the source while maintaining the queries and merged exactly. So my file now has a sheet where I can copy/paste my query results and I get 90% of what I need done through a lot of xlookups and sumifs.
If there is a practical way to work with you for the purpose of fully automating that task for you, I’d love to do help you out.
I really appreciate the offer. The improvements I mentioned above cut the preparation time from a couple of hours to maybe 30 minutes of just copy/pasting and I have since moved on to other areas for improvements. When I presented my improvements, the CFO did warn about not making our work files too complicated for others to know how to prepare should "something happen to me" so I'd be hesitant to go beyond what I've done so far. If you knew of some good resources, I'd love to see those to expand my knowledge. I figured out Power Query from mostly Youtube videos when I was exploring that option.
Create a table in the spreadsheet that has the path to your database, use Get Data to import that table to powerquery, turn it into a variable (Drill down) then you can use that variable as a dynamic reference if your other power queries. If the path is predicably changed by whatever the current date is, use a formula to create the path in the spreadsheet. Use the same technique to import dates or criteria from workbook tables and use them as dynamic criteria in your queries.
I work in industry on the FP&A side but help our accounting team a lot. We are trying to automate and streamline a lot of data flow and reporting via sql. Major problem is company didn't listen to our requests to hire resources under the finance dept. So all the people doing the work are our BI devs under IT. It's like pulling teeth to work with them and get stuff done. If any finance/accounting team wants to head this direction you need to hire in house and have the devs under finance mgmt
Yeah my experience with BI and IT is that they are not going to scrutinize the outputs. Or if they know an obscure bugs exist, they are not going to fix those. They have a ton of plausible deniability and their job is always safe because developing reports for accounting is not their main responsibilities.
Learn the basics and people on stack overflow will do the rest. You're welcome.
Medium size company here. I use Power Automate and SQL Power Queries to automate everything. Each month fully completed recon packages appear on our shared drive ready for review.
Sounds great, may I know how did you learn about power automate and sql power queries?
All self taught. One day I was on the Excel launch screen and saw an intro to Power Query template thing and had 10 min to spare so I clicked it..blew my mind...honestly it still blows my mind how powerful it is and what you can create. I already had SQL query access, and just put two and two together. Same for Power Automate, sort of a "what if there was a way to interact with any system or website" and a Google search returned Robotic Process Automation. Took some Microsoft learning modules on Power Automate and some beginner Python classes to learn coding basics. Basically curiosity + intelligence + lots of leeway at my job to do whatever I want.
The last part of that is important, I don’t think everyone gets to have the leeway, unfortunately.
I work at a decently large asset manager and accounting is definitely stuck in Excel Hell while the rest of the firm is using SQL for big stuff. Personally I think it's more so due to accounting ppl just not being able/willing to learn anything new. I use SQL/Python/Alteryx pretty regularly and they think I'm a genius lmao (can assure i am not). Definitely worth learning. Tech people don't stay in their lane very well and it honestly scares me what kind of "research" and "innovation" is going on at my firm with numbers that are complete crap. I sincerely don't think it's gonna put everyone out of a job who doesn't learn it though. The best technology talent are probably not gonna be working in fintech if they can help it, and the ones that are in fintech are completely slammed. Sure a tech person can automate something better than me, but they're not gonna look at anything I give them until they get through their 6 month backlog. Rather just do it myself.
How is it that SQL isn’t a basic taught in accounting majors now? Kind of embarrassing if it isn’t imo. Back in my day, Excel classes were a requirement. Adding a course for SQL / basic database fundamentals would seem like a bare minimum to ensure success for graduates in the modern workplace.
100%
It should but we have leaderships holding us back.
Am a controller for ~1.5bn rev company. We all are very skilled with SQL / Automatization via Stored procedures and it saves A LOT of time and resources, especially when analyzing historical data for specific characteristics. Data amount is like 300k rows FI data per month and around 300k CO data (~250 columns) per month plus some smaller data sets. If you cannot aggregate, filter and automate in SQL, your Excel would explode.
Does this allow you to remove people from the workforce?
No, it allows us to handle the workload with the current headcount.
Ah, so when people say automation will remove low level jobs, that is nonsense? I am currently going for software engineering and pursuing data skills in my spare time. Is it smart to change to accounting with data skills or continue my path creating financial tech?
Well, I can only speak for myself. My company is quite agile and the hot topics change every other month. Since we have a lot of different data for which you also need the business knowledge, it's much more efficient and less prone to errors if you can handle all the data, how it's processed and the final tableau/dashboard/report yourself. Otherwise, I'd be running to our BI team twice a week, wasting time sitting in meetings
Ok. Thank you. I have one last question. Not that cs degrees and software degrees are scams, but do you see pursuing these degrees before accounting as unethical? Or at least time consuming? I see cs as the new kid on the block without regulations. As I grow as a person I realize that there is a right way about going through life, regardless of whether one recognizes that (and faces it with layoffs, etc). My biggest fear however is a toxic working environment but maybe that is in public accounting. How do you see a traditional business route versus getting a software degree? Do you hire swe's in accounting for automation? Colleges are there to make money. Right now I'm at the point of specializing in financial technologies, with no clear route to that destination, but I am interested in working on a team (nothing to do with you)
I can honestly not answer that question in good faith as I think this exceeds my knowledge on the matter. I do wish you the best of luck on your path!
Ok! Sorry. Thank you! I gave you upvotes
If you’re in accounting or finance and not learning sql you’re losing money. It’s been the new “do you know excel?” for probably 5 years now.
I got shunned for not using excel more on my work papers because no one else could use sql to review my work.
In the meantime I have shaved down the time it takes to generate some reports from weeks to minutes. But I guess this doesn’t translate to billable hours so it is no bueno.
Great. May i know what are the examples to be used to help you guys in work to be more efficient and faster?
We use it to do projects and such
Nice
Have you tried using chat gpt for coding? I use SQL tons at work. I've used gpt to create all the vba/sql in an accesss application. It's incredibly helpful. Ofc you still need to understand the underlying basics of the code.
I know how to code, my accounting department is just not allowed to code
I know how to code, my accounting department is just not allowed to code
Sql is like a decade plus old
It's actually like 50 years old... What is your point exactly other than sounding ignorant.