Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have a Google Spreadsheet that I am using to checkout last prices or levels of certain stock indices's. I have two worksheets in the workbook, one contains the indices's and a calculation I am keeping track of and the other sheet is empty.

I want, on the empty sheet to check the calculation on the other sheet daily at 5PM eastern standard time and take that value and create a new record on the empty sheet going from A2:A

How would I go about this?

Sheet one looks like this:

Index, Price, No. Of Components

^DJI, 12,456, 30

etc.

Calculation

=myCustomFormula

Worksheet two should be =Day 1 at 5PM EST, value of myCustomCalculation

I would also like the value in spreadsheet to, to recalculate work sheet 1 every day at 5PM EST in order to get the value I am looking for.

share|improve this question
    
Could you share a doc with us? –  Jacob Jan Tuinstra Jan 20 '13 at 12:52
1  
    
Would you like to have LIBOR index as a fixed value added per date? –  Jacob Jan Tuinstra Jan 23 '13 at 8:23
    
Yes I would like the LIBOR calculated every day and at the close of day recorded and inserted in a row –  MCP_infiltrator Jan 24 '13 at 22:57
    
Then, check my answer !! –  Jacob Jan Tuinstra Jan 24 '13 at 22:58
show 1 more comment

1 Answer

up vote 0 down vote accepted

With the following Google Apps Script you can grab the LIBOR value and added it to a new sheet. Goto the script editor:
enter image description here

and add this script:

UPDATE 25-01-2013
I've noticed that the trigger occasionally troughs a NA#. Most probably because the GoogleFinance formula takes some time to get its info. Therefore I revised the snippet a bit, to have a sleep function added to it:

NEW

function myCustomCalculation() {
  //set active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get both the sheets 
  var sh0 = ss.getSheets()[0], sh1 = ss.getSheets()[1];

  // sleep
  Utilities.sleep(6000);

  // get cell value
  var cell = sh0.getRange("D2").getValues()[0];

  // add libor value to other sheet  
  sh1.appendRow([new Date(), cell]);
}

OLD

function myCustomCalculation() {
  //set active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // flush the spreadsheet before using it  
  SpreadsheetApp.flush();

  // get both the sheets 
  var sh0 = ss.getSheets()[0], sh1 = ss.getSheets()[1];

  // get cell value from sheet[0]
  var cell = sh0.getRange("D2").getValues()[0];

  // add libor value to other sheet  
  sh1.appendRow([new Date(), cell]);
}  

The clue to the story is to create a time based trigger:
enter image description here

and set the trigger to execute at 5-6 pm:
enter image description here

See the example file I´ve created: LIBOR. I've added an extra menu item, so that you can execute the script this way.

share|improve this answer
    
Will report back –  MCP_infiltrator Jan 26 '13 at 14:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.