In this video, I demonstrate how to analyze stock using the GOOGLEFINANACE Function on Google Sheets with Google Apps Script.
How to Video:
Video Notes:
- Google Finance Function Documentation
Code in Video:
function start() {
var ss= SpreadsheetApp.getActiveSpreadsheet();
var setupSheet = ss.getSheetByName("SETUP");
var lastRow = setupSheet.getLastRow();
for(var y = 6; y <= lastRow; y++)
{
var checkbox = setupSheet.getRange(y,8).getValue();
if(checkbox == true)
{
setupSheet.getRange("D"+y+":G"+y).clear();
var symbol = setupSheet.getRange(y,3).getValue();
var market = setupSheet.getRange(y,2).getValue();
getData(symbol, market, y);
setLink(symbol, market, y);
setupSheet.getRange(y,8).setValue(false);
}
}
}
function checkAll() {
var ss= SpreadsheetApp.getActiveSpreadsheet();
var setupSheet = ss.getSheetByName("SETUP");
var lastRow = setupSheet.getLastRow();
for(var y = 6; y <= lastRow; y++)
{
setupSheet.getRange(y,8).setValue(true);
}
}
function uncheckAll() {
var ss= SpreadsheetApp.getActiveSpreadsheet();
var setupSheet = ss.getSheetByName("SETUP");
var lastRow = setupSheet.getLastRow();
for(var y = 6; y <= lastRow; y++)
{
setupSheet.getRange(y,8).setValue(false);
}
}
function setLink(symbol, market, row)
{
var ss= SpreadsheetApp.getActiveSpreadsheet();
var setupSheet = ss.getSheetByName("SETUP");
var richValue = SpreadsheetApp.newRichTextValue()
.setText(symbol)
.setLinkUrl("https://www.google.com/finance/quote/"+symbol+":"+market)
.build();
setupSheet.getRange(row, 9).setRichTextValue(richValue);
}
function getData(symbol, market, row) {
var ss= SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getSheetByName("DATA");
var setupSheet = ss.getSheetByName("SETUP");
var percentGain = setupSheet.getRange(2,2).getValue();
var percentDown = setupSheet.getRange(3,2).getValue();
var daysBack = setupSheet.getRange(1,2).getValue();
dataSheet.getRange("C1:D1000").clear();
var cell = dataSheet.getRange("A1");
cell.setFormula("=GOOGLEFINANCE(\""+market+":"+symbol+"\",\"price\",TODAY()-"+daysBack+",TODAY())");
dataSheet.getRange("C1").setValue(symbol);
var datalastRow = dataSheet.getLastRow();
for(var i = 3; i <= datalastRow; i++)
{
var pastRow = i - 1;
var cell = dataSheet.getRange(i,3);
cell.setFormula("=B"+i+"-B"+pastRow+"");
}
for(var i = 3; i <= datalastRow; i++)
{
var pastRow = i - 1;
var cell = dataSheet.getRange(i,4);
cell.setFormula("=(C"+i+"/B"+pastRow+")*100");
}
var percentGainTotal = 0;
var percentDownTotal = 0;
var consecutiveDaysGain = 0;
for(var i = 3; i <= datalastRow; i++)
{
var currentValue = dataSheet.getRange(i,4).getValue();
if(currentValue > percentGain)
{
percentGainTotal++;
}
if(currentValue < percentDown * -1)
{
percentDownTotal++;
}
}
for(var i = datalastRow; i >= 3; i--)
{
var currentValue = dataSheet.getRange(i,4).getValue();
if(currentValue > 0)
{
consecutiveDaysGain++;
}
else
{
break;
}
}
setupSheet.getRange(row,5).setValue(percentGainTotal);
setupSheet.getRange(row,4).setValue(percentDownTotal);
setupSheet.getRange(row,6).setValue(consecutiveDaysGain);
setupSheet.getRange(row,7).setValue(datalastRow-1);
}
Related Posts
![Back Up Google Drive Files](https://codewithcurt.com/wp-content/uploads/2023/04/Back-Google-Drive-Files-WS-150x150.png)
![Embed Chart Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/11/Embed-Chart-GS-WS-150x150.png)
![Search Google Drive Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/11/Search-Google-Drive-GS-WS-150x150.png)
![Create Custom Menu Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/10/Create-Custom-Menu-GS-WS-150x150.png)
![Get and Set Value Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/07/Get-and-Set-Value-GS-WS-150x150.png)
![Create Data Validation Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/04/Create-Data-Validation-GS-WS-150x150.png)
![Scrape Amazon Website Googe Sheets](https://codewithcurt.com/wp-content/uploads/2022/03/Scrape-Amazon-Website-GS-WS-150x150.png)
![Alert Popups on Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/03/Alert-Popups-on-GS-WS-150x150.png)
![Combine Multiple Spreadsheets Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/02/Combine-Multiple-Spreadsheets-GS-WS-150x150.png)
![Last Row and Column Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/04/Last-Row-and-Column-GS-WS-150x150.png)
![Time and Distance Map Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/02/Time-and-Distance-GS-WS-150x150.png)
![Create Dependent Dropdown Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/01/Create-Dependent-Dropdown-GS-WS-150x150.png)
![Call Rest API Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/01/Call-Rest-API-GS-WS-150x150.png)
![Google Doc From Apps Script](https://codewithcurt.com/wp-content/uploads/2021/12/Google-Doc-From-Apps-Script-WS-150x150.png)
![How to Clear, Delete, Insert, and Replace Row Google Sheets](https://codewithcurt.com/wp-content/uploads/2021/12/How-to-Clear-Delete-Insert-and-Replace-Row-GS-WS-150x150.png)
![Email Last Update Google Sheet](https://codewithcurt.com/wp-content/uploads/2021/12/Email-Last-Update-GS-WS-150x150.png)
![JSON Web Service Google Sheets](https://codewithcurt.com/wp-content/uploads/2021/11/Web-Service-GS-WS-150x150.png)
![Scrape Website Google Sheet](https://codewithcurt.com/wp-content/uploads/2021/02/Scrape-Website-GS-WS-150x150.png)
![Query Function Google Sheets](https://codewithcurt.com/wp-content/uploads/2021/02/Query-Function-WS-150x150.png)
![Copy Row Google Sheet to Google Sheet](https://codewithcurt.com/wp-content/uploads/2021/02/Copy-Row-Sheet-to-Sheet-WS-150x150.png)