Some Cool Calc/Excel Tricks For Indian Taxpayers

If you do not consider yourself as a Calc or Excel pro but have to now do your taxes in a spreadsheet, then these cool tricks can help you a great deal.

The financial year ended in March and everyone with taxable income has to file IT returns in July this year. For me, things have become complicated because I started a new bank account last year. And, it came with a new demat (share-trading) account. Rather than just shovel numerous lists of transactions from my bank and demat accounts to my tax consultant, I decided to put all that data in one spreadsheet before sending it to him. My experience with spreadsheets ended with Lotus 1-2-3 (for DOS) in the 90s so working with Calc threw several challenges. Here is how I solved them.

IF function — for conditional formulae

Some fixed deposits make interest payments at a regular intervals (monthly, quarterly, half-yearly or yearly) while others pay cumulative interest only at maturity. How do I make a column for monthly interest payments for a list that contains both types of deposits? If the amount paid back by a fixed deposit at maturity is same as the amount invested at the beginning, then it would have paid interest at regular intervals. If the amount paid back at maturity was much greater than the amount invested at the beginning, then obviously, it must be a cumulative fixed deposit.

SUMIF function - for conditional totals

In a spreadsheet, I have a list of my gold ornaments. I bought most of the gold but some came from my parents. Because share-trading platforms routinely demand to know my networth, I need to calculate how much of the gold was my own and how much was not.

VLOOKUP function — for values from a lookup table

I have a list of stocks in my portfolio. Can Excel or Calc show me its worth based on the LTPs (last traded prices)? There are some web services that provide the latest stock quotes but they are all paid services. The National Stock Exchange (NSE) does publish the quotes on its website. I cannot write a shell script to download it because they use a dynamic link specifically to foil script writers like me.

The only choice is to

• manually download the market data from the NSE site. That is, from the site menu, choose MARKET DATA » Equity & SME Market » NIFTY TOTAL MARKET » Download(.csv).
• open the CSV (comma separated values) file and copy the data to a new worksheet in your Calc file. There will be over 750 rows to copy. (From the first cell, use Ctrl+Shift+Right and Ctrl+Shift+Down keys to select the whole data.)
• Name the worksheet as NSE_MARKET_DATA. For each stock, the trading symbol (code or ID) will be in the A column. The LTP will be in the F (sixth) column.

In other worksheets, I use a VLOOKUP function wherever I want to display data from the NSE_MARKET_DATA worksheet. (NSE_MARKET_DATA worksheet is the lookup table.) The first parameter of the function is for the value that I want to look up. The second parameter is the target range in the worksheet containing the lookup table (market data). The third parameter is the column number in the target range that contains the required data corresponding to the looked-up value. The last parameter is set to FALSE or 0 as the market data is not expected to be sorted.

For example, look at this formula in the second row:

=VLOOKUP(A2,$NSE_MARKET_DATA.$A$1:$N$753, 6,0)

The lookup table is locked in using dollar characters($NSE_MARKET_DATA.$A$1:$N$753). The A column contains the stock ID. After I specify the formula as shown above, the LTP price of the stock will be copied from the lookup table in the NSE_MARKET_DATA worksheet and displayed in the current worksheet.

Some stocks are listed only on BSE. There you have to download what is called the daily Equity Bhav Copy. This is available only after trading hours at
https://www.bseindia.com/markets/MarketInfo/BhavCopy.aspx

The ‘Bhav Copy’ file can be downloaded using a script but I will not provide it here as BSE might follow the footsteps of NSE and begin to use dynamic links.

INDEX and COUNT functions — for the last cell in a column

Netbanking websites provide downloadable spreadsheet files that contain the list of transactions in your account. The last cell in the statement contains your current balance. However, in a financial year, the list of transactions grows and the location of the last cell moves. How can you find the last cell in a column using a formula?

The trick is to use both INDEX and COUNT functions. The INDEX function can use row and column coordinates to obtain a value from a range of cells. The COUNT function can count the number of cells that are not empty.

Using the two functions together, I had this formula:

=INDEX(E7:E16,COUNT(E7:E16),1)

To limit the size of the screenshot, I used only a few rows in the example (E7:E16) including blank ones. A typical list of transactions will have hundreds of rows so a much larger range will be required.

Convert text to columns

If you download your list of transactions from your netbanking site as a spreadsheet or CSV, it is likely that not all cell values are appropriately converted. It is likely that numbers and dates are all prefixed with a single quote (" ' ") to prevent their values from being converted to illogical values. For example, to prevent a long number 23423234234234 from being converted as an exponential value 234232E+12 (which loses precision as it gets rounded off), it may be specified as text as '23423234234234.

When cell values contain text rather than as numbers and dates, you cannot apply formulae on them from other cells. Rather than leave them as they are, you may need to study each set of cells first and convert appropriately. There is a Text to Columns … option in the Data menu of Calc but it is not very intuitive. It works only on one column at a time. That is, you cannot convert multiple columns at a time. The correct procedure however is:

1. Select the cells (in a column) that need to be converted to numbers or dates.
2. Select Data » Text to Columns … from the Calc main menu.
3. In the Text to Columns dialog that opens up, click on the cells preview in the Fields section.
4. Choose an option in the Column type list, which will now be populated with appropriate target value types for the cells that you have clicked and selected in the previous step.

Bottomline

People who use Excel or Calc everyday may find these functions as nothing out of the ordinary. For someone who has only used spreadsheet programs to sort lists or do simple totals, these conditional functions does indeed seem mind-blowing.