actual date of investment
cashflow / amount($1000 every month)
nasdaq closing price that day
SUMformula, to add up all units purchased over the years. Then multiply it by NASDAQ closing price, as on 22nd May 2015, to get corpus value as on that date.
DD/MM/YYYYformat). But you’re free to use any format that’s commonly used.
Datefor that. If
DD/MM/YYYYis not listed as a format, you’d have to add it with custom format, under
More Date and Time Formats.
=GOOGLEFINANCE(".IXIC", "close", <date>, 1)
".IXIC"is the indicator / ticker for NASDAQ on Google Finance. You can replace this with other tickers, like
"TSLA"; to fetch data for these tickers.
"close"means closing market price. Other values can be
"low"(lowest price of that day),
"high"(highest price of that day) etc.
<date>means referring to the cell which has the date in. Since we’ve made sure it’s by system as a valid date, the Google Finance API would be able to use it.
1, is interesting, and takes care of our issue from earlier, about knowing working date on or after 1st of month.
7or any other higher number.
Returnon your keyboard; the cell would switch to display
Loading..., then print a small sub-table that looks like this:
GOOGLEFINANCEreturns a table, and not just a single data, we need to find a way to extract right data from these and put these in two cells.
GOOGLEFINANCEfunction. This is how the sub-table returned by
GOOGLEFINANCE()function would look like.
GOOGLEFINANCEcall, and pass it into
INDEXfunction, like this
actual datecolumn, we use (2,1) with
=INDEX(GOOGLEFINANCE(".IXIC", "close", <date cell>, 1), 2, 1)
Actual Datecolumn, drag the small square till you get to the end of it, so that there’s a
Dateentry for each of the entry in
INDEX, and extracting the entry from cell (2,1) for actual date.
2in last argument in
GOOGLEFINANCE(). In one case, we had to change that to
=INDEX(GOOGLEFINANCE(".IXIC", "close", <date cell>, 2), 2, 1)
#NAcells by tweaking the
IFERROR()function to re-run same query multiple times, until the
1000in first row. Enter same
1000again in second row.
x, rather denoted by
*(asterisk); division is denoted by
/(front slash), and not any division symbol.
D4 / E4(E4 is the cell with price, D4 is the cell with cashflow).
01/05/2015- below that, in a new row, add an entry for 22nd May 2015.
=SUMfunction to add up all units, listed above that cell.
".IXIC"ticker everywhere in the
"NASDAQ:MSFT"(Microsoft Corp.) or
"NASDAQ:GOOG"(Google) etc. - just by updating the ticker.
INDEX. This is effectively