In a previous blog post I mentioned that I use Google Sheets to track, monitor and analyze my stock portfolio. I have several sheets that organize my stocks into different categories by industry:
Trading-Investments
Tech-Financial-Misc
Gold-Silver
Energy
Dividend Stocks
My long term income stocks are in the Dividend Stocks category while the stocks I trade are in the other categories. I have written a Google Apps Script that uses the historical prices of a stock to calculate several technical indicators. I use these indicators to identify short term bullish or bearish moves in a stock. I only hold the stock or options for several days to two weeks with the probability of making 25-50 cents per options contract. The historical data for each stock is pulled for free from a website called alphavantage.
Traders will find that the 24 hours access Google Sheets and Google Apps Script give them is invaluable. You only need an internet connection and you can do you stock analysts on a beach or in the bathroom. I have included a snippet of my code below.
==================
function importCSVFromWeb(stock_symbol, own_options, refresh, cell_refresh) {
// Provide the historical prices from Google Finance full URL as CSV file.
//
// Date-Open-High-Low-Close-Volume
//
//var csvUrl = “http://www.google.com/finance/historical?q=”+stock_symbol+”&output=csv”;
//var csvUrl = “https://www.alphavantage.co/query?function=TIME_SERIES_DAILY_ADJUSTED&symbol=AAL&apikey=7P9H2Y2Y5WQO77LR&datatype=csv”;
//var csvUrl = “https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=”+stock_symbol+”&apikey=7P9H2Y2Y5WQO77LR&datatype=csv”;
var csvUrl = “https://www.alphavantage.co/query?function=TIME_SERIES_DAILY_ADJUSTED&symbol=”+stock_symbol+”&apikey=7P9H2Y2Y5WQO77LR&datatype=csv”;
var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
var csvData = Utilities.parseCsv(csvContent);
//———————————————————————————————
//——– Debug using aa.csv (AA historical prices file in Google Drive) ————
//var file = DriveApp.getFilesByName(“aa.csv”).next();
//var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
//———————————————————————————————
//——– Debug using AA historical prices from Google Finance Website ———-
//var csvUrl = “http://www.google.com/finance/historical?q=AA&output=csv”;
//var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
//var csvData = Utilities.parseCsv(csvContent);
//———————————————————————————————
// SMA(n period): n period sum / n
// Multiplier(n period): (2 / (Time periods + 1) ); eg: 10 period = (2 / (10 + 1) ) = 0.1818 (18.18%)
// EMA: {Close – EMA(previous day)} x multiplier + EMA(previous day).
var csvdata_length = csvData.length;
// SMA – 13 day
var ema_13day_weighting = (2 / (13 + 1));
var ema_13day = new Array(csvdata_length – 12);
var sma_day13 = 0.0;
var index_sma13 = (csvdata_length – 1);
var index_max13 = index_sma13 – 13;
===================================================