BitCoin Trend.EXCEL.yaml

name: BitCoin Trend
description: ''
host: EXCEL
api_set: {}
script:
  content: |
    $("#setup").click(() => tryCatch(setup));
    $("#run").click(() => tryCatch(run));
    $("#stop").click(() => tryCatch(stop));

    var isPending = false;
    var isStop = false;
    var prices = [];
    var candleChart;
    const sheetName = "Price";
    const dataSheetName = "PriceData";
    const tableName = "PricesTable";
    const period = 8;
    const priceAddr = "E2";

    async function setup() {
      await Excel.run(async function(ctx) {
        var sheet = ctx.workbook.worksheets.getItemOrNullObject(sheetName);
        var dataSheet = ctx.workbook.worksheets.getItemOrNullObject(dataSheetName);
        await ctx.sync();

        if (!sheet.isNullObject) {
          sheet.delete();
        }

        if (!dataSheet.isNullObject) {
          dataSheet.delete();
        }

        sheet = ctx.workbook.worksheets.add(sheetName);
        sheet.activate();
        dataSheet = ctx.workbook.worksheets.add(dataSheetName);
        dataSheet.visibility = Excel.SheetVisibility.hidden;
        dataSheet.getRange("A1:D2").values = [["Open", "High", "Low", "Close"], ["", "", "", ""]];
        var table = dataSheet.tables.add("A1:D2", true);
        table.name = tableName;

        candleChart = null;

        createCurrentPrice();

        await ctx.sync();
      });
    }

    async function run() {
      isStop = false;

      setTimeout(updatePrice, 500);
    }

    async function stop() {
      isStop = true;
    }

    async function updatePrice() {
      var price = await getBtcPrice();
      prices.push(price);
      await Excel.run(async function(ctx) {
        var table = ctx.workbook.tables.getItem(tableName);
        var row;
        if (prices.length < period) {
          var count = table.rows.getCount();
          await ctx.sync();
          row = table.rows.getItemAt(count.value - 1);
        } else {
          row = table.rows.add();
        }

        var values = [[prices[0], Math.max(...prices), Math.min(...prices), prices[prices.length - 1]]];

        row.getRange().values = values;

        if (prices.length >= period) {
          prices = [];
        }

        await ctx.sync();

        if (!candleChart) {
          createCandleChart();
        }

        ctx.workbook.worksheets.getItem(sheetName).getRange(priceAddr).values = [[price]];

        if (!isStop) {
          setTimeout(updatePrice, 1000);
        }
      });
    }

    async function createCandleChart() {
      await Excel.run(async (ctx) => {
        var table = ctx.workbook.tables.getItem(tableName);
        var sheet = ctx.workbook.worksheets.getItem(sheetName);
        sheet.activate();
        candleChart = sheet.charts.add(Excel.ChartType.stockOHLC, table.getDataBodyRange());
        candleChart.setPosition("B4");
        candleChart.title.text = "BTC Price";
        candleChart.legend.visible = false;
        candleChart.axes.categoryAxis.visible = false;
      });
    }

    async function createCurrentPrice() {
      await Excel.run(async (ctx) => {
        var sheet = ctx.workbook.worksheets.getItem(sheetName);
        var range = sheet.getRange("C2:E2");
        range.values = [["BitCoin Price", "", ""]];
        range.format.font.size = 18;

        var rangebt = sheet.getRange("E2");
        rangebt.numberFormat = "0.00";

        rangebt.format.columnWidth = 100;
        await ctx.sync();
      });
    }

    async function getBtcPrice() {
      // If there is already a pending request, skip this iteration:
      if (isPending) {
        return;
      }

      var url = "https://api.huobi.pro/market/tickers";
      isPending = true;

      return $.get({
        url: url,
        cache: false
      })
        .then(function(response) {
          return response.data.filter((item) => item.symbol == "btcusdt")[0].close;
        })
        .then(function(price) {
          return price;
        })
        .catch(function(error) {
          console.error("ERROR: " + JSON.stringify(error));
        })
        .then(function(price) {
          isPending = false;
          return price;
        });
    }

    /** Default helper for invoking an action and handling errors. */
    async function tryCatch(callback) {
      try {
        await callback();
      } catch (error) {
        OfficeHelpers.UI.notify(error);
        OfficeHelpers.Utilities.log(error);
      }
    }
  language: typescript
template:
  content: |-
    <button id="setup" class="ms-Button">
        <span class="ms-Button-label">Setup</span>
    </button>

    <button id="run" class="ms-Button">
        <span class="ms-Button-label">Run</span>
    </button>

    <button id="stop" class="ms-Button">
        <span class="ms-Button-label">Stop</span>
    </button>
  language: html
style:
  content: |-
    .ms-Button {
        margin: 10px;
        display: block;
    }
  language: css
libraries: |
  https://appsforoffice.microsoft.com/lib/beta/hosted/office.js
  @types/office-js

  [email protected]/dist/css/fabric.min.css
  [email protected]/dist/css/fabric.components.min.css

  [email protected]/client/core.min.js
  @types/core-js

  @microsoft/[email protected]/dist/office.helpers.min.js
  @microsoft/[email protected]/dist/office.helpers.d.ts

  [email protected]
  @types/jquery

BitCoin Trend.yaml

name: BitCoin Trend
description: ''
author: lumine2008
host: EXCEL
api_set: {}
script:
    content: |
        $("#setup").click(() => tryCatch(setup));
        $("#run").click(() => tryCatch(run));
        $("#stop").click(() => tryCatch(stop));

        var isPending = false;
        var isStop = false;
        var prices = [];
        var candleChart;
        const sheetName = "Price";
        const dataSheetName = "PriceData";
        const tableName = "PricesTable";
        const period = 8;
        const priceAddr = "E2";

        async function setup() {
            await Excel.run(async function (ctx) {
                var sheet = ctx.workbook.worksheets.getItemOrNullObject(sheetName);
                var dataSheet = ctx.workbook.worksheets.getItemOrNullObject(dataSheetName);
                await ctx.sync();

                if (!sheet.isNullObject) {
                    sheet.delete();
                }

                if (!dataSheet.isNullObject) {
                    dataSheet.delete();
                }

                sheet = ctx.workbook.worksheets.add(sheetName);
                sheet.activate();
                dataSheet = ctx.workbook.worksheets.add(dataSheetName);
                dataSheet.visibility = Excel.SheetVisibility.hidden;
                dataSheet.getRange("A1:D2").values = [
                    ["Open", "High", "Low", "Close"],
                    ["", "", "", ""]
                ];
                var table = dataSheet.tables.add("A1:D2", true);
                table.name = tableName;

                candleChart = null;

                createCurrentPrice();

                await ctx.sync();
            })
        }

        async function run() {
            isStop = false;

            setTimeout(updatePrice, 500);
        }

        async function stop() {
            isStop = true;
        }

        async function updatePrice() {
            var price = await getBtcPrice();
            prices.push(price);
            await Excel.run(async function (ctx) {
                var table = ctx.workbook.tables.getItem(tableName);
                var row;
                if (prices.length < period) {
                    var count = table.rows.getCount();
                    await ctx.sync();
                    row = table.rows.getItemAt(count.value - 1);
                } else {
                    row = table.rows.add();
                }

                var values = [[
                    prices[0],
                    Math.max(...prices),
                    Math.min(...prices),
                    prices[prices.length - 1],            
                ]];

                row.getRange().values = values;

                if (prices.length >= period) {
                    prices = [];
                }

                await ctx.sync();

                if (!candleChart) {
                    createCandleChart();
                }

                ctx.workbook.worksheets.getItem(sheetName).getRange(priceAddr).values = [[price]];

                if (!isStop) {
                    setTimeout(updatePrice, 1000);
                }
            });
        }

        async function createCandleChart() {
            await Excel.run(async (ctx) => {
                var table = ctx.workbook.tables.getItem(tableName);
                var sheet = ctx.workbook.worksheets.getItem(sheetName);
                sheet.activate();
                candleChart = sheet.charts.add(Excel.ChartType.stockOHLC, table.getDataBodyRange());
                candleChart.setPosition("B4");
                candleChart.title.text = "BTC Price";
                candleChart.legend.visible = false;
                candleChart.axes.categoryAxis.visible = false;
            });
        }

        async function createCurrentPrice() {
            await Excel.run(async (ctx) => {
                var sheet = ctx.workbook.worksheets.getItem(sheetName);
                var range = sheet.getRange("C2:E2");
                range.values = [["BitCoin Price", "", ""]];
                range.format.font.size = 18;
                await ctx.sync();
            })
        }

        async function getBtcPrice() {
            // If there is already a pending request, skip this iteration:
            if (isPending) {
                return;
            }

            var url = "https://api.huobi.pro/market/tickers";
            isPending = true;

            return $.get({
                url: url,
                cache: false
            }).then(function (response) {
                return response.data.filter((item) => item.symbol == "btcusdt")[0].close;
            }).then(function (price) {
                return price;
            }).catch(function (error) {
                console.error("ERROR: " + JSON.stringify(error));
            }).then(function (price) {
                isPending = false;
                return price;
            });
        }

        /** Default helper for invoking an action and handling errors. */
        async function tryCatch(callback) {
            try {
                await callback();
            }
            catch (error) {
                OfficeHelpers.UI.notify(error);
                OfficeHelpers.Utilities.log(error);
            }
        }
    language: typescript
template:
    content: |-
        <button id="setup" class="ms-Button">
            <span class="ms-Button-label">Setup</span>
        </button>

        <button id="run" class="ms-Button">
            <span class="ms-Button-label">Run</span>
        </button>

        <button id="stop" class="ms-Button">
            <span class="ms-Button-label">Stop</span>
        </button>
    language: html
style:
    content: |-
        .ms-Button {
            margin: 10px;
            display: block;
        }
    language: css
libraries: |
    https://appsforoffice.microsoft.com/lib/beta/hosted/office.js
    @types/office-js

    [email protected]/dist/css/fabric.min.css
    [email protected]/dist/css/fabric.components.min.css

    [email protected]/client/core.min.js
    @types/core-js

    @microsoft/[email protected]/dist/office.helpers.min.js
    @microsoft/[email protected]/dist/office.helpers.d.ts

    [email protected]
    @types/jquery