Technology :
Using Power Query
Case study 03
Code: Select all
NWSA01 NEW WEEKLY STATEMENT OF AFFAIRS PAGE: 1
STATE BANK OF INDIA AS AT SEP 30, 2020 RUN DATE: 30/09/2020
BRANCH : 00900PUDUKKOTTAI
000 INDIAN RUPEE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
CODE CREDIT BALANCES INNER COLUMN OUTER COLUMN
NO. AMOUNT IN FULL AMOUNT IN FULL
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
LIABILITIES
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
L01A CURRENT ACCOUNT 20,97,80,232.26
L01B CASH CREDIT (CREDIT BAL) 2,68,40,247.94
L01D SAVING BANKS ACCOUNTS 273,18,75,708.50
L01E SUNDRY DEPOSITS 25,55,637.95
L01DA PARKING ACCOUNT FOR INITIAL CASH DEPOSIT IN BC 6,129.00
L01 DEMAND DEPOSITS 297,10,57,955.65
L02A TERM DEPOSITS 38,25,66,973.53
L02B SPECIAL TERM DEPOSIT 153,33,76,132.41
L02C RECURRING DEPOSITS 1,36,77,928.92
L02E ANNUITY DEPOSITS 1,24,323.00
L02 TIME DEPOSITS 192,97,45,357.86
L21 SBI BILLS ACCOUNT (CONTRA) A18 1,26,65,285.00 1,26,65,285.00
L24 BANK GUARANTEES ISSUED ACCOUNT (CONTRA) A21 1,07,67,000.00 1,07,67,000.00
L30AL CERSAI CHARGES RECOVERED ACCOUNTS 27,671.00
L30C INTEREST ACCOUNT 7,79,62,133.28
L30D EXCHANGE ACCOUNT 1,12,732.90
L30E COMMISSION ACCOUNT 1,21,95,735.88
L30ED COMMITMENT CHARGES ON LOANS & ADVANCES A/C 13,674.00
L30EE FORECLOSURE CHARGES ON LOANS & ADVANCES A/C 3,87,983.96
L30EH STATE GOVERNMENT COMMISSION 32,77,358.71
L30EI CENRTAL GOVERNMENT COMMISSION 3,70,847.71
L30EN ATM OTHER INCOME ACCOUNTS 3,29,526.80
L30EQ COMMISSION FROM PSU/AUTONOMOUS BODIES 1,28,089.59
L30EW COMMISSION ON IMPS TRANSACTIONS 12.00
L30F RECOVERY IN WRITTEN OFF ACCOUNTS 7,14,800.00
L30FH SERVICE CHARGES : MISCELLANEOUS 1,230.00
L30FJ HL SWITCH FEE RECOVERED ACCOUNT 11,800.00
L30 SUNDRIES (CREDIT) (INCOME ACCOUNTS) 9,55,33,595.83
L31A SAVINGS BANK INTEREST ACCOUNT 9,50,626.71
L31B TERM DEPOSIT INTEREST ACCOUNT 19,20,983.52
L31C SPECIAL TERM DEPOSIT INTEREST ACCOUNT 1,17,05,490.64
L31H RECURRING DEPOSIT INTEREST ACCOUNT 1,28,925.94
L31 MISCELLANEOUS LIABILITIES - INTEREST PROVISION 1,47,06,026.81
NWSA02 NEW WEEKLY STATEMENT OF AFFAIRS PAGE: 1
STATE BANK OF INDIA AS AT SEP 30, 2020 RUN DATE: 30/09/2020
BRANCH : 00900 PUDUKKOTTAI
000 INDIAN RUPEE
L32I BRANCH SYSTEM SUSPENSE ACCOUNT 3,46,762.26
L32IQ LIABILITY ON INTEREST CAPITALISATION ON RESTRUCTURED A/CS (STD ASSETS) 3,63,353.00
L32JE AGENCY CLEARING DRAFT PAYABLE A/C NEW 1,580.00
L32QH DEPRECIATION (YEAR TO DATE)-FURNITURE & FIXTURE 8,23,549.33
L32QK TDS ON CASH WITHDRAWALS 32,884.00
L32JW TDS ON PAYMENTS MADE BY THE BANK 2,25,187.00
L32 OTHER MISCELLANEOUS LIABILITIES 17,93,315.59
L33CA CORE INTER BRANCH ACCOUNT(IF CR)(ONLY FOR CORE BANKING BRANCHES) 537,38,54,947.94
L33W INTER BRANCH TRANSFER(NON-DTL)(IF CREDIT) 2,97,280.00
L33 INTER OFFICE ACCOUNTS (NON DTL) (IF CREDIT) 537,41,52,227.94
L43A INTER OFFICE INSTRUMENTS NEW ACCOUNT (IOI) (IF CREDIT) 3,82,54,278.37
L43B INTER OFFICE INSTRUMENTS (IOI) (IF CR.) 57,57,26,273.59
L43 INTER OFFICE INSTRUMENTS ACCOUNT (IOI) (IF CREDIT) 61,39,80,551.96
L49II BANKS LIAB OF BANKER CHEQUE >THAN 3 YEARS CR TO CHARGES A/C CTR- A47IF 42,98,273.60
L49 SECURITISATION CONTROL ACCOUNTS (CREDIT) 42,98,273.60
NWSA03 NEW WEEKLY STATEMENT OF AFFAIRS PAGE: 1
STATE BANK OF INDIA AS AT SEP 30, 2020 RUN DATE: 30/09/2020
BRANCH : 009000 PUDUKKOTTAI
000 INDIAN RUPEE
----------------------------------------------------------------------------- ---------------------- ---------------------------
L99 TOTAL LIBILITIES 1102,86,99,590.24 1102,86,99,590.24
============================================================================= ====================== ===========================
NWSA04 NEW WEEKLY STATEMENT OF AFFAIRS PAGE: 1
STATE BANK OF INDIA AS AT SEP 30, 2020 RUN DATE: 30/09/2020
BRANCH : 00900 PUDUKKOTTAI
000 INDIAN RUPEE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
CODE DEBIT BALANCES INNER COLUMN OUTER COLUMN
NO. AMOUNT IN FULL AMOUNT IN FULL
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
ASSETS
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
A01A BRANCH CASH BALANCE 1,65,344.00
A01C ATM CASH BALANCE 10,14,76,500.00
A01I ATM CASH INTERMEDIATE (WITH CIT AGENCY) A/C 7,00,000.00
A01 CASH & GOLD STOCK 10,23,41,844.00
A12A DEMAND LOANS 48,44,72,985.23
A12C TERM LOANS 227,83,66,801.33
A12D AGRICULTURAL TERM LOANS 1,73,28,046.18
A12E CURRENT ACCOUNT (DEBIT BALANCE) 27,74,13,959.53
A12EB FESTIVAL ADVANCE CURRENT ACCOUNT OVERDRAFT 6,08,000.00
A12F CASH CREDIT ACCOUNT (DEBIT BALANCE) 76,82,28,949.89
A12G AGL. CASH CREDIT ACCOUNT (DEBIT BALANCE) 8,35,00,127.19
A12I RECALLED ASSETS (NET OF CLAIMS RECEIVED FROM DICGC) 1,63,42,616.59
A12U SAVINGS BANK (OD) 1,935.80
A12 LOANS, CASH CREDITS, OVERDRAFTS ETC. 392,62,63,421.74
A18 CONSTITUENTS LIABILITIES ON ACCOUNT OF SBI BILLS ACCOUNT (CONTRA) L21 1,26,65,285.00 1,26,65,285.00
A21 CONSTITUENTS LIABILITIES ON A/C OF BANK GUARANTEES ISSUED-CONTRA L24 1,07,67,000.00 1,07,67,000.00
A27A FIXED ASSETS - FURNITURE & FIXTURES 62,47,487.81
A27 FIXED ASSETS 62,47,487.81
A29A CHARGES ACCOUNT 2,06,31,280.59
A29CA SERVICE TAX ON COMMISSION / EXCHANGE INCOME A/C 19,17,980.79
A29AX COMMISSION PAID TO RECOVERY AGENTS A/C 43,960.00
A29AW LOSS ON SALE OF FIXED ASSET 1,034.95
A29GD CHARGES ACCOUNT HOME LOAN SOURCING FEE 2,72,438.00
A29GE CHARGES ACCOUNT AUTO LOAN SOURCING FEE 58,336.00
A29GF GST EXPENSES SERVICES/INPUT 1,19,715.08
A29GG GST EXENSES-CAPITAL ASSETS 29,799.77
A29 SUNDRIES (EXPENDITURE ACCOUNTS) 2,30,74,545.18
A30A STATIONERY ACCOUNT 46,095.66
A30B STAMP ACCOUNT 200.00
A30BH AGRICULTURAL INTEREST SUBVENTION CLAIM RECEIVABLE 36,93,845.14
A30BQ STATE GOVERNMENT COMMISSION RECEIVABLE 17,22,370.83
A30BR CENTRAL GOVERNMENT COMMISSION RECEIVABLE 1,54,769.29
A30BZ ADD INTEREST SUBVENTION RECEIVABLE FOR PROMPT PAYING FARMERS - 2015-16 45,54,458.04
A30C SUSPENSE ACCOUNT 2,29,298.80
A30E USROMD ACCOUNT 5,58,910.00
NWSA05 NEW WEEKLY STATEMENT OF AFFAIRS PAGE: 1
STATE BANK OF INDIA AS AT SEP 30, 2020 RUN DATE: 30/09/2020
BRANCH : 00900 PUDUKKOTTAI
000 INDIAN RUPEE
A30G BRANCH SYSTEM SUSPENSE ACCOUNT 8,50,856.10
A30 MISCELLANEOUS ASSETS 1,18,10,803.86
A37 STATE GOVT. ACCOUNT (IF DEBIT) 691,39,30,929.05 691,39,30,929.05
A39 CURRENCY TRANSACTION ACCOUNT (IF DEBIT) 1,73,00,000.00 1,73,00,000.00
A47IF BANKER CHEQUE OUTSTANDING BEYOND 3 YEARS CR TO CHARGES A/C-CONTR-L49II 42,98,273.60
A47 SECURITISATION CONTROL ACCOUNTS (DEBIT) 42,98,273.60
----------------------------------------------------------------------------- ---------------------- ---------------------------
A99 TOTAL ASSETS 1102,86,99,590.24 1102,86,99,590.24
============================================================================= ====================== ===========================
NWSA07 SUPPLEMENTRY WEEKLY STATEMENT OF AFFAIRS PAGE: 1
STATE BANK OF INDIA AS AT SEP 30, 2020 RUN DATE: 30/09/2020
BRANCH : 00900 PUDUKKOTTAI
000 INDIAN RUPEE
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
CODE WEEKLY STATEMENT OF AFFAIRES - SUPPLEMENTARY INFORMATION AMOUNT IN FULL AMOUNT IN '000
NO.
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
S01 DEMAND DEPOSITS FROM BANKS (INCLUDED IN DEMAND DEPOSITS) 76,09,087.39 7,609
S05 SAVING BANKS ACCOUNT(INCLUDED IN DEMAND DEPOSITS) 272,78,83,651.70 27,27,884
S11 TIME DEPOSITS WITH CONTRACTUAL MATURITY OF ONE YEAR OR LESS 149,99,02,879.29 14,99,903
S14 DEPOSITS IN NON-RESIDENT(EXTERNAL RUPEE)ACCOUNT 48,24,51,102.97 4,82,451
S15 INTT. PROVISION ON SAVINGS BANK ACCOUNTS 9,50,626.71 951
S17 BALANCES IN CURRENT ACCOUNT WITH PUBLIC SECTOR BANKS 62,774.50- 63
S18 TIME DEPOSITS WITH CONTRACTUAL MATURITY OF > 1 YEAR 42,98,42,478.57 4,29,842
S19 INTT. PROVISION ON NRE DEPOSITS 1,29,30,261.38 12,930
S20 ADVANCES UNDER COLLECTION A/C 5,04,15,379.13- 50,415
S29 CORE INTER BRANCH A/C (IF CREDIT)(L33CA) 537,38,54,947.94- 53,73,855
S48 BR. SYS. SUSPENSE A/C(CREDIT)(L32I) 3,46,762.26- 347
S68 BR.SYS. SUSPENSE A/C (DEBIT) (A30G) DEPOSITS 8,50,856.10 851
S70 TERM LOANS(OTHER THAN TL AGAINST FCNR[B] 229,25,22,002.33 22,92,522
S87 NON RESIDENT(ORDINARY)A/CS(NRO) 2,54,10,400.03- 25,410
S88 INTEREST PROVISION ON NRO A/CS 4,29,426.60- 429
S101 SBI TINY SAVINGS BANK ACCOUNT 39,92,056.80- 3,992
S102 SBI TINEY FLEXI RECURRING DEPOSITS A/C 51,114.00- 51
S121 CAPITALISED INTEREST ON SPECIAL TERM DEPOSITS 5,25,53,714.12- 52,554
S122 CAPITALISED INTEREST ON OTHER TERM DEPOSITS 22,71,831.26- 2,272
S123 SC CONTRA 5,55,450.00- 555
S124 LIABILITY ON SHORT CREDIT 5,55,450.00 555
S134 TDS PAYABLE ACCOUNT ( INCLUDED IN UNDER L32RQ) 32,884.00- 33
S139 AGENCY CLEARING DRAFT PAYABLE A/C NEW 1,580.00- 2
S151 TDS ON PAYMENTS MADE BY THE BANK 2,25,187.00- 225
NXY50 INTER BRANCH TRANSFER CREDIT ACCOUNT (NDTL) (ONLY FOR CAO,KOLKATA) 2,97,280.00- 297