Be Indian – Excel Audit Tool Template Using Lamdas
Posted: Fri Mar 15, 2024 3:43 pm
Be Indian – Excel Audit Tool Template – An Introduction
- CA S. Rathinagiri (srathinagiri@gmail.com)
Downloadable link from sirc Site
https://sirc-icai.org/sirc-tool/pdf.php
Welcome, this is IT era! Like big bang, today we have big data! Data is rapidly
expanding/exploding in various dimensions, thanks to computers, internet, mobile, IoTs and what
not?! Development of Hardware, Software, Network and Algorithms made it possible. Starting from
a flat file based database, today we are having data centers spreading across the globe. The field of
data analytics, data science, artificial intelligence are madly getting innovated by organizations,
corporations and individuals.
As an accounting professional, I am so fascinated by the Computer Aided Audit Tools shortly
called as CAAT. There are exclusive commercial products like Caseware IDEA and ACL as CAAT. As I use
Electronic Spreadsheets a lot it my office, I tried the features of these products in Microsoft Excel
products and seen that Excel is having most of them already! What we can get with a click of a
button in the Computer Aided Audit Tools, which are exclusively designed for audit purposes, can be
done by developing some formulas/macros/functions in Excel.
Until 2016, before the introduction of Power Query and Power Pivot, Excel was struggling
when we had data more than 100000 records. Apart from Power Query and Power Pivot the I am
seeing two other features as revolutions done by Microsoft Excel in the recent versions especially
Excel 365. The first feature is dynamic arrays and functions related to arrays. The second feature is
making the users as developers with Let and Lambda functions. Everything blended together, today’s
Excel is so powerful and capable of doing whatever CAAT can do!
Mantra of data analytics today is “No coding, Low coding”. After Python and R, it is well
proven that you need not be a developer for analyzing the data. From the day Lambda function was
introduced in Excel, I tried to learn about the programming abilities of this function along with other
array functions.
This Excel Audit Tool template is collection of 100+ wide range of functions related to
financial analysis, statistics, data manipulation, and other Excel operations. I have started this as a
hobby project. When searching for some algorithms and solutions, I have come across various online
resources and contributors who have generously shared the algorithms and knowledge related to the
functions listed. These resources have been instrumental in the development of these functions, and
they have played a significant role in enhancing their utility in various domains, including finance,
auditing, data analysis, and more. The collective wisdom and expertise of the online community have been invaluable that
serve both personal and professional purposes. The willingness of individuals and organizations to
share their insights and solutions has been instrumental in broadening the scope and capabilities of
these functions.
I have named this tool 'Be Indian' because some of these functions are created within the
context of the Indian environment. Furthermore, I have used this name since 2000 for my Excel addin, which was designed to spell Indian numbers and format numbers using the Indian method of commas.
This template is and will be 100% free and it certainly comes with no warranties or
guarantees! If you use this template you are most welcome to give
suggestions/comments/critics/ideas for further improvement.
I extend my thanks to the countless individuals, forums, websites, and open-source
communities who have contributed to the development of these algorithms. Their dedication to
knowledge sharing has enriched me in this learning and development process.
I am truly grateful for the support and guidance provided by the online community. I wish to list
out some of them:
1. Mark J. Nigrini – Many of the forensic audit related (almost all!) functions are inspired from
his AUTHORITATIVE book Forensic Analytics where he explained in detail about the algorithm
and how to do them in MS Excel and MS Access. His research with Benford’s Law is mind
blowing.
2. Audit Monk – A great resource of Excel/VBA script and algorithms for auditors.
3. Mike Excelisfun Girvin – Having 3500+ videos in YouTube, Mike Girvin had contributed a lot
to Excel community and I can prodly say that he is my Guru in Power Query and Power Pivot.
4. Owen Price – His contributions in Lambda functions are available in his GitHub posts. I used
one of his function as it is, with his permission. It is useful to calculate Levenshtein distance
between two strings.
5. Many of my CA Professional Colleagues and students gave me ideas.
- CA S. Rathinagiri (srathinagiri@gmail.com)
Downloadable link from sirc Site
https://sirc-icai.org/sirc-tool/pdf.php
Welcome, this is IT era! Like big bang, today we have big data! Data is rapidly
expanding/exploding in various dimensions, thanks to computers, internet, mobile, IoTs and what
not?! Development of Hardware, Software, Network and Algorithms made it possible. Starting from
a flat file based database, today we are having data centers spreading across the globe. The field of
data analytics, data science, artificial intelligence are madly getting innovated by organizations,
corporations and individuals.
As an accounting professional, I am so fascinated by the Computer Aided Audit Tools shortly
called as CAAT. There are exclusive commercial products like Caseware IDEA and ACL as CAAT. As I use
Electronic Spreadsheets a lot it my office, I tried the features of these products in Microsoft Excel
products and seen that Excel is having most of them already! What we can get with a click of a
button in the Computer Aided Audit Tools, which are exclusively designed for audit purposes, can be
done by developing some formulas/macros/functions in Excel.
Until 2016, before the introduction of Power Query and Power Pivot, Excel was struggling
when we had data more than 100000 records. Apart from Power Query and Power Pivot the I am
seeing two other features as revolutions done by Microsoft Excel in the recent versions especially
Excel 365. The first feature is dynamic arrays and functions related to arrays. The second feature is
making the users as developers with Let and Lambda functions. Everything blended together, today’s
Excel is so powerful and capable of doing whatever CAAT can do!
Mantra of data analytics today is “No coding, Low coding”. After Python and R, it is well
proven that you need not be a developer for analyzing the data. From the day Lambda function was
introduced in Excel, I tried to learn about the programming abilities of this function along with other
array functions.
This Excel Audit Tool template is collection of 100+ wide range of functions related to
financial analysis, statistics, data manipulation, and other Excel operations. I have started this as a
hobby project. When searching for some algorithms and solutions, I have come across various online
resources and contributors who have generously shared the algorithms and knowledge related to the
functions listed. These resources have been instrumental in the development of these functions, and
they have played a significant role in enhancing their utility in various domains, including finance,
auditing, data analysis, and more. The collective wisdom and expertise of the online community have been invaluable that
serve both personal and professional purposes. The willingness of individuals and organizations to
share their insights and solutions has been instrumental in broadening the scope and capabilities of
these functions.
I have named this tool 'Be Indian' because some of these functions are created within the
context of the Indian environment. Furthermore, I have used this name since 2000 for my Excel addin, which was designed to spell Indian numbers and format numbers using the Indian method of commas.
This template is and will be 100% free and it certainly comes with no warranties or
guarantees! If you use this template you are most welcome to give
suggestions/comments/critics/ideas for further improvement.
I extend my thanks to the countless individuals, forums, websites, and open-source
communities who have contributed to the development of these algorithms. Their dedication to
knowledge sharing has enriched me in this learning and development process.
I am truly grateful for the support and guidance provided by the online community. I wish to list
out some of them:
1. Mark J. Nigrini – Many of the forensic audit related (almost all!) functions are inspired from
his AUTHORITATIVE book Forensic Analytics where he explained in detail about the algorithm
and how to do them in MS Excel and MS Access. His research with Benford’s Law is mind
blowing.
2. Audit Monk – A great resource of Excel/VBA script and algorithms for auditors.
3. Mike Excelisfun Girvin – Having 3500+ videos in YouTube, Mike Girvin had contributed a lot
to Excel community and I can prodly say that he is my Guru in Power Query and Power Pivot.
4. Owen Price – His contributions in Lambda functions are available in his GitHub posts. I used
one of his function as it is, with his permission. It is useful to calculate Levenshtein distance
between two strings.
5. Many of my CA Professional Colleagues and students gave me ideas.