SUM(selling price of units - purchase price of units).
U1units, on date
t1, at price
U2units, on date
t2, at price
U3units, on date
t3, at price
(U1 + U2 + U3)available for selling. Realized gain is:
(U1 + U2 + U3), for any number of transactions.
P3; for all those transactions.
GOOGLEFINANCE()for historic data.
GOOGLEFINANCE()can be one option here. But as we’ve learned in the previous section, it has issues (remember seeing
#NAall over the place?)
TRANSPOSE()would’ve been of no use? Well, as you’re about to see, not all CSV data come in text files.
IMPORTDATA()function, that’d do just fine. We provide it a URL for the AMFI NAV endpoint, and it’d fetch that latest raw NAV data.
data), add a column next to fund’s name. You can use Insert Column.
IMPORTDATA()function, and pass the cell ID of the URL above, as argument
=IF(ISBLANK(<cell ID>), , SPLIT(<cell ID>, ";"), where
<cell ID>points to a row of semicolon-separated data, in the sheet AMFI NAV (Raw).
ISBLANK(<cell ID>)checks if the cell is empty. As you can see from the data, there can be plenty of empty cells. Invoking
SPLIT()on these, would result in an error, it’d show up as a
#VALUEerror on spread sheet.
IF(condition, arg1, arg2)checks if
conditionis true for a cell (in this case, if the cell is empty). If so, the first argument
arg1in inserted in the result cell (not the cell on which condition is being evaluated). Otherwise,
=IF(ISBLANK(<cell ID>), , SPLIT(<cell ID>, ";")basically means if
<cell Id>is empty, don’t put anything, else split the content of the cell by semicolon, and put the results in cells.
VLOOKUPwith ISIN as search query.
VLOOKUP()stands for vertical lookup or row-wise lookup, across columns. It’s a way of searching for matching data.
LOOKUP(); but for our use case,
VLOOKUP()would work fine for now.
VLOOKUP()for the top-most row as follows:
<cell ID that has ISIN for the fund>is ISIN, but we’re doing it this way, to be able to drag and auto-fill.
<range of data from processed NAV sheet>is a bit interesting.
VLOOKUP()does not work if the column we are searching for is not the first column of a range. In our processed NAV sheet, the ISIN is the second column.
Scheme Codetext in it; then, our range would be something like
C9:F18123(leaving out the last, date column).
<column index, that has NAV for the fund>, is column index of the column for the result.We’re looking for NAV when ISIN matches, which is the 4th column in our range (ISIN is the first column in this range). Hence, we can put this value as 4.
false. It tells
VLOOKUP()that the dataset in the range is not sorted. Our data set is not sorted on the basis of ISIN (sorted alphabetically on AMC name, actually), hence
falseis the right value to pass here.
QUERY()function, writing something akin to SQL queries.