Advertisement
RemcoE33

FIFO report

Aug 30th, 2021
129
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. function onOpen(e){
  2.   SpreadsheetApp.getUi().createMenu('Create report')
  3.     .addItem('Rebuild report', 'createReport')
  4.     .addToUi();
  5. }
  6.  
  7. function createReport() {
  8.   const ss = SpreadsheetApp.getActiveSpreadsheet();
  9.   const inputSheet = ss.getSheetByName('Fifo');
  10.   const report = ss.getSheetByName('Report');
  11.  
  12.   const data = inputSheet.getRange(2, 1, inputSheet.getLastRow() - 1, inputSheet.getLastColumn()).getValues();
  13.   const filterd = data.filter(row => row[2] == 'Buy' || row[2] == 'Sell');
  14.   const sorted = filterd.sort((a, b) => {
  15.     if (a[0] < b[0]) return -1
  16.     if (a[0] > b[0]) return 1
  17.     else return 0
  18.   })
  19.  
  20.   const securityObject = sorted.reduce((acc, curr) => {
  21.     if (curr[1] in acc) {
  22.       if (curr[2] in acc[curr[1]]) {
  23.         acc[curr[1]][curr[2]].push(curr)
  24.       } else {
  25.         acc[curr[1]] = { ...acc[curr[1]], [curr[2]]: [curr] }
  26.       }
  27.     } else {
  28.       acc[curr[1]] = { [curr[2]]: [curr] }
  29.     }
  30.     return acc
  31.   }, {});
  32.  
  33.   const objects = [];
  34.  
  35.   Object.keys(securityObject).forEach(ticker => {
  36.     const tic = securityObject[ticker];
  37.     let index = 0;
  38.  
  39.     tic.Sell.forEach(sell => {
  40.       const [date, security, , quanity, total, price, account, category] = sell;
  41.       let totalBuy = 0;
  42.       let remainder = quanity;
  43.  
  44.       do {
  45.         let [, , , buyQuanity, , buyPrice] = tic.Buy[index];
  46.         console.log(`Ticker: ${security} | BuyQuantity: ${buyQuanity} | Remainder ${remainder} | Index: ${index}`);
  47.         if (buyQuanity < remainder) {
  48.           totalBuy += (buyQuanity * buyPrice);
  49.           remainder -= buyQuanity;
  50.           index++;
  51.         } else {
  52.           totalBuy += (remainder * buyPrice);
  53.           securityObject[ticker].Buy[index][3] = (buyQuanity - remainder);
  54.           remainder = 0;
  55.           if (buyQuanity < 1) {
  56.             index++;
  57.           }
  58.         }
  59.  
  60.       } while (remainder > 0);
  61.       objects.push({
  62.         date,
  63.         security,
  64.         account,
  65.         category,
  66.         totalBuy,
  67.         total,
  68.         result: total - totalBuy
  69.       })
  70.     })
  71.   })
  72.  
  73.   const convertToSheetsArray = [["Sell Date", "Security", "Account", "Category", "Total Buy", "Total Sell", "Result"]]
  74.   objects.forEach(obj => convertToSheetsArray.push(Object.values(obj)));
  75.  
  76.   const sheetsArray = convertToSheetsArray.sort((a, b) => {
  77.     if (a[0] < b[0]) return -1
  78.     if (a[0] > b[0]) return 1
  79.     else return 0
  80.   })
  81.  
  82.   report.getDataRange().clearContent();
  83.   report.getRange(1,1,sheetsArray.length, 7).setValues(sheetsArray);
  84. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement