0

I would like to calculate averages and other metrics based on share prices. These share prices are stored in an array and are pulled from Google Finance like this:

=GOOGLEFINANCE("GOOG","price",TODAY()-30,TODAY())

The GoogleFinance function is decribed here.

How can I return the average share price, the first share, the last share price or any other calculated metric (weighted expontential average, etc) based on the share prices in the array?

I know that Google Spreadsheet provides array formulas (e.g. ARRAYFORMULA) but have a hard time understanding on how they could be used to calculate such metrics. Or, is there any other way to perform this calculations without writing each share price in a cell and calculating the metrics manually.

I would appreciate any help or ideas from you.

Thank you!

AlexR
  • 5,514
  • 9
  • 75
  • 130

2 Answers2

1

If I understand your question correctly, you are interested in getting data from Google Finance, processing it further, and then displaying only the processed information.

Unfortunately, the native Spreadsheet formulas don't quite allow this -- at least not easily. Furthermore, Google Apps Script really isn't a viable solution because it's Finance service has been deprecated.

The most direct solution would be to use the GOOGLEFINANCE() formula to populate a range of cells with data, and then a separate cell construct your own formulas to process that data (find the average price by using AVERAGE() over the GOOGLEFINANCE() price output column, for example).

If it is important not show the output of GOOGLEFINANCE(), you can always hide those rows or place that output in a separate sheet.

Ryan Roth
  • 1,394
  • 9
  • 15
1

Here is a formula that is able to return an average of values returned from a GOOGLEFINANCE historical query. The trick is to use INDEX() to strip out the column of dates so you are left with just the prices.

=AVERAGE(INDEX(GOOGLEFINANCE("GOOG","price",TODAY()-30,TODAY(),"DAILY"), 0, 2))

The arguments (0,2) to INDEX appear to return the entire second column, including the "Price" header cell, but that seems to be OK because AVERAGE() doesn't count text cells.

Alex Hofsteede
  • 251
  • 2
  • 5