0

Basically I'm doing this:

I have a series of 500 stocks that I'm running calculations on. Each calculation requires that I use GoogleFinance() and get a 50 day Open, High, Low, Close, Volume for the stock. I then re-use that data about 25 times per stock.

Getting 50 rows 500 times is fairly easy for GoogleFinance. Getting 50 rows X 500 X 25 is costly--the spreadsheet stops working. I'd like to get the data once, HAVE IT NOT TAKE UP ROOM IN MY SPREADSHEET and be able to re-use it 25 times to run the calculations I'm looking for.

Here's a typical query that I'm doing:

GoogleFinance( "GOOG" , "all" , WORKDAY( TODAY(), -50 ) , TODAY() )

Here's how you process it (one time only) without it taking up space in the spreadsheet (This gets the 50 day simple moving average and calculates it):

Average(Index(GoogleFinance( B10 , "close" , WORKDAY( TODAY(), -50 ) , TODAY() ),,2))

However if I want to get the 30, 20, 16 day average, I have to go back to google finance and get the data (or have the array in the spreadsheet) and then when I have to do high, low, open, close calculations I have to get those arrays from GoogleFinance as well--some times as many as 5 times for one calculation.

If I go to google finance and put the data in the spreadsheet it looks like this (for 51 rows):

Date    Open    High    Low Close   Volume
12/21/2016 16:00:00 7.39    7.59    7.34    7.43    38914935
12/22/2016 16:00:00 7.42    7.57    7.3 7.34    30818204
12/23/2016 16:00:00 7.36    7.49    7.33    7.44    27258669
12/27/2016 16:00:00 7.48    7.65    7.43    7.59    44681657
12/28/2016 16:00:00 7.61    7.62    7.16    7.23    69491582
12/29/2016 16:00:00 7.22    7.29    7.07    7.16    35444824
12/30/2016 16:00:00 7.15    7.21    7   7.02    46054539
1/3/2017 16:00:00   7.2 7.32    6.78    6.92    55800763
1/4/2017 16:00:00   7   7.07    6.85    7   48230387
1/5/2017 16:00:00   7.06    7.19    6.89    7.18    54284160
1/6/2017 16:00:00   7.25    7.28    6.99    7.01    39765906
1/9/2017 16:00:00   6.91    6.94    6.78    6.87    40637764
1/10/2017 16:00:00  6.96    7.01    6.87    6.92    28675833
1/11/2017 16:00:00  6.94    7.01    6.85    6.92    31826672
1/12/2017 16:00:00  6.99    7.075   6.87    6.93    30604286

Doing this for all 500 stocks takes 25,000 (ish rows) and becomes a maintenance nightmare--data all over the place!

So, Is there a way I can get an array once, use it in multiple equations and not have it take up space in my spreadsheet?

ᔕᖺᘎᕊ
  • 2,971
  • 3
  • 23
  • 38
David Urry
  • 807
  • 1
  • 8
  • 15

1 Answers1

0

I found a way to do this that's reasonably elegant.

The basic solution is to write a java script method for the google sheet with the following characteristics:

1) pass in the array like this (because it's easier than figuring out how to access the googleFinance libs) you can then use this data over and over again for all your calculations:
myStockStats(GoogleFinance( "GOOG" , "all" , WORKDAY( TODAY(), -50 ) , TODAY() ))

2) To keep your result on the same line is a bit tricky but you can do this: resultArray.push([ma20, ma50, close, Kpercent, Dpercent,...]);

Instructions for creating your own function in Google Sheets is here: https://developers.google.com/apps-script/guides/sheets/functions

David Urry
  • 807
  • 1
  • 8
  • 15