31 Oct 2008 by Chetan Shah.

If you want the autonumber to work even after you insert a row in Excel, use the following formula.

=CELL(”contents”, INDIRECT(CONCATENATE(”B”, ROW()-1)))+1

I am assuming the list of values are in B column.

A simple formula =”aboverow”+1 does not work if you insert a new row which falls in the value range.

If you have a better way of doing this please let me know

Posted in Technology | No Comments »

21 Oct 2008 by Chetan Shah.

Completely understand that the stock market is kicking us from all directions that sometimes it feels like calling it quits. But if you have loss making positions then you can sell those and buy it back after 30 days. This way you can atleast claim it as capital loss on income tax return and make uncle sam refund you the amount which is equal to (whatever your income tax bracket is)*loss.

Watchout for the Wash Sale Rule though.

Even more sophisticated way to do this to sell a covered call option. That way you can make the market pay for your promise to sell the stock at the price you chose.

Posted in Personal Finance | No Comments »

15 Oct 2008 by Chetan Shah.

What is Transaction Monitoring ?
Scanning every bank transaction that a customer makes to determine whether it is a money laundering activity or not.

Two Major Points to consider :
- Huge Volume of data to crunch
- Computation intensive algorithms.

The concept on surface seems very simple. Simplistically put, all we have to do is get all transactions that a customer makes, pass it through a mathematical “model” and analyze the alerts generated by the model. Alerts are the potential transactions which may be considered as money laundering activity and needs further attention.

This easily gets complicated when we consider medium, large and very large financial institutions having millions of transactions on a daily basis. Key parts of the application viz. data sourcing via an ETL process, data storage, statistical model execution need to be designed with performance perspective. Even though the application may have a very small user base, batch cycle execution time can easily ruin the SLA times.

Design Considerations :

1. Leverage industry standard ETL tools like Informatica to source transaction, customer and account data. Do not try to custom code, it might work initially but very soon you will realize that the custom does not scale as industry standard products like Informatica do.

2. Keep data as close to the mathematical models as possible. Preferred configuration is the same physical box so that the network latency is at its minimum. For most of us this may not be an option but the point is to have data available to the CPU as soon as possible.

3. Consider future generations of the product before designing for the current generation. This is a classic point for architects. Always design with future scalability in mind. For transaction monitoring type of application, revisiting the core architecture is painful not only from money perspective but from business confidence perspective. Rewriting code which works perfectly (functionally) to take care of new architecture decreases business partners confidence level as they do not see any lift from functionality perspective.

Posted in AML | No Comments »