Hi
It would be grateful if anyone can tell how to calculate QUANTITY to be freeze depend on the Debit Amt. How can we automate in Excel for repeat client code basis as shown in sample column for Freeze Qty & Freeze Value (should be as per debit amount). If holding value is more than Debit amount than partial quantity in excess of debit can be released.
<tbody>
</tbody>
It would be grateful if anyone can tell how to calculate QUANTITY to be freeze depend on the Debit Amt. How can we automate in Excel for repeat client code basis as shown in sample column for Freeze Qty & Freeze Value (should be as per debit amount). If holding value is more than Debit amount than partial quantity in excess of debit can be released.
| Client Code | ISIN | Script | Quantity | Rate | Holding Value | Debit Amt. | Map | Freeze Qty | Freeze Value | |
| 2902 | INE170V01019 | Salasar Techno Engineering Limited | 500 | 308.86 | 154430 | 106427 | N | 345 | 106557 | |
| 2902 | INE778B01029 | Ester Industries Limited | 5000 | 43.99 | 219950 | 106427 | N | 0 | 0 | |
| 2950 | INE426D01013 | Goa Carbon Limited | 200 | 571.49 | 114298 | 57680 | Y | 101 | 57720 | |
| 3051 | INE160A01022 | PUNJAB NATIONAL BANK | 200 | 60.72 | 12144 | 241 | Y | 4 | 243 | |
| 6021 | INE371A01025 | Graphite India Limited | 40 | 548.6 | 21944 | 21850 | Y | 40 | 21944 | |
| A0330 | INE029A01011 | BHARAT PETROLEUM CORP LT | 50 | 308.34 | 15417 | 14934 | Y | 49 | 15109 | |
| A0330 | INE775B01025 | KWALITY LIMITED | 100 | 12.74 | 1274 | 14934 | Y | 0 | 0 | |
| A0330 | INE209L01016 | COMMERCIAL ENG & CO. LTD | 20000 | 21.91 | 438200 | 14934 | Y | 0 | 0 | |
| A0447 | INE711A01022 | Walchandnagar Industries Limited | 200 | 138.07 | 27614 | 27212 | Y | 198 | 27338 | |
| A08022 | INE140A01024 | Piramal Enterprises Limited | 10 | 2234.13 | 22341 | 214 | Y | 1 | 2234 | |
| A0817 | INE750C01026 | Marksans Pharma Limited | 400 | 17.24 | 6896 | 2010 | Y | 117 | 2017 | |
| A0817 | INE694A01020 | Unitech Limited | 500 | 14.12 | 7060 | 2010 | Y | 0 | 0 | |
| A0817 | INE775B01025 | KWALITY LIMITED | 150 | 12.74 | 1911 | 2010 | Y | 0 | 0 | |
| A0817 | INE754A01014 | SUBEX LIMITED | 1200 | 6.15 | 7380 | 2010 | Y | 0 | 0 | |
| A0817 | INE776C01039 | GMR INFRASTRUCTURE LTD. | 300 | 1.34 | 402 | 2010 | Y | 0 | 0 | |
| A0817 | INE549A01026 | Hindustan Construction Company Limited | 500 | 0.9 | 450 | 2010 | Y | 0 | 0 | |
| A0817 | INE550C01020 | Urja Global Limited | 200 | 0 | 0 | 2010 | Y | 0 | 0 | |
| A0858 | INE855B01025 | RAIN INDUSTRIES LIMITED | 220 | 269.93 | 59385 | 36205 | Y | 135 | 36441 | |
| A0862 | INE692A01016 | UNION BANK OF INDIA | 634 | 61.8 | 39181 | 26520 | Y | 430 | 26574 | |
| A7000 | INE338A01016 | THIRUMALAI CHEMICALS LTD | 100 | 1925.79 | 192579 | 258952 | Y | 100 | 192579 | |
| A7000 | INE192R01011 | AVENUE SUPERMARTS LIMITED | 250 | 1278.28 | 319570 | 258952 | Y | 52 | 66471 | |
| A7000 | INE878B01027 | KEI INDUSTRIES LTD. | 500 | 361.7 | 180850 | 258952 | Y | 0 | 0 | |
| AA2014 | INE423A01024 | Adani Enterprises Limited | 100 | 96.05 | 9605 | 3935 | Y | 41 | 3938 | |
| AK402 | INE243D01012 | BF UTILITIES LIMITED | 50 | 336.53 | 16827 | 9464 | N | 29 | 9759 | |
| AK402 | INE775B01025 | kwality limited | 400 | 12.74 | 5096 | 9464 | N | 0 | 0 | |
| AK408 | INE208A01029 | ASHOK LEYLAND LTD | 11 | 126.17 | 1388 | 1358 | Y | 11 | 1388 | |
| AKJ1 | INE614G01033 | Reliance Power Limited | 400 | 14.76 | 5904 | 3826 | N | 260 | 3838 | |
| AM11 | INE092T01019 | IDFC BANK LIMITED | 200 | 28.83 | 5766 | 4834 | Y | 168 | 4843 | |
| B03200 | INE908D01010 | SHAKTI PUMPS (I) LTD | 10 | 546.59 | 5466 | 78954 | Y | 10 | 5466 | |
| B03200 | INE081A01012 | Tata Steel Limited | 25 | 509.86 | 12747 | 78954 | Y | 25 | 12747 | |
| B03200 | INE276C01014 | EIH Associated Hotels Limited | 10 | 447.32 | 4473 | 78954 | Y | 10 | 4473 | |
| B03200 | INE264T01014 | CAPACITE INFRAPROJECT LTD | 75 | 304.41 | 22831 | 78954 | Y | 75 | 22831 | |
| B03200 | INE283H01019 | Royal Orchid Hotels Limited | 25 | 143.85 | 3596 | 78954 | Y | 25 | 3596 | |
| B03200 | INE686A01026 | ITD Cementation India Limited | 50 | 132.47 | 6624 | 78954 | Y | 50 | 6624 | |
| B03200 | INE643A01035 | JTEKT INDIA LIMITED | 50 | 85.53 | 4277 | 78954 | Y | 50 | 4277 | |
| B03200 | INE087A01019 | Kesoram Industries Limited | 50 | 84.98 | 4249 | 78954 | Y | 50 | 4249 | |
| B03200 | INE684F01012 | Firstsource Solutions Limited | 100 | 37.82 | 3782 | 78954 | Y | 100 | 3782 | |
| B03200 | INE426A01027 | ALEMBIC LIMITED | 25 | 35.63 | 891 | 78954 | Y | 25 | 891 | |
| B03200 | INE878E01021 | Setco Automotive Limited | 100 | 33.39 | 3339 | 78954 | Y | 100 | 3339 | |
| B03200 | INE172N01012 | ADLABS ENTERTAINMENT LTD. | 100 | 31.18 | 3118 | 78954 | Y | 100 | 3118 | |
| B03200 | INE414A01015 | Madras Fertilizers Limited | 200 | 10.97 | 2194 | 78954 | Y | 200 | 2194 | |
| B03200 | INE294A01037 | Ballarpur Industries Limited | 200 | 3.52 | 704 | 78954 | Y | 200 | 704 | |
| B1547 | INE062A01020 | State Bank of India | 10 | 197.53 | 1975 | 954 | Y | 5 | 988 | |
| B20 | INE358A01014 | Abbott India Limited | 10 | 5433.18 | 54332 | 163468 | Y | 10 | 54332 | |
| B20 | INE797F01012 | Jubilant Foodworks Limited | 10 | 2129.31 | 21293 | 163468 | Y | 10 | 21293 | |
| B20 | INE702C01019 | APL Apollo Tubes Limited | 1 | 1893.58 | 1894 | 163468 | Y | 1 | 1894 | |
| B20 | INE192R01011 | AVENUE SUPERMARTS LIMITED | 10 | 1278.28 | 12783 | 163468 | Y | 10 | 12783 | |
| B20 | INE031B01049 | Ajanta Pharma Limited | 10 | 1192.23 | 11922 | 163468 | Y | 10 | 11922 | |
| B20 | INE674A01014 | Tata Sponge Iron Limited | 10 | 921.27 | 9213 | 163468 | Y | 10 | 9213 | |
| B20 | INE844O01022 | Gujarat Gas Limited | 50 | 742.52 | 37126 | 163468 | Y | 50 | 37126 | |
| B20 | INE176A01028 | Bata India Limited | 62 | 673.09 | 41732 | 163468 | Y | 23 | 15481 | |
| B20 | INE774D01024 | Mahindra & Mahindra Financial Services Limited | 10 | 433.08 | 4331 | 163468 | Y | 0 | 0 | |
| B20 | INE196A01026 | MARICO LIMITED | 30 | 263.68 | 7910 | 163468 | Y | 0 | 0 | |
| B20 | INE736A01011 | CENTRAL DEPO SER (I) LTD | 30 | 254.23 | 7627 | 163468 | Y | 0 | 0 | |
| B20 | INE094A01015 | Hindustan Petroleum Corporation Limited | 30 | 245.83 | 7375 | 163468 | Y | 0 | 0 | |
| B20 | INE095N01023 | NBCC (India) Limited | 500 | 167.95 | 83975 | 163468 | Y | 0 | 0 | |
| B20 | INE172A01027 | Castrol India Limited | 100 | 161.08 | 16108 | 163468 | Y | 0 | 0 | |
| B20 | INE421A01028 | ABAN OFFSHORE LTD. | 10 | 137.8 | 1378 | 163468 | Y | 0 | 0 | |
| B20 | INE028A01039 | BANK OF BARODA | 300 | 107.17 | 32151 | 163468 | Y | 0 | 0 | |
| B20 | INE263A01024 | BHARAT ELECTRONICS LTD | 80 | 105.83 | 8466 | 163468 | Y | 0 | 0 | |
| B20 | INE026A01025 | Gujarat State Fertilizers & Chemicals Limited | 60 | 101.56 | 6094 | 163468 | Y | 0 | 0 | |
| B20 | INE615H01020 | TITAGARH WAGONS LTD. | 20 | 90.24 | 1805 | 163468 | Y | 0 | 0 | |
| B20 | INE250B01029 | VINYL CHEMICALS (I) LTD. | 29 | 82.38 | 2389 | 163468 | Y | 0 | 0 | |
| B20 | INE171A01029 | The Federal Bank Limited | 100 | 72.27 | 7227 | 163468 | Y | 0 | 0 | |
| B20 | INE084A01016 | BANK OF INDIA | 450 | 67.53 | 30389 | 163468 | Y | 0 | 0 | |
| B226 | INE830Q01018 | Manaksia Coated Metals & Industries Limited | 702 | 22.98 | 16132 | 5063 | Y | 221 | 5079 | |
| G0844 | INE029A01011 | Bharat Petroleum Corporation Limited | 25 | 308.34 | 7709 | 4075 | Y | 14 | 4317 | |
| G0844 | INE094A01015 | Hindustan Petroleum Corporation Limited | 25 | 245.83 | 6146 | 4075 | Y | 0 | 0 | |
| H55 | INE391D01019 | TRF LIMITED | 100 | 195.02 | 19502 | 20291 | Y | 100 | 19502 | |
| I003 | INE572E01012 | PNB HOUSING FIN LTD. | 29 | 1172.84 | 34012 | 53960 | Y | 29 | 34012 | |
| I003 | INE484J01027 | Godrej Properties Limited | 1178 | 700.21 | 824847 | 53960 | Y | 29 | 20306 | |
| I003 | INE265F01028 | ENTERTAIN NET. IND. LTD. | 454 | 590.23 | 267964 | 53960 | Y | 0 | 0 | |
| I003 | INE461B01014 | Repro India Limited | 512 | 578.28 | 296079 | 53960 | Y | 0 | 0 | |
| I003 | INE765D01014 | WPIL LTD. | 2000 | 569.2 | 1138400 | 53960 | Y | 0 | 0 | |
| I003 | INE908D01010 | Shakti Pumps (India) Limited | 225 | 546.59 | 122983 | 53960 | Y | 0 | 0 | |
| I003 | INE193E01025 | BAJAJ ELECT.LTD | 4574 | 544.9 | 2492373 | 53960 | Y | 0 | 0 | |
| I003 | INE934S01014 | GNA AXLES LIMITED | 471 | 465.79 | 219387 | 53960 | Y | 0 | 0 | |
| I003 | INE898S01029 | Majesco Limited | 79 | 463.64 | 36628 | 53960 | Y | 0 | 0 | |
| I003 | INE671H01015 | Sobha Limited | 424 | 459.66 | 194896 | 53960 | Y | 0 | 0 | |
| I003 | INE795G01014 | HDFC STAND LIFE IN CO LTD | 910 | 424.11 | 385940 | 53960 | Y | 0 | 0 | |
| I003 | INE545U01014 | Bandhan Bank Limited | 4958 | 415.62 | 2060644 | 53960 | Y | 0 | 0 | |
| I003 | INE790G01031 | Shilpa Medicare Limited | 15 | 393.86 | 5908 | 53960 | Y | 0 | 0 | |
| I003 | INE274F01020 | WESTLIFE DEVELOPMENT LTD. | 2000 | 373.55 | 747100 | 53960 | Y | 0 | 0 | |
| I003 | INE335A01012 | Surya Roshni Limited | 155 | 353.46 | 54786 | 53960 | Y | 0 | 0 | |
| I003 | INE054A01027 | VIP Industries Limited | 2359 | 327.15 | 771747 | 53960 | Y | 0 | 0 | |
| I003 | INE485A01015 | CENTURY ENKA LTD | 1318 | 311.31 | 410307 | 53960 | Y | 0 | 0 | |
| I003 | INE863B01011 | Premier Explosives Limited | 233 | 301.61 | 70275 | 53960 | Y | 0 | 0 | |
| I003 | INE849A01020 | TRENT LTD | 4682 | 293.69 | 1375057 | 53960 | Y | 0 | 0 | |
| I003 | INE435A01028 | International Paper APPM Limited | 2980 | 269.89 | 804272 | 53960 | Y | 0 | 0 | |
| I003 | INE306L01010 | Quick Heal Technologies Limited | 5013 | 269.47 | 1350853 | 53960 | Y | 0 | 0 | |
| I003 | INE270I01014 | VISHNU CHEMICALS LIMITED | 78 | 243.3 | 18977 | 53960 | Y | 0 | 0 | |
| I003 | INE192A01025 | Tata Global Beverages Limited | 915 | 234.76 | 214805 | 53960 | Y | 0 | 0 | |
| I003 | INE032A01023 | BOMBAY DYEING & MFG. CO L | 9140 | 221 | 2019940 | 53960 | Y | 0 | 0 | |
| I003 | INE298G01027 | Ajmera Realty & Infra India Limited | 546 | 220.85 | 120584 | 53960 | Y | 0 | 0 | |
| I003 | INE062A01020 | State Bank of India | 1000 | 197.53 | 197530 | 53960 | Y | 0 | 0 | |
| I003 | INE597J01018 | Shalby Limited | 346 | 180.29 | 62380 | 53960 | Y | 0 | 0 | |
| I003 | INE164A01016 | Balmer Lawrie & Company Limited | 930 | 178.54 | 166042 | 53960 | Y | 0 | 0 | |
| I003 | INE034S01021 | Arvind SmartSpaces Limited | 903 | 153.07 | 138222 | 53960 | Y | 0 | 0 | |
| I003 | INE085A01013 | Chambal Fertilizers & Chemicals Limited | 3981 | 141.48 | 563232 | 53960 | Y | 0 | 0 | |
| I003 | INE305A01015 | TOURISM FINANCE CORP. OF | 1795 | 126.21 | 226547 | 53960 | Y | 0 | 0 | |
| I003 | INE323I01011 | Puravankara Limited | 6554 | 115.09 | 754300 | 53960 | Y | 0 | 0 | |
| I003 | INE643K01018 | ARIHANT SUPERSTRUCT LTD | 469 | 102.63 | 48133 | 53960 | Y | 0 | 0 | |
| I003 | INE993A01026 | MAN INDUSTRIES (I) LTD. | 1958 | 100.63 | 197034 | 53960 | Y | 0 | 0 | |
| I003 | INE338D01028 | BODAL CHEMICALS LTD | 7027 | 92.84 | 652387 | 53960 | Y | 0 | 0 | |
| I003 | INE522D01027 | Manappuram Finance Limited | 3658 | 90.91 | 332549 | 53960 | Y | 0 | 0 | |
| I003 | INE007B01023 | Geojit Financial Services Limited | 873 | 79.34 | 69264 | 53960 | Y | 0 | 0 | |
| I003 | INE229H01012 | Nitin Spinners Limited | 1821 | 74.33 | 135355 | 53960 | Y | 0 | 0 | |
| I003 | INE364A01020 | Jayshree Tea & Industries Limited | 3897 | 73.1 | 284871 | 53960 | Y | 0 | 0 | |
| I003 | INE818H01020 | LT Foods Limited | 3210 | 65.78 | 211154 | 53960 | Y | 0 | 0 | |
| I003 | INE386C01029 | Astra Microwave Products Limited | 340 | 64.04 | 21774 | 53960 | Y | 0 | 0 | |
| I003 | INE812G01025 | SMS Pharmaceuticals Limited | 5567 | 55.56 | 309303 | 53960 | Y | 0 | 0 | |
| I003 | INE080B01012 | KOHINOOR FOODS LIMITED | 911 | 43.04 | 39209 | 53960 | Y | 0 | 0 | |
| I003 | INE531E01026 | HINDUSTAN COPPER LTD | 7273 | 41.45 | 301466 | 53960 | Y | 0 | 0 | |
| I003 | INE082A01010 | Kopran Limited | 92 | 39.88 | 3669 | 53960 | Y | 0 | 0 | |
| I003 | INE684F01012 | Firstsource Solutions Limited | 13557 | 37.82 | 512726 | 53960 | Y | 0 | 0 | |
| I003 | INE148A01019 | TAMILNADU PETROPRODUCTS L | 10278 | 33.81 | 347499 | 53960 | Y | 0 | 0 | |
| I003 | INE195N01013 | PROZONE INTU PROPRTIS LTD | 10112 | 25.58 | 258665 | 53960 | Y | 0 | 0 | |
| I003 | INE185H01016 | Celebrity Fashions Limited | 910 | 20.14 | 18327 | 53960 | Y | 0 | 0 |
<tbody>
</tbody>
