Monday, April 4, 2011

Bank Audit SBI XL Tools For 2014


If you are and auditor of any of the branches of State Bank of India, here is a program an Excel Sheet named “Bank Audit SBI XL Tools” containing some utility program for you to help in your audit work.

This file will be helpful for Auditors of State Bank of India to import text files namely Loan file, CC_OD file and NPA statement to a well formatted excel spread sheet within seconds. And you can also analyze other accounts of NPA borrowers easily. Basically it is an Excel Worksheet programmed using macro and VBA. Before you start working with this program, please ensure that macros are not disabled in your system. It is very important for the smooth functioning of this utility program. If you want to get any help to understand how to enable macro in your computer just press “F1” Function key and type in the box provided “enabling macros” and press enter. And do according to the instructions you receive form the help topic of Microsoft Excel. This utility program is very useful in the current scenario of Core Banking Solutions. And you can further analyze the data using various tools of Excel (Sorting, Filtering, etc). Let me know if it was helpful to you. I assure you that this program will save your 50% time and energy otherwise you should have to spend for the same work manually. Thank you for sending your feedback and supporting me.

(PS: This audit tool is designed only for conducting audit in State Bank of India. Data structure may be different in other banks. The Data you receive from the Bank will be usually in a Data Compression Format (Generally *.gz Files). It can be extracted with WinRAR or any other similar programs. After extraction you get some text files; from those files choose the following text files:-


1. Loans Balance File,
2. CC/OD Balance File, and
3. List of NPA Accounts.


These text files should be renamed as given below and copied to your C:\ Drive before you use this Excel Sheet.)


You can download this program from the following link at free of cost. You can also donate for the further improvements of Bank Audit SBI XL Tools.

Click to Download - Bank Audit SBI XL Tools for 2014 year Audit  
For 2014 Bank Audit. Last updated on 11/04/2014
Please download this modified version..

Posted by MidhuN at

34 comments:

  1. Amaey AnandApril 5, 2011 at 2:38 AM

    Thanks!

    ReplyDelete
  2. MidhuNApril 7, 2011 at 12:48 PM

    I know there is an error in the program written to trace other accounts of NPA borrowers while we use it in MS Excel 2003 (It is not tracing well in 2003). I will modify it soon. But I assure you that this program will work well in MS Excel 2007. Mail me, if you want a well working excel sheet for MS Excel 2003 for urgent; or wait for the modified worksheet to be available here. Thank you

    ReplyDelete
    Replies
    1. MidhuNApril 10, 2012 at 8:38 AM

      Data structure of the text files used in this program changed and hence an updated version is given in the above link. All are requested to download the latest version.. Thank you..

      Delete
  3. CA. MOHIT ARORAApril 5, 2012 at 10:52 PM

    Thanks for this audit tool. Pl. tell me from where we will get the text files. I think from report folder they receive for annual closing.

    ReplyDelete
    Replies
    1. MidhuNApril 6, 2012 at 12:18 PM

      These text files are available to bank and they usually provide us a printed copy of these text files. Instead of receiving printed copies we can demand soft copies of those files.

      Delete
    2. MidhuNApril 10, 2012 at 8:39 AM

      Please download latest version for bank audit 2012 from here.

      Delete
  4. rmaApril 6, 2012 at 8:16 AM

    Please inform the purpose this software can be used for.

    ReplyDelete
    Replies
    1. MidhuNApril 6, 2012 at 12:44 PM

      Basically we can not process data received in text format. With this tool we are converting some text reports to Excel format. Excel is an effective data handling application And hence we can process the data in this spread sheet.

      We focused on computerized audit 3 years ago and we could trace out so many errors and unclassified NPAs even in the computerized environment. Time is an important factor in audit, however we have succeeded to cover 100% area in our audit with the help of computerized auditing techniques.

      Please do not consider this as a software or something even if it is helpful as good as a software. This excel sheet is a collection of macros recorded in audit environment (while we do all the conversion steps for our audit). And I've made some alterations and modifications with the help of VBA Programming language for gaining maximum advantage of it. Like this program was created by me, any of you can record macros for repetitive tasks and use it for later use. Thank you..

      Delete
    2. CA. MOHIT ARORAApril 6, 2012 at 11:12 PM

      pl. also explain that how to use this tool. In order to convert the text file into excel we generally copy the contents to an excel sheet and then run "text to columns"
      although I have not opened the downloaded file but it seems that this will be very useful in analysis.

      Delete
    3. MidhuNApril 7, 2012 at 4:32 AM

      Yes, this is the basic idea behind this tool. But while we do this we face so many practical issues (For eg: excel can not recognize "+100" as "100" or "120-" as "-120" or " 100 " as "100" or "1,00,000" as "100000" and so on). All these issues can be solved but can never be within 5 or 10 seconds.

      Main procedures of this program is

      1) Importing Loan Balance, CC OD, NPA reports in to Excel sheet (from C:\ drive).

      2) Removing all unwanted "------------" lines and other symbols used in text report format.

      3) Formatting, adjusting column width and renaming each columns as per the data type.

      4) Solving minus issue faced in NPA Report

      5) Solving minus issue faced in CCOD Report

      6) Removing spaces from all columns (" 100.00" to"100.00") (unless numbers will never be recognized as numbers) and Solving other recognized minor problems.

      7) Tracing other accounts of NPA Borrowers to know whether it is also NPA classified or not (By comparing all the three reports - Many accounts were detected unclassified during our audit even in the computerized environment).

      Time required to do all these tasks are less than 10 seconds.

      Delete
    4. CA. MOHIT ARORAApril 7, 2012 at 11:37 PM

      Thanks Buddy....I will definetely use it

      Delete
    5. MidhuNApril 10, 2012 at 8:40 AM

      Please download latest version for bank audit 2012 from here.

      Delete
  5. ca.tapanApril 9, 2012 at 7:30 AM

    Dear MidhuN,

    Thanks for this excellent tool.

    I too have been doing the same but thru the longer step wise procedure which you have mentioned in your comment. However, this tool has made the task very easy and fast.

    Now the other important area is the SA returns. If you could suggest some procedure to convert the same, it would be very beneficial in analyzing the advances.

    Thanks again.

    Warm Regards
    CA. Tapan Rai
    traico@gmail.com

    ReplyDelete
    Replies
    1. MidhuNApril 9, 2012 at 1:27 PM

      Dear Sir,
      We are going to start our current year bank audit within two days. I hopefully think about some improvisation in this program.

      SA returns; I forgot about the structure or type of data. Anyway as you know we can convert any text files to excel.

      Regards

      Delete
    2. MidhuNApril 10, 2012 at 8:41 AM

      Please download latest version for bank audit 2012 from here.

      Delete
  6. tapan goyalApril 9, 2012 at 8:41 AM

    dear midhun
    i tried the excellent excel audit tool provided by you but the problem is that whenever i wish to import data after coping the text file to c drive i receive a massage that 'compile error in module 1'. kindly let me know how to resolve the same

    thanks

    regards

    ca tapan goyal

    ReplyDelete
    Replies
    1. MidhuNApril 9, 2012 at 1:33 PM

      Dear Sir,

      I assume any of the following two reasons for this error

      1) You are using MS Office 2003 or
      2) Ms office 2003 and 2007 has together been installed in your system

      In both of the above situations many errors has been reported by mail and all of these problems were resolved when they try this in a system in which only MS Office 2007 is installed.

      I hope this solves the problem. Anyway please reply, that may help others to solve the same issue.

      Regards

      Delete
    2. MidhuNApril 10, 2012 at 8:41 AM

      Please download latest version for bank audit 2012 from here.

      Delete
  7. Puneet AggarwalApril 10, 2012 at 3:16 AM

    but its not working

    ReplyDelete
    Replies
    1. MidhuNApril 10, 2012 at 5:18 AM

      Pls use this program in a system in which Ms-Office 2007 version alone is installed.. It works fine in Ms-Office 2007..

      Delete
    2. MidhuNApril 10, 2012 at 8:42 AM

      Please download latest version for bank audit 2012 from here.

      Delete
  8. RajatApril 13, 2012 at 9:55 AM

    I am facing the same above issue. The system is 2007 and no other excel installed.

    ReplyDelete
    Replies
    1. MidhuNApril 14, 2012 at 12:52 AM

      Please send a screen shot of the error message. Or send me those text files I shall do that conversion for you..

      Personally I know 4 auditors using this for their audit, and their friends are also using this I have got many mails about some other issues but no similar issues reported. So I am unable to recognize the error occurred in your system. Sorry for the inconvenience..

      Delete
  9. P VMarch 5, 2014 at 10:09 AM

    Good work , give us some tips as to how to convert it for usage in other banks which normally use finnacle by infosys

    ReplyDelete
  10. CA Mukesh RuwatiaMarch 25, 2014 at 12:03 AM

    Very nice sir,

    There is any change in xls tools for current year, if any, please update the tools and send me. It is very useful for me.

    Thanks sir in advance.

    CA Mukesh Ruwatia
    M/s Ruwatia & Ruwatia
    DIDWANA-341303
    9414587151
    9549587151
    camukeshruwatia@gmail.com

    ReplyDelete
  11. KalpavrikshaMarch 27, 2014 at 9:15 AM

    Hi Mithun,
    Thanks for the excellent and useful tool. I would be thankful if you can guide me how to manually convert the txt file- step by step. Thanks once again. Kind regards.
    CA Kumar A

    ReplyDelete
  12. MidhuNApril 10, 2014 at 11:21 PM

    Bank Audit SBI XL Tools is updated for 2014 Year Audit, and available for free download here.

    ReplyDelete
  13. KalpavrikshaApril 10, 2014 at 11:43 PM

    Thank you so much......:-))

    ReplyDelete
  14. nandakumarMarch 25, 2015 at 1:46 AM

    dear mithun
    are u updating it for 2015. i got sbi audit for first time and if there are changes send to rseshancas@gmail.com please

    ReplyDelete
  15. MANISH MAHESHWARIMarch 29, 2015 at 9:42 PM

    I am also auditing first time in SBI. I wanted to know that which accounting software is used by SBI..is it finacle or any other For auditing purpose can u provide list of commands for transaction checking. ..also any new updation in tool mail me to caanubhavmaheshwari@gmail.com

    ReplyDelete
  16. eswarOctober 6, 2015 at 6:17 AM

    can you provide concurrent audit audit program please

    my mail id is seswarca@gmail.com

    ReplyDelete
  17. gkApril 2, 2016 at 1:03 AM

    Can this tool be used in Excel 2010

    ReplyDelete
    Replies
    1. MidhuNApril 2, 2016 at 1:31 AM

      Dear Sir,

      I have not updated it as I moved abroad with a new Job. Thanks for your support and feedback. Anyway you can do manually whatever that tool can do, for that please see the following url links.

      https://support.office.com/en-us/article/Import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba

      https://www.youtube.com/watch?v=37kwwEeFKvY

      https://www.youtube.com/watch?v=xK9DRfbNZAM

      Delete

Subscribe to: Post Comments (Atom)