Advertisement
RemcoE33

fmcloud

Aug 12th, 2021 (edited)
118
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.   Created by RemcoE33
  3.   Docs: https://fmpcloud.io/documentation
  4.   Apps script qoutas: https://developers.google.com/apps-script/guides/services/quotas
  5. */
  6.  
  7. function onOpen(e) {
  8.   SpreadsheetApp.getUi().createMenu('fmpcloud')
  9.     .addItem('Set API key', "storeAPIkey")
  10.     .addItem('Run all tickers', 'getAllData')
  11.     .addToUi();
  12. }
  13.  
  14. function storeAPIkey() {
  15.   const key = SpreadsheetApp.getUi().prompt('Enter API key:').getResponseText();
  16.   ScriptProperties.setProperty('apikey', key);
  17. }
  18.  
  19. function getAllData() {
  20.   console.time('Timer');
  21.   const errors = [];
  22.   const ss = SpreadsheetApp.getActiveSpreadsheet();
  23.   const tickersSheet = ss.getSheetByName('Tickers');
  24.   const tickers = tickersSheet.getRange(2, 1, tickersSheet.getLastRow() - 1).getValues().flat();
  25.   const apikey = ScriptProperties.getProperty('apikey');
  26.  
  27.   const fmpcloud = {
  28.     Balance_Y: `https://fmpcloud.io/api/v3/balance-sheet-statement/###?limit=120&apikey=${apikey}`,
  29.     Balance_Q: `https://fmpcloud.io/api/v3/balance-sheet-statement/###?period=quarter&limit=400&apikey=${apikey}`,
  30.     Income_Q: `https://fmpcloud.io/api/v3/income-statement/###?period=quarter&limit=400&apikey=${apikey}`,
  31.     Income_Y: `https://fmpcloud.io/api/v3/income-statement/###?limit=120&apikey=${apikey}`,
  32.     CashFlow_Y: `https://fmpcloud.io/api/v3/cash-flow-statement/###?limit=120&apikey=${apikey}`,
  33.     CashFlow_Q: `https://fmpcloud.io/api/v3/cash-flow-statement/###?period=quarter&limit=400&apikey=${apikey}`,
  34.     Ratios: `https://fmpcloud.io/api/v3/ratios/###?limit=40&apikey=${apikey}`,
  35.     Metrics: `https://fmpcloud.io/api/v3/key-metrics/###?limit=40&apikey=${apikey}`,
  36.     Press: `https://fmpcloud.io/api/v3/press-releases/###?limit=100&apikey=${apikey}`,
  37.     News: `https://fmpcloud.io/api/v3/stock_news?tickers=###&limit=100&apikey=${apikey}`,
  38.     Surprises: `https://fmpcloud.io/api/v3/earnings-surpises/###?apikey=${apikey}`,
  39.     Transcript: `https://fmpcloud.io/api/v3/earning_call_transcript/###?quarter=3&year=2020&apikey=${apikey}`
  40.   }
  41.  
  42.   const urlsAndSheetnames = Object.entries(fmpcloud);
  43.  
  44.   tickers.forEach((tic, index) => {
  45.     urlsAndSheetnames.forEach(endpoint => {
  46.       let [sheetname, url] = endpoint;
  47.       const tickerUrl = url.replace('###', tic);
  48.       try{
  49.         console.log(`${tic} | ${sheetname}`);
  50.         handleAPI(sheetname, tickerUrl, index);
  51.       } catch (err){
  52.         errors.push(err);
  53.         console.log(`${tic} | ${sheetname} | ${err}`);
  54.       }
  55.     })
  56.   })
  57.  
  58.   console.timeEnd('Timer');
  59.  
  60.   if(errors.length > 0){
  61.     SpreadsheetApp.getUi().alert(errors.join('## '));
  62.   }
  63.  
  64. }
  65.  
  66. function handleAPI(sheetname, url, index) {
  67.   const ss = SpreadsheetApp.getActiveSpreadsheet();
  68.   const sheet = ss.getSheetByName(sheetname);
  69.  
  70.   const response = UrlFetchApp.fetch(url);
  71.   const dataAll = JSON.parse(response.getContentText());
  72.   const dataRows = dataAll;
  73.  
  74.   const rowHeaders = Object.keys(dataRows[0]);
  75.   const rows = [rowHeaders];
  76.   for (let i = 0; i < dataRows.length; i++) {
  77.     const rowData = [];
  78.     for (let j = 0; j < rowHeaders.length; j++) {
  79.       rowData.push(dataRows[i][rowHeaders[j]]);
  80.     }
  81.     rows.push(rowData);
  82.   }
  83.  
  84.   if (index == 0) {
  85.     sheet.getDataRange().clearContent();
  86.   } else {
  87.     rows.shift()
  88.   }
  89.   sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, rows[0].length).setValues(rows);
  90.  
  91. }
  92.  
  93.  
  94.  
  95.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement