SUM(selling price of units - purchase price of units)
.U1
units, on date t1
, at price P1
.U2
units, on date t2
, at price P2
.U3
units, on date t3
, at price P3
.P
.(U1 + U2 + U3)
available for selling. Realized gain is:(U1 + U2 + U3)
, for any number of transactions.P1
, P2
, P3
; for all those transactions.P
.GOOGLEFINANCE()
for historic data.GOOGLEFINANCE()
can be one option here. But as we’ve learned in the previous section, it has issues (remember seeing #NA
all over the place?)SPLIT()
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.ISIN
data
), add a column next to fund’s name. You can use Insert Column.https://www.amfiindia.com/spages/NAVAll.txt
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 #VALUE
error on spread sheet.IF(condition, arg1, arg2)
checks if condition
is true for a cell (in this case, if the cell is empty). If so, the first argument arg1
in inserted in the result cell (not the cell on which condition is being evaluated). Otherwise, arg2
is inserted.=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.VLOOKUP
with ISIN as search query.VLOOKUP()
stands for vertical lookup or row-wise lookup, across columns. It’s a way of searching for matching data.HLOOKUP()
and 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.B3
with Scheme Code
text in it; then, our range would be something like C9:F18123
(leaving out the last, date column).$C$9:$F$18123
.<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 false
is the right value to pass here.http://portal.amfiindia.com/DownloadNAVHistoryReport_Po.aspx?mf=<AMC Code>&tp=1&frmdt=31-Jan-2018&todt=31-Jan-2018
http://portal.amfiindia.com/DownloadNAVHistoryReport_Po.aspx?mf=21&tp=1&frmdt=31-Jan-2018&todt=31-Jan-2018
QUERY()
function, writing something akin to SQL queries.