Skip to content

Instantly share code, notes, and snippets.

@wannadrunk

wannadrunk/scbam-nav.gs

Last active January 11, 2025 10:09
Show Gist options
  • Save wannadrunk/f2d005e32b89b660ce7be39879a8cc77 to your computer and use it in GitHub Desktop.
Save wannadrunk/f2d005e32b89b660ce7be39879a8cc77 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. wannadrunk revised this gist Jan 11, 2025. 1 changed file with 11 additions and 10 deletions.
    21 changes: 11 additions & 10 deletions scbam-nav.gs
    View file
    Original file line number Diff line number Diff line change
    @@ -1,27 +1,28 @@

    function getSCBAMNAV() {
    var url = 'https://';
    var options = {
    'method': 'get',
    'contentType': 'application/json'
    };

    var today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm');

    var response = UrlFetchApp.fetch(url, options);
    var tableNAV = JSON.parse(response.getContentText());

    var sortedFundCodes = Object.keys(tableNAV).sort();
    var data = sortedFundCodes.map(function(fundcode) {
    return [fundcode, tableNAV[fundcode].navunit, tableNAV[fundcode].navdate];
    });

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SCBAMNAV');
    if (!sheet) {
    sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('SCBAMNAV');
    sheet.appendRow(['Fund Code', 'NAV', 'NAV Date', 'Risk Level']);
    sheet.appendRow(['Fund Code', 'NAV', 'NAV Date']);
    }


    sheet.getRange('E1').setValue(today);
    var range = sheet.getRange('A2:D')
    range.clearContent();

    var sortedFundCodes = Object.keys(tableNAV).sort();
    for (var fundcode of sortedFundCodes) {
    sheet.appendRow([fundcode, tableNAV[fundcode].navunit, tableNAV[fundcode].navdate]);
    }
    sheet.getRange(2, 1, data.length, 3).setValues(data);

    var cell = sheet.getRange('B2:B');
    cell.setNumberFormat('0.0000');
  2. wannadrunk revised this gist Dec 31, 2024. 1 changed file with 33 additions and 51 deletions.
    84 changes: 33 additions & 51 deletions scbam-nav.gs
    View file
    Original file line number Diff line number Diff line change
    @@ -1,57 +1,39 @@

    function getSCBAMNAV() {
    var url = 'https://www.scbam.com/setdatenav';
    var today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd');
    var payload = {'now': today};
    var headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36'};
    var options = {
    'method': 'post',
    'headers': headers,
    'contentType': 'application/json',
    'payload': JSON.stringify(payload)
    };

    var response = UrlFetchApp.fetch(url, options);
    var results = JSON.parse(response.getContentText());
    var url = 'https://';
    var options = {
    'method': 'get',
    'contentType': 'application/json'
    };

    var response = UrlFetchApp.fetch(url, options);
    var tableNAV = JSON.parse(response.getContentText());

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SCBAMNAV');
    if (!sheet) {
    sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('SCBAMNAV');
    }

    sheet.clear();
    sheet.appendRow(['Fund Code', 'NAV', 'NAV Date', 'Risk Level', today]);

    var tableNAV = {};
    var i = 1;
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SCBAMNAV');
    if (!sheet) {
    sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('SCBAMNAV');
    sheet.appendRow(['Fund Code', 'NAV', 'NAV Date', 'Risk Level']);
    }

    var range = sheet.getRange('A2:D')
    range.clearContent();

    var sortedFundCodes = Object.keys(tableNAV).sort();
    for (var fundcode of sortedFundCodes) {
    sheet.appendRow([fundcode, tableNAV[fundcode].navunit, tableNAV[fundcode].navdate]);
    }

    for (var fund of results) {
    if (Array.isArray(fund.data)) {
    for (var x of fund.data) {
    var navunit = x.nav.navunit;
    var navdate = x.nav.navdate;
    var fundcode = x.fund_code;
    var risklevel = x.risk_level
    tableNAV[fundcode] = {"navunit": navunit, "navdate": navdate, "risklevel": risklevel};
    }
    }
    var cell = sheet.getRange('B2:B');
    cell.setNumberFormat('0.0000');

    if (Array.isArray(fund.parent)) {
    for (var x of fund.parent) {
    if (Array.isArray(x.data)) {
    for (var t of x.data) {
    var navunit = t.nav.navunit;
    var navdate = t.nav.navdate;
    var fundcode = t.fund_code;
    var risklevel = t.risk_level
    tableNAV[fundcode] = {"navunit": navunit, "navdate": navdate, "risklevel": risklevel};
    }
    }
    }
    }
    }
    }

    for (var fundcode in tableNAV) {
    sheet.appendRow([fundcode, tableNAV[fundcode].navunit, tableNAV[fundcode].navdate], tableNAV[fundcode].risklevel);
    }
    /**
    * create custom menu
    */
    function onOpen() {
    var ui = SpreadsheetApp.getUi();
    ui.createMenu('SCBAM NAV')
    .addItem('Get NAV', 'getSCBAMNAV')
    .addToUi();
    }
  3. wannadrunk revised this gist Dec 30, 2024. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion scbam-nav.gs
    View file
    Original file line number Diff line number Diff line change
    @@ -43,7 +43,7 @@ function getSCBAMNAV() {
    var navunit = t.nav.navunit;
    var navdate = t.nav.navdate;
    var fundcode = t.fund_code;
    var risklevel = x.risk_level
    var risklevel = t.risk_level
    tableNAV[fundcode] = {"navunit": navunit, "navdate": navdate, "risklevel": risklevel};
    }
    }
  4. wannadrunk revised this gist Dec 30, 2024. No changes.
  5. wannadrunk revised this gist Dec 30, 2024. 1 changed file with 45 additions and 4 deletions.
    49 changes: 45 additions & 4 deletions scbam-nav.gs
    View file
    Original file line number Diff line number Diff line change
    @@ -1,16 +1,57 @@
    function getSCBAMNAV() {
    var url = 'https://www.scbam.com/setdatenav';
    var today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd');
    var payload = {'now': today};

    var headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36'};
    var options = {
    'method': 'post',
    'headers': headers,
    'contentType': 'application/json',
    'payload': JSON.stringify(payload)
    };

    var response = UrlFetchApp.fetch(url, options);
    var results = JSON.parse(response.getContentText());

    var navunit = x.nav.navunit;
    var navdate = x.nav.navdate;
    var fundcode = x.fund_code;
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SCBAMNAV');
    if (!sheet) {
    sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('SCBAMNAV');
    }

    sheet.clear();
    sheet.appendRow(['Fund Code', 'NAV', 'NAV Date', 'Risk Level', today]);

    var tableNAV = {};
    var i = 1;

    for (var fund of results) {
    if (Array.isArray(fund.data)) {
    for (var x of fund.data) {
    var navunit = x.nav.navunit;
    var navdate = x.nav.navdate;
    var fundcode = x.fund_code;
    var risklevel = x.risk_level
    tableNAV[fundcode] = {"navunit": navunit, "navdate": navdate, "risklevel": risklevel};
    }
    }

    if (Array.isArray(fund.parent)) {
    for (var x of fund.parent) {
    if (Array.isArray(x.data)) {
    for (var t of x.data) {
    var navunit = t.nav.navunit;
    var navdate = t.nav.navdate;
    var fundcode = t.fund_code;
    var risklevel = x.risk_level
    tableNAV[fundcode] = {"navunit": navunit, "navdate": navdate, "risklevel": risklevel};
    }
    }
    }
    }
    }

    for (var fundcode in tableNAV) {
    sheet.appendRow([fundcode, tableNAV[fundcode].navunit, tableNAV[fundcode].navdate], tableNAV[fundcode].risklevel);
    }
    }
  6. wannadrunk created this gist Dec 29, 2024.
    16 changes: 16 additions & 0 deletions scbam-nav.gs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,16 @@
    var url = 'https://www.scbam.com/setdatenav';
    var today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd');
    var payload = {'now': today};

    var options = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify(payload)
    };

    var response = UrlFetchApp.fetch(url, options);
    var results = JSON.parse(response.getContentText());

    var navunit = x.nav.navunit;
    var navdate = x.nav.navdate;
    var fundcode = x.fund_code;