GOOGLEFINANCE
function, that fetches financial data from Google Finance. And preferably, use Google Sheets; because most likely, your MS Excel application doesn't come with this in-built function.GOOGLEFINANCE()
documentation by Google, to make sure you using this in your spreadsheets, is compliant with Google's terms of usage.GOOGLEFINANCE
function.date
actual date of investment
cashflow / amount
($1000 every month)nasdaq closing price that day
units purchased
SUM
formula, 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.GOOGLEFINANCE
looks like.01/04/2000
(in DD/MM/YYYY
format). But you’re free to use any format that’s commonly used.Format
-> Number
-> Date
for that. If DD/MM/YYYY
is not listed as a format, you’d have to add it with custom format, under Format
-> Number
-> More Formats
-> More Date and Time Formats
.GOOGLEFINANCE
function.=GOOGLEFINANCE(".IXIC", "close", <date>, 1)
".IXIC"
is the indicator / ticker for NASDAQ on Google Finance. You can replace this with other tickers, like "GOOG"
, or "MSFT"
, or "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.""
to wrap various texts inside the function. We could have also used single quotes (''
) to wrap these texts.1
, is interesting, and takes care of our issue from earlier, about knowing working date on or after 1st of month.7
or any other higher number.Enter
or Return
on your keyboard; the cell would switch to display Loading...
, then print a small sub-table that looks like this:GOOGLEFINANCE
returns 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.INDEX
GOOGLEFINANCE
function. This is how the sub-table returned by GOOGLEFINANCE()
function would look like.GOOGLEFINANCE
call, and pass it into INDEX
function, like thisactual date
column, we use (2,1) with INDEX
:
=INDEX(GOOGLEFINANCE(".IXIC", "close", <date cell>, 1), 2, 1)
Actual Date
column, drag the small square till you get to the end of it, so that there’s a Date
entry for each of the entry in Actual Date
.#NA
and say that there’s an error in getting data from Google Finance. This is a quirk of the GOOGLEFINANCE()
query. This can be fixed easily, but would require some manual intervention.INDEX
, and extracting the entry from cell (2,1) for actual date.1
to 2
in last argument in GOOGLEFINANCE()
. In one case, we had to change that to 3
.=INDEX(GOOGLEFINANCE(".IXIC", "close", <date cell>, 2), 2, 1)
#NA
cells by tweaking the GOOGLEFINANCE()
query.IFERROR()
function to re-run same query multiple times, until the #NA
is resolved.Cashflow
column, enter 1000
in first row. Enter same 1000
again in second row.$
. Just plain 1000, with no units.x
, rather denoted by *
(asterisk); division is denoted by /
(front slash), and not any division symbol.=
, then 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.Date
.=SUM
function to add up all units, listed above that cell.79.00688187
units.".IXIC"
ticker everywhere in the GOOGLEFINANCE
call."NASDAQ:MSFT"
(Microsoft Corp.) or "NASDAQ:GOOG"
(Google) etc. - just by updating the ticker.GOOGLEFINANCE
in INDEX
. This is effectively f(g(x))
.