Google Spreadsheet How to use GoogleFinance to load a table with Date, Low, Open, Close, High

Question:

( by Mike del Caribe )

Hi I would like to use the GoogleFinance function to supply input data for a candlestick chart.  First a function should create a table of historical data of length defined by a start date and today's date variable.  I am using the Position Tracker with Chart Template that I downloaded from the Google Docs site.

When I use =GoogleFinance($M$3, "price",'Common Data'!$B$5,TODAY(),'Common Data'!B7)

where:

$M$3 is the symbol

price is the last price

'Common Data'!$B$5 is the spreadsheet location with the start date of the data

'Common Data'!B7 is the spreadsheet location with the weekly or daily variable

Using this formula I only get two columns with date and last price.

I have tried replacing price with a cell reference (say D42 which contains Low) that contains low, open, close, or high and again I get a 2D array with only date and price.

If I then try to specify a range of cells (say D42:D43 which contain Low and Open) I get the value error message "#Vale: Range has no entry corresponding to this Cell"

If I use the Index function, example: =Index(GoogleFinance($M$3, F43,'Common Data'!$B$5,TODAY(),'Common Data'!$B$7), 2,2), I can get the individual values for a single date, but the table isn't automatically filled in for the rest of the table like it is using the GoogleFinance function. I have tried copying the Continue function that GoogleFinance uses, but I get the say result that the table doesn't fill.

Please recommend what my options are.

Solution:

Have a look at the following screenshot:

And I have the following formula in Cell A6:

=googlefinance( B1 ; "all" ; B2 ; B3 )

General explanation:

=googlefinance( "SYMBOL" ; "all" ; "START_DATE" ; "END_DATE" ) 

And try the following formula if you want to get it only for a single date:

=googlefinance( "SYMBOL" ; "all" ; "DATE" ) 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive