Friday, August 31, 2012

How to capture the value of the stock price

How to capture the value of the stock price so that it is static and not update any more?



Question:

How to log the price of a stock at 15 minute increments during the day?

How to figure out that 15 minutes has indeed passed by?


How to capture the value of the stock price so that it is static and not update any more?


Solution:


Here is the script that will do your job:


function myFunction()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName("Sheet1");
  var range = sheet.getDataRange();
  var lastRow = range.getLastRow();
  var currentTime = Utilities.formatDate(new Date(), "GMT+0530", "dd-MM-yy HH:mm");
  var stockprice =range.offset(0,0,1,1).getValue(); // this is for Cell A1
  range.offset(lastRow, 0, 1, 1).setValue(currentTime);
  range.offset(lastRow, 1, 1, 1).setValue(stockprice);
}


Requirements for the above script example:
You must have the formula =googlefinance("GOOG","price") or any such formula in Cell A1, which will be recorded along with the time in the last row of the sheet.


If you are not familiar with writing scripts and don't know where to start from then have a look at the following link:
Here is post for how to work with scripting in google spreadsheets:


Now to set the timer to record this stock price at every 15 mins, you have to set the trigger for that. Following link will make you understand how to set a trigger on an event:


I hope above post will help you to understand and to record stock prices with the help of the script.


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.

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 




Cheers!!
Kishan,

No comments:

Post a Comment