بینکنگ کی غیب ریڑھ کی ہڈی: ملاپ اور مفاہمت میں ایک گہرا غوطہ

بینکنگ کی غیب ریڑھ کی ہڈی: ملاپ اور مفاہمت میں ایک گہرا غوطہ

ٹائم سٹیمپ: 1 دسمبر 2023 5:34 AM
ماخذ نوڈ: 2988681

Last year I celebrated two decades of immersion in IT, specifically within the Financial Services sector. During this period I have been a witness to remarkable transformations in banking and technology. The emergence of Fintech companies and their customer-centric
approach, along with significant advancements in software engineering like Agile methodologies, microservices, and cloud computing, have reshaped the landscape. Yet, intriguingly, the back-office operations of many financial service companies have remained
relatively static over these years, still grappling with دستی انکوڈنگ، دہرائے جانے والے کام، اور ایکسل پر بہت زیادہ انحصار.

فنانشل سروسز سیکٹر میں خاص طور پر دستی اور ابھی تک خودکار عمل ہے۔ ملاپ اور مفاہمت. This process arises in various forms, i.e. from identifying and addressing discrepancies (typically occurring due to issues
or gaps with the integrations) in master-slave integrations to correcting or removing duplicates and semi-automated updates of operational systems with data from external sources.

کی دستیابی کے باوجود جدید ترین سافٹ ویئر (e.g. FIS IntelliMatch, Calypso Confirmation Matching, Misys CMS, Temenos T24 Confirmation Matching…​) for specific reconciliation tasks, such as payment and trade confirmation matching
(often based on SWIFT messages), the مماثل کاموں کی اکثریت اکثر حسب ضرورت یا دستی حل پر انحصار کرتی ہے۔, including Excel or even paper-based methods. Very often automation is also not pertinent, as matching is often involved in one-time actions
like marketing campaigns, data clean-ups, alignment with partners…​

بہتر مفاہمت کو سمجھنے کی ضرورت ہے۔ اس کے اجزاء کو الگ کرنایعنی

  • اس کے ساتھ شروع ہوتا ہے موازنہ کے لیے مختلف ڈیٹا سیٹوں کو جمع کرنا اور تبدیل کرنا. This consists of recuperating 2 data sets, which can be delivered in different formats, different structures, different scopes and with different names
    or enumerations. The data needs to be transformed to make them comparable and loaded into the same tool (e.g. a database or Excel), so that they can be easily compared.

  • اگلا مرحلہ a کی وضاحت کر رہا ہے۔ عین مطابق مماثل الگورتھم. This can be a simple unique key, but it can also a combination of multiple attributes (composite key), a hierarchical rule (i.e. match first on key 1, if no match try on key 2…​) or
    a fuzzy rule (if key of data set 1 resembles key of data set 2 it is a match). Defining this matching algorithm can be very complex, but it is crucial in the ability to automate the matching and reach a good output quality.

  • ایک بار مماثل الگورتھم کی وضاحت ہو جانے کے بعد، ہم داخل ہوتے ہیں۔ موازنہ کا مرحلہ. For small data sets, this can be done quite simple, but for very large data sets, it can necessitate all kinds of performance optimizations (like indices, segmentation,
    parallelism…​) in order to execute the comparison in a reasonable time.

  • آخر میں، شناخت شدہ تضادات کو قابل عمل نتائج میں ترجمہ کیا جانا چاہیے۔جیسے کہ رپورٹس، ساتھیوں یا فریقین ثالث سے مواصلت یا اصلاحی اقدامات (مثلاً فائلوں، پیغامات یا ایس کیو ایل اسٹیٹمنٹس کی تخلیق تاکہ اختلافات کو دور کیا جا سکے۔

مالیاتی خدمات میں مماثلت کی پیچیدگیاں متنوع ہیں۔ آئیے دریافت کریں۔ کچھ عام استعمال کے معاملات مالیاتی خدمات کے منظر نامے میں:

  • زیادہ تر بینکوں کے پاس a سیکیورٹیز ماسٹر فائل, describing all securities which are in position or can be traded at the bank. This file needs to be integrated with a lot of applications, but also needs to be fed by multiple data sources, like
    Telekurs, Reuters, Bloomberg, Moody’s…​ This means a security needs to be uniquely matched. Unfortunately, there is not 1 unique identifier describing all securities. Publicly traded instruments have a commonly agreed ISIN code, but private and OTC products
    like e.g. most derivatives usually do not. Banks have therefore invented internal identifiers, use fake ISIN codes (typically starting with an “X”) or use composite keys to uniquely identify the instrument (e.g. for a derivative this can be combination of
    ticker of underlying security, strike price, option type and expiration date).

  • ریٹیل بینکنگ میں یہ واضح طور پر ضروری ہے۔ مخصوص جسمانی شخص کی منفرد شناخت اور اس سے میل کھاتا ہے۔. However even in a developed country like Belgium, this is easier said than done. Every individual in Belgium has a National Register Number,
    so this seems the obvious choice for a matching key. Unfortunately, Belgian laws restrict the usage of this number to specific use cases. Additionally this identifier is not existing for foreigners and can change over time (e.g. foreign residents receive first
    a temporary National Register number which can change to a definitive, other one later or in case of gender change the National Register Number will change as well). Another option is to use the identity card number, but this is also different for foreigners
    and will change every 10 years. Many banks therefore use more complex rules, like a matching based on first name, last name and birth date, but obviously this comes also with all kinds of issues, like duplicates, spelling differences and errors in the names,
    use of special characters in the names…​

  • بہت ملتا جلتا مسئلہ ہے۔ کسی کمپنی یا خاص طور پر اسٹور سے مماثل. In Belgium, each company has a company number, which is similar to the VAT number (without the “BE” prefix), but this is again very national and 1 VAT number can
    have multiple locations (e.g. multiple stores). There exists a concept of a “branch number” (“vestigingsnummer” in Dutch), but this concept is not very well known and rarely used. Similar there exists the LEI code (Legal Entity Identifier) which is a code
    of a combination of 20 letters and codes, which uniquely identifies a company worldwide. Unfortunately, only large companies have requested a LEI code, so for smaller companies this is not really an option.
    Again more complex matchings are often done, like a combination of VAT number, postal code and house number, but obviously this is far from being ideal. In search for a unique and commonly known identifier, the Google ID becomes also more and more in use, but
    the dependency with a commercial company might also poses a big operational risk.

  • ایک اور دلچسپ معاملہ ہے۔ ویزا کارڈ کی ادائیگی میں اجازت اور کلیئرنگ میسج کا ملاپ. Normally a unique identifier should match both messages, but due to all kinds of exception cases (e.g. offline authorizations or
    incremental authorizations), this will not always be correct. Therefore a more complex rule is required, looking at several identifiers, but also to other matching criteria like acquirer ID, merchant ID, terminal ID, PAN (card number), timestamp and/or amount.
    اس قسم کی مماثلت ادائیگی کے استعمال کے دیگر معاملات پر بھی لاگو ہوتی ہے، جیسے کہ اجازت سے پہلے کی تکمیل کو اس کی سابقہ ​​اجازت کے ساتھ ملانا یا پہلے کی خریداری کے ساتھ رقم کی واپسی۔

  • مالی استعمال کا معاملہ جو تقریبا کسی بھی کاروبار سے متعلق ہے۔ انوائس اور ادائیگی کا ملاپ. When a company issues an invoice, it needs to be able to see when the invoice can be considered as paid. This is important for the accounting, but also
    to see if reminders for unpaid invoices should be sent out.
    To uniquely match the payment with the invoice, in Belgium typically a structured comment is used in the payment instruction. This unique code with check digit provides a unique matching reference. Unfortunately, customers often forget to put the structured
    comment or use the wrong one (e.g. copy/paste of a previous invoice). This means a company needs to have a fallback matching rule in case the unstructured comment is missing or wrong. Typically a combination of payment amount, payment date, IBAN of counterparty
    and/or name of counterparty can give an alternative way to match those invoices.

جیسا کہ آپ دیکھ سکتے ہیں مماثلت آسان نہیں ہے، لیکن بنیادی مراحل کو سمجھنا بہتر میچنگ میں مدد کر سکتا ہے۔ اس دوران، اپنی حدود کے باوجود، Excel (دستی) مماثلت کے لیے ایک طاقتور ٹول بنا ہوا ہے۔ لہذا a quick reminder for everyone who wants
to do matching in Excel:

  • استعمال مماثلت انجام دینے کے لیے VLOOKUP. تاہم VLOOKUP کی کچھ حدود ہیں، جیسا کہ حقیقت یہ ہے کہ اگر کوئی مماثلت نہیں ہے تو یہ ایک غلطی دیتا ہے اور یہ کہ آپ صرف پہلے کالم پر تلاش کر سکتے ہیں۔ ایک طاقتور متبادل استعمال کرنا ہے۔ XLOOKUP، جس
    does not have these limitations.

  • اگر آپ کو ایک ضرورت ہے جامع تلاش کی کلید, add a column in your search data set, with the composite search key (i.e. concatenate the different attributes, with e.g. “#” as a separator) and then use VLOOKUP/XLOOKUP to search on this new column.

  • کچھ توجہ کے پوائنٹس VLOOKUP استعمال کرتے وقت:

    • Do not forget to add “false” as the last argument of the function VLOOKUP to ensure an exact match.

    • Ensure that data formats are the same. E.g. a number “123” and the text “123” will not match, so it is important to convert them to the same format first. Idem for identifiers starting with leading 0’s. Often Excel will convert those to numbers, thus removing
      the leading 0’s and not resulting in a match.

    • Excel میں 100.000 سے زیادہ قطاروں کے ڈیٹا سیٹ استعمال نہ کریں۔ ایکسل کی کارکردگی اور استحکام کے لیے بڑے ڈیٹا سیٹ مسائل کا شکار ہیں۔
      It can also be interesting to put calculation mode to “Manual” if you are working with VLOOKUP on large data sets, otherwise Excel will recalculate all VLOOKUPs each time you make a minor change to the data.

    • VLOOKUP کے پاس تیسری دلیل کے طور پر واپس جانے کے لیے کالم نمبر ہے۔ کالموں کو شامل کرتے یا ہٹاتے وقت یہ نمبر متحرک طور پر موافق نہیں ہوتا ہے، لہذا کالم شامل کرتے یا ہٹاتے وقت موافقت کرنا یاد رکھیں۔

    • If you just want a match, you can use formula “=IF(ISERROR(VLOOKUP(<SearchValue>,<DataSet>,1,false),”NO MATCH”,”MATCH”)”

یہ چالیں مدد کر سکتی ہیں۔ اپنے دستی مماثلت کو تیز کریں۔، لیکن ظاہر ہے کہ حقیقی آٹومیشن ہمیشہ بہتر ہوتی ہے۔

مالیاتی خدمات میں مماثلت ہے a کثیر جہتی چیلنج, but understanding its fundamental steps is key to improving outcomes. While tools like Excel offer temporary solutions, the future lies in intelligent automation, which can significantly
streamline these processes. For those seeking to delve deeper into matching complexities or automation, leveraging advanced tools and platforms, including AI-driven solutions like ChatGPT, can provide both insights and practical solutions.

میرے تمام بلاگز کو چیک کریں۔ https://bankloch.blogspot.com/

ٹائم اسٹیمپ:

سے زیادہ فن ٹیکسٹرا