Skip to content

Instantly share code, notes, and snippets.

@bonlime

bonlime/binance_script.js

Last active January 11, 2022 15:32
Show Gist options
  • Save bonlime/b3d2cfdb6f4a2d9003eb6d37256783bb to your computer and use it in GitHub Desktop.
Save bonlime/b3d2cfdb6f4a2d9003eb6d37256783bb to your computer and use it in GitHub Desktop.
Download ZIP
Raw
binance_script.js
// Описание и настройки скрипта: https://www.all-connect.site/spreadsheets_scripts/binance-future-coin-m
// Версия от 12.10.2021
// modified by bonlime on 29.12.2021
// added BNB to total asset calculation. this could lead to misleading growth in case of BNB price growth
// but this is still better than totally excluding it
// upd. on 11.01.2022
// returned margin calculation (but I still don't really undestand what it means)
const sheet = "1X................UF4"; // id вашей гугл-таблицы
const key_fut_M = "QY.........qjy"; //Ключ доступа фьючерсы Coin
const secret_fut_M = "yg.............My" // Секретный ключ фьючерсы Coin
const key_fut_USDT = "h3....WE"; //Ключ доступа фьючерсы USDT
const secret_fut_USDT = "RL.....Lt" // Секретный ключ фьючерсы USDT
const key_spot = "9P.............76"; //ключ доступа к SPOT
const secret_spot = "j...............9"; //секретный ключ к SPOT
const BTC_on = "No" //Чтоб отключить, удалите Yes или впишите No
const BNB_on = "No" //Чтоб отключить, удалите Yes или впишите No
const USDT_Fut_on = "Yes" //Чтоб отключить, удалите Yes или впишите No
const Sum_info = "No" //Чтоб отключить, удалите Yes или впишите No
const Spot_on = "No" //Чтоб отключить, удалите Yes или впишите No
const btc_start = 0.05;
const bnb_start = 5;
const usdt_f_start = 200;
const spot_usd = 1000;
const GTM_TIME = "GMT+3:00"; //Укажите тут свой часовой пояс. Для Москвы GMT+2:00, для Киева GMT+3:00
//////////////////////////////////////////////////
// Можете поддержать проект донатом: //
// Переводите любую волюту по почте: //
// elvenseolib@gmail.com //
// Перевод внутри Бинанс - без комиссии //
/////////////////СПАСИБО!!!///////////////////////
var spreadsheet = SpreadsheetApp.openById(sheet);
if (Sum_info == "Yes") {
var sum_val = {};
}
function Run_Me() {
if (BTC_on == "Yes") {
setTable("BTC")
}
if (BNB_on == "Yes") {
setTable("BNB")
}
if (Sum_info == "Yes") {
var sheetData = spreadsheet.getSheetByName('Sum_Info');
setCaptionSum_info(sheetData);
setSumDate(sheetData);
setBorder(sheetData);
}
if (Spot_on == "Yes") {
getSpotBalance();
}
if (USDT_Fut_on == "Yes")
futUSDT_balance();
}
function setTable(val) {
var spreadsheet = SpreadsheetApp.openById(sheet);
if (val == "BTC")
var sheetData = spreadsheet.getSheetByName('BTC');
if (val == "BNB")
var sheetData = spreadsheet.getSheetByName('BNB');
setCaption(sheetData, val);
setBorder(sheetData);
sheetData.insertRowBefore(2);
let data = getBTCcource(val);
sheetData.getRange(2, 15).setValue(data[10].toFixed(0));
sheetData.getRange(2, 16).setValue(data[11].toFixed(0));
sheetData.getRange(2, 16).setBackgroundColor('#fce5cd');
data.pop();
data.pop();
sheetData.getRange(2, 1, 1, 10).setValues([data]);
var pribOl_btc = sheetData.getRange(2, 2).getValue() - sheetData.getRange(3, 2).getValue();
var pribOl_prc = pribOl_btc / sheetData.getRange(2, 2).getValue() * 100;
var pribOl_usd = sheetData.getRange(2, 3).getValue() - sheetData.getRange(3, 3).getValue();
var pribOl_prcc = pribOl_usd / sheetData.getRange(2, 3).getValue() * 100;
sheetData.getRange(2, 11).setValue(pribOl_btc.toFixed(8) * 1);
sheetData.getRange(2, 12).setValue(pribOl_prc.toFixed(4) * 1);
sheetData.getRange(2, 13).setValue(pribOl_usd.toFixed(2) * 1);
sheetData.getRange(2, 14).setValue(pribOl_prcc.toFixed(4) * 1);
fillRange(sheetData.getRange(2, 5));
fillRange(sheetData.getRange(2, 11));
fillRange(sheetData.getRange(2, 12));
fillRange(sheetData.getRange(2, 13));
fillRange(sheetData.getRange(2, 14));
fillRange(sheetData.getRange(2, 17));
fillRange(sheetData.getRange(2, 18));
console.log("setTable")
}
function getBTCcource(val) {
let balance = getBalance(val);
let t = Utilities.formatDate(new Date(), GTM_TIME, "dd.MM.yy HH:mm");
var dop_url = "/dapi/v1/positionRisk?";
let positions = getDateDromBinance(dop_url, val);
var pos_val=positions.filter(function(item){
if (item.symbol.match(/_PERP/))
return item
})
if (val == "BTC") {
var margin = (pos_val[0].positionAmt * 100 / pos_val[0].leverage / pos_val[0].markPrice).toFixed(4) * 1
} else {
var margin = (pos_val[0].positionAmt * 10 / pos_val[0].leverage / pos_val[0].markPrice).toFixed(4) * 1
}
var temp = [t,
balance[0],
Math.round(balance[0] * pos_val[0].markPrice * 1),
Math.round(pos_val[0].markPrice),
margin,
pos_val[0].positionAmt,
pos_val[0].unRealizedProfit,
Math.round(pos_val[0].markPrice * pos_val[0].unRealizedProfit * 1),
balance[1],
Math.round(pos_val[0].markPrice * balance[1] * 1),
pos_val[0].entryPrice * 1,
pos_val[0].liquidationPrice * 1];
if (Sum_info == "Yes") {
sum_val.time = t;
sum_val[val + "_balance"] = balance[0].toFixed(4) * 1;
sum_val[val + "_bal_USD"] = Math.round(balance[0] * pos_val[0].markPrice * 1);
sum_val[val + "cource"] = Math.round(pos_val[0].markPrice);
sum_val[val + "posAmt"] = pos_val[0].positionAmt;
sum_val[val + "_UNPUSD"] = Math.round(pos_val[0].markPrice * pos_val[0].unRealizedProfit * 1);
}
console.log(temp)
console.log("getBTCcource")
return temp;
}
function setCaption(sheet, val) {
sheet.getRange(1, 1).setValue("Дата");
sheet.getRange(1, 2).setValue("Баланс, " + val);
sheet.getRange(1, 3).setValue("Баланс, $");
sheet.getRange(1, 4).setValue("Курс, $");
sheet.getRange(1, 5).setValue("Маржа, " + val);
sheet.getRange(1, 6).setValue("Контракты, шт.");
sheet.getRange(1, 7).setValue("Позиция, " + val);
sheet.getRange(1, 8).setValue("Позиция, $");
sheet.getRange(1, 9).setValue("Баланс маржи, " + val);
sheet.getRange(1, 10).setValue("Баланс маржи, $");
sheet.getRange(1, 11).setValue("Прибыль, " + val);
sheet.getRange(1, 12).setValue("%");
sheet.getRange(1, 13).setValue("Прибыль, $");
sheet.getRange(1, 14).setValue("%");
sheet.getRange(1, 15).setValue("Цена входа");
sheet.getRange(1, 16).setValue("Цена ликвидации");
sheet.getRange(1, 1, 1, 12).setHorizontalAlignment("center");
sheet.getRange(2, 1, 1, 12).setHorizontalAlignment("center");
var renge = sheet.getRange(1, 1, 1, 18)
renge.setBackgroundColor('#fce5cd')
renge.setFontWeight("bold");
console.log("setCaption")
}
function setBorder(sheet) {
var lr = sheet.getLastRow();
var lc = sheet.getLastColumn();
sheet.getRange(2, 1, 1, lc).setBorder(false, false, false, false, false, false, "black", SpreadsheetApp.BorderStyle.DOTTED);
sheet.getRange(2, 1, 1, lc).setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.DOTTED);
sheet.getRange(1, 1, 1, lc).setBorder(false, false, false, false, false, false, "black", SpreadsheetApp.BorderStyle.SOLID);
sheet.getRange(1, 1, 1, lc).setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
}
function fillRange(range) {
range.setBackgroundColor('white');
if (range.getValue() > 0) {
range.setBackgroundColor('#bbffa3')
} else if (range.getValue() < 0) {
range.setBackgroundColor('#ffb0b0')
}
}
function getBalance(val) {
var dop_url = "/dapi/v1/balance?";
let resp_data = getDateDromBinance(dop_url, val);
var val_balance=resp_data.filter(function(item){
if (item.asset==val)
return item
})
var spreadsheet = SpreadsheetApp.openById(sheet);
if (val == "BTC") {
var balance = [];
balance[0] = parseFloat(val_balance[0].balance);
balance[1] = parseFloat(val_balance[0].availableBalance + val_balance[0].crossUnPnl);
if (btc_start > 0) {
var sheetData = spreadsheet.getSheetByName('BTC');
sheetData.getRange(1, 17).setValue("Разница с нач. бал.");
sheetData.getRange(1, 18).setValue("% с нач. бал");
sheetData.getRange(2, 17).setValue((balance[0] - btc_start).toFixed(4) * 1);
sheetData.getRange(2, 18).setValue(((balance[0] - btc_start) / btc_start).toFixed(4) * 100);
}
}
if (val == "BNB") {
var balance = [];
balance[0] = parseFloat(val_balance[0].balance);
balance[1] = parseFloat(val_balance[0].availableBalance + val_balance[0].crossUnPnl);
if (bnb_start > 0) {
var sheetData = spreadsheet.getSheetByName('BNB');
sheetData.getRange(1, 17).setValue("Разница с нач. бал.");
sheetData.getRange(1, 18).setValue("% с нач. бал");
sheetData.getRange(2, 17).setValue((balance[0] - bnb_start).toFixed(4) * 1);
sheetData.getRange(2, 18).setValue(((balance[0] - bnb_start) / bnb_start).toFixed(4) * 100);
}
}
console.log("getBalance")
return balance;
}
function getDateDromBinance(options, val) {
var base_url = "https://dapi.binance.com";
var curTime = Number(new Date().getTime()).toFixed(0);
if (val == "BTC")
var string = "pair=BTCUSD&timestamp=" + curTime;
if (val == "BNB")
var string = "pair=BNBUSD&timestamp=" + curTime;
var sKey = Utilities.computeHmacSha256Signature(string, secret_fut_M);
sKey = sKey.map(function (e) {
var v = (e < 0 ? e + 256 : e).toString(16);
return v.length == 1 ? "0" + v : v;
}).join("");
var params = {
'method': 'get',
'headers': { 'X-MBX-APIKEY': key_fut_M },
'muteHttpExceptions': true,
};
var url = base_url + options + string + "&signature=" + sKey;
var data = UrlFetchApp.fetch(url, params);
var i = 0;
while (data.getResponseCode() !== 200 && i < 20) {
Utilities.sleep(60000)
console.log("Ждем минуту")
var data = UrlFetchApp.fetch(url, params);
i++
}
var parsedResponse = JSON.parse(data.getContentText());
return parsedResponse;
}
function setSumDate(sheetData) {
sheetData.insertRowBefore(2);
var i = 1;
for (var key in sum_val) {
sheetData.getRange(2, i).setValue(sum_val[key]);
i++
}
sheetData.getRange(2, 12).setValue(sum_val.BNB_UNPUSD + sum_val.BTC_UNPUSD);
sheetData.getRange(2, 13).setValue(sum_val.BTC_bal_USD + sum_val.BNB_bal_USD);
let b1 = sheetData.getRange(2, 13).getValue() - sheetData.getRange(3, 13).getValue();
sheetData.getRange(2, 14).setValue(b1);
let b2 = ((b1 / sheetData.getRange(3, 13).getValue()) * 100).toFixed(3) * 10 / 10;
sheetData.getRange(2, 15).setValue(b2);
fillRange(sheetData.getRange(2, 6));
fillRange(sheetData.getRange(2, 11));
fillRange(sheetData.getRange(2, 12));
fillRange(sheetData.getRange(2, 14));
fillRange(sheetData.getRange(2, 15));
console.log("setSumDate")
}
function setCaptionSum_info(sheet) {
sheet.getRange(1, 1).setValue("Дата");
sheet.getRange(1, 2).setValue("Баланс BTC");
sheet.getRange(1, 3).setValue("Баланс BTC в $");
sheet.getRange(1, 4).setValue("Курс BTC-$");
sheet.getRange(1, 5).setValue("Контрактов BTC");
sheet.getRange(1, 6).setValue("PNL BTC в $");
sheet.getRange(1, 7).setValue("Баланс BNB");
sheet.getRange(1, 8).setValue("Баланс BNB в $");
sheet.getRange(1, 9).setValue("Курс BNB-$");
sheet.getRange(1, 10).setValue("Контрактов BNB");
sheet.getRange(1, 11).setValue("PNL BNB в $");
sheet.getRange(1, 12).setValue("Итоговый текущий доход в $");
sheet.getRange(1, 13).setValue("Итоговый Баланс в $");
sheet.getRange(1, 14).setValue("Прибыль, $");
sheet.getRange(1, 15).setValue("%");
sheet.getRange(1, 1, 1, 15).setHorizontalAlignment("center");
sheet.getRange(2, 1, 1, 15).setHorizontalAlignment("center");
var renge = sheet.getRange(1, 1, 1, 15)
renge.setBackgroundColor('#fce5cd')
renge.setFontWeight("bold")
console.log("setCaptionSum_info")
}
function getSpotBalance() {
var base_url = "/api/v3/account?";
var data = go2Spot(base_url).balances;
var cource = getSpotCource();
var i = 0;
var j = 0;
var sum = 0;
var sum_lock = 0;
var btccource = cource.BTCUSDT;
data.forEach(function (item) {
if (item.free > 0) {
i++
let c1 = cource[item.asset + "USDT"] * 1;
if (c1) {
let val = item.free * 1;
sum = sum + val * c1;
}
else if (cource[item.asset + "BTC"] * 1) {
let c1 = cource[item.asset + "BTC"] * 1;
let val = item.free * 1;
sum = sum + val * c1 * btccource;
}
}
if (item.locked > 0) {
j++
let c1 = cource[item.asset + "USDT"] * 1;
if (c1) {
let val = item.locked * 1;
sum_lock = sum_lock + val * c1;
}
else if (cource[item.asset + "BTC"] * 1) {
let c1 = cource[item.asset + "BTC"] * 1;
let val = item.locked * 1;
sum_lock = sum_lock + val * c1 * btccource;
}
}
})
var sheet = spreadsheet.getSheetByName('SPOT');
sheet.insertRowBefore(2);
sheet.getRange(1, 1).setValue("Дата");
sheet.getRange(1, 2).setValue("Free USDT");
sheet.getRange(1, 3).setValue("Free, BTC");
sheet.getRange(1, 4).setValue("Курс, $");
sheet.getRange(1, 5).setValue("Валют. шт");
sheet.getRange(1, 6).setValue("Lock USDT");
sheet.getRange(1, 7).setValue("Валют в сделках, шт");
sheet.getRange(1, 8).setValue("Итог USDT");
sheet.getRange(1, 9).setValue("Итог BTC");
sheet.getRange(1, 10).setValue("Изменения USDT");
sheet.getRange(1, 11).setValue("Изменения BTC");
sheet.getRange(1, 12).setValue("% изменения (USDT)");
sheet.getRange(1, 13).setValue("Изменения с начальным USDT");
sheet.getRange(1, 14).setValue(" % Изменения с начальным");
var renge = sheet.getRange(1, 1, 1, 14)
renge.setBackgroundColor('#fce5cd')
renge.setFontWeight("bold");
let t = Utilities.formatDate(new Date(), GTM_TIME, "dd.MM.yy HH:mm");
sheet.getRange(2, 1).setValue(t);
sheet.getRange(2, 2).setValue(sum.toFixed(2) * 1);
sheet.getRange(2, 3).setValue((sum / btccource).toFixed(4) * 1);
sheet.getRange(2, 4).setValue((btccource * 1).toFixed(0) * 1);
sheet.getRange(2, 5).setValue(i);
sheet.getRange(2, 6).setValue(sum_lock.toFixed(2) * 1);
sheet.getRange(2, 7).setValue(j);
sheet.getRange(2, 8).setValue((sum + sum_lock).toFixed(2) * 1);
sheet.getRange(2, 9).setValue(((sum + sum_lock) / btccource).toFixed(4) * 1);
let prev = sheet.getRange(3, 8).getValue();
sheet.getRange(2, 10).setValue((sum + sum_lock - prev).toFixed(2) * 1);
let prev1 = sheet.getRange(3, 9).getValue();
sheet.getRange(2, 11).setValue((((sum + sum_lock) / btccource) - prev1).toFixed(4) * 1);
sheet.getRange(2, 12).setValue(((sum + sum_lock - prev) / prev).toFixed(4) * 100);
sheet.getRange(2, 13).setValue((sum + sum_lock - spot_usd).toFixed(2) * 1);
sheet.getRange(2, 14).setValue(((sum + sum_lock - spot_usd) / spot_usd).toFixed(4) * 100);
fillRange(sheet.getRange(2, 10));
fillRange(sheet.getRange(2, 11));
fillRange(sheet.getRange(2, 12));
fillRange(sheet.getRange(2, 13));
fillRange(sheet.getRange(2, 14));
console.log("getSpotBalance")
}
function getSpotCource() {
var base_url = "https://api.binance.com/api/v3/ticker/price";
try {
var data = UrlFetchApp.fetch(base_url);
} catch (err) {
var base_url = "https://api1.binance.com/api/v3/ticker/price";
var data = UrlFetchApp.fetch(base_url);
}
var parsedResponse = JSON.parse(data.getContentText());
var cource = {};
parsedResponse.forEach(function (item) {
cource[item.symbol] = item.price;
})
cource.USDTUSDT = 1;
console.log("getSpotCource")
return cource;
}
function go2Spot() {
var getkey = getSecret(key_spot, secret_spot);
var url = "https://api.binance.com/api/v3/account?" + getkey[0];
var data = UrlFetchApp.fetch(url, getkey[1]);
var i = 0;
while (data.getResponseCode() !== 200 && i < 3) {
Utilities.sleep(10000)
i++
var getkey = getSecret(key_spot, secret_spot);
var new_url = "https://api" + i + ".binance.com/api/v3/account?" + getkey[0];
var data = UrlFetchApp.fetch(new_url, getkey[1]);
}
var parsedResponse = JSON.parse(data.getContentText());
return parsedResponse
}
function futUSDT_balance() {
var sheet = spreadsheet.getSheetByName('USDT_F');
setFUtable(sheet)
var dop_url = "/fapi/v2/balance?"
var base_url = "https://fapi.binance.com" + dop_url;
var getkey = getSecret(key_fut_USDT, secret_fut_USDT);
var main_data = [];
var url = base_url + getkey[0];
var data = UrlFetchApp.fetch(url, getkey[1]);
var resp_data = JSON.parse(data.getContentText());
var sum_balance = 0;
var crossUnPnl = 0;
var availableBalance = 0;
for (k in resp_data) {
if (resp_data[k].asset == "USDT" || resp_data[k].asset == "BUSD")
sum_balance = sum_balance + parseFloat(resp_data[k].balance)
crossUnPnl = crossUnPnl + parseFloat(resp_data[k].crossUnPnl)
availableBalance = availableBalance + parseFloat(resp_data[k].availableBalance)
if (resp_data[k].asset == "BNB")
var bnb_balance = parseFloat(getSpotCource()["BNBUSDT"]) * parseFloat(resp_data[k].balance)
}
main_data[0] = Utilities.formatDate(new Date(), GTM_TIME, "dd.MM.yy HH:mm");
main_data[1] = (sum_balance + bnb_balance).toFixed(2) * 1;
main_data[2] = (crossUnPnl).toFixed(2) * 1;
main_data[3] = (sum_balance - availableBalance).toFixed(2) * 1;
url = "https://fapi.binance.com/fapi/v2/positionRisk?" + getkey[0];
var data = UrlFetchApp.fetch(url, getkey[1]);
var parsedResponse = JSON.parse(data.getContentText());
var i = 0;
parsedResponse.forEach(function (item) {
if (item.positionAmt != 0) {
i++
}
})
main_data[4] = i;
main_data[5] = main_data[1] - sheet.getRange(2, 2).getValue();
main_data[6] = (main_data[5] / sheet.getRange(2, 2).getValue()).toFixed(2) * 100;
main_data[7] = main_data[1] - usdt_f_start;
main_data[8] = (main_data[7] / usdt_f_start).toFixed(2) * 100;
sheet.insertRowBefore(2);
sheet.getRange(2, 1, 1, main_data.length).setValues([main_data]);
fillRange(sheet.getRange(2, 5));
fillRange(sheet.getRange(2, 6));
fillRange(sheet.getRange(2, 7));
fillRange(sheet.getRange(2, 8));
fillRange(sheet.getRange(2, 9));
console.log("futUSDT_balance")
}
function setFUtable(sheet) {
var theaders = ["Дата", "Баланс USDT", "PNL", "Маржа", "Валют в работе", "Изменение баланса $", "Изменение %", "Разница с нач. бал.", "% с нач. бал"]
sheet.getRange(1, 1, 1, theaders.length).setValues([theaders]);
var renge = sheet.getRange(1, 1, 1, theaders.length)
renge.setBackgroundColor('#fce5cd')
renge.setFontWeight("bold");
}
function getSecret(key, secret) {
var curTime = Number(new Date().getTime()).toFixed(0);
var string = "timestamp=" + curTime;
var sKey = Utilities.computeHmacSha256Signature(string, secret);
sKey = sKey.map(function (e) {
var v = (e < 0 ? e + 256 : e).toString(16);
return v.length == 1 ? "0" + v : v;
}).join("");
var params = {
'method': 'get',
'headers': { 'X-MBX-APIKEY': key },
'muteHttpExceptions': true,
};
var temp_url = string + "&signature=" + sKey;
return [temp_url, params]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment