How to filter/sum array data by year/quarter?

Question

Trying to consolidate dividend data by year and quarter from an object from this URL. I was able to retrieve dividend date and dividend amount from the object and convert the dividend date to YYYY Q format with the following codes. From the results, I like to remove any dividend arrays outside of year 2018 and 2019, and consolidate the remainder by year and quarter only between 2018 and 2019, like this picture. I like to put 0 if no dividend data in a given quarter, and sum any multiple dividend amounts in a given quarter. How can I achieve that?

function test() {
  const url = 'https://query1.finance.yahoo.com/v8/finance/chart/BAYRY?formatted=true&lang=en-US&region=US&interval=1d&period1=1451624400&period2=1577854799&events=div&useYfid=true&corsDomain=finance.yahoo.com';
  const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true }).getContentText();;
  const obj = JSON.parse(res);
  const dividend_id = Object.keys(obj.chart.result[0].events.dividends);
  var dividend = dividend_id.map((id => [
    obj.chart.result[0].events.dividends[id].date,
    obj.chart.result[0].events.dividends[id].amount]));

  var dividend = dividend.map(arr => [new Date(arr[0] * 1000).toLocaleDateString('en-US'), arr[1]]);
  console.log(dividend)

  var dividend = dividend.map(arr => [(arr[0].slice(-4) + ' Q' + (getQuarter(new Date(arr[0])))), arr[1]]);
  console.log(dividend)

  function getQuarter(date = new Date()) {
    return Math.floor(date.getMonth() / 3 + 1);
  }

}

Answer 1

Here is how I would do it. I would use the Date to get the year and build an array for each year [year,0,0,0,0]. Then use the year to find the array for that year and the month to find the quarter as index into the arrays.

function getDividends() {
  const url = 'https://query1.finance.yahoo.com/v8/finance/chart/BAYRY?formatted=true&lang=en-US&region=US&interval=1d&period1=1451624400&period2=1577854799&events=div&useYfid=true&corsDomain=finance.yahoo.com';
  const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true }).getContentText();;
  const obj = JSON.parse(res);
  const dividend_id = Object.keys(obj.chart.result[0].events.dividends);
  var dividend = dividend_id.map((id => [
    obj.chart.result[0].events.dividends[id].date,
    obj.chart.result[0].events.dividends[id].amount]));

  let quarters = [];

  dividend.forEach( item => {
      let day = new Date(item[0]*1000);
      let year = day.getFullYear();
      if( year > 2017 ) {
        let i = quarters.findIndex( quarter => quarter[0] === year );
        if( i < 0 ) {
          quarters.push([year,0,0,0,0]);
          i = quarters.length-1;
        }
        let j = Math.floor(day.getMonth()/4)+1;
        quarters[i][j] = quarters[i][j]+item[1];
      }
    }
  );

  dividend = [];
  
  quarters = quarters.forEach( quarter => {
      for( let i=1; i<5; i++ ) {
        dividend.push([(quarter[0]+" Q"+i),quarter[i]]);
      }
    }
  )
  console.log(dividend);
}

Execution log

9:33:08 AM  Notice  Execution started
9:33:09 AM  Info    
  [ [ '2018 Q1', 0 ],
  [ '2018 Q2', 1.2289999999999999 ],
  [ '2018 Q3', 0 ],
  [ '2018 Q4', 0 ],
  [ '2019 Q1', 0.789 ],
  [ '2019 Q2', 0 ],
  [ '2019 Q3', 0 ],
  [ '2019 Q4', 0 ] ]
9:33:09 AM  Notice  Execution completed

Reference

• Array.forEach()
• Date object
• Array.findIndex()