Need help (and ready to pay): Building Poloniex trading tool

HCP

legendary

Activity: 2086

Merit: 4363

Quote from: manhdn on July 18, 2017, 12:36:43 AM

I dont want to use menu bcos I want sheet to place buy/sell command auto based on other cell value. With menu I can only do it manually.
So I have an idea to do that,
When the command place a BUY/SELL order succesfully, we will assign amount, date, total, rate to variables, let say a,b,c,d. Then we set other condition to write those variables back to the sheet "output". It means we dont setValue for "output" directly from other cell, the script did it. How do you think?
**Try the last time to help me on those probs to know how far we can do. And if we cant do any thing more, just tell me I will try other way to use this code. Anyway, I had a quite good result. THanks

Your idea probably won't work... the only way to execute parts of the script is to make a function call from a cell right? So, even if you create global variables... and populate them, you'd need to have a function call in each cell that you wanted populated... that would require a minimum of 5 or 6 function calls to populate each cell:

cellB1 =getOrderNumber()
cellB2 =getTradeID()
cellB3 =getType()
cellB4 =getDate()
cellB5 =getAmount()
cellB6 =getRate()
cellB7 =getTotal()

Also, you'd have no way to know how many ResultingTrades there were... there could be multiple resulting from one buy/sell order... (ie. you want to buy 10 coins, but there is only lots of 1coin,3coin,3coin,2coin,4coin for sale... you would end up with at least 3 and possibly 5 trades to fulfil your order for 10 coins)... so you would need to multiple blocks of cells, each making function calls to get the individual values for each "resultingTrade"... there is also the possibility that there are NO resultingTrades if you put in an order that is not immediately fulfilled... so you'd probably need to wrap them all in IF() statements:

CellA2=getNumberResultingTrades()

CellB2=if($A$2>0,getTradeID(0),"")
CellC2=if($A$2>1,getTradeID(1),"")
CellD2=if($A$2>2,getTradeID(2),"")
...
Cellx2=if($A$2>x,getTradeID(x),"")
etc

Code:

var buyData;

function getNumberResultTrades() {
  return buyData["resultingTrades"].length;
}

function getTradeID(num) {
  return buyData["resultingTrades"][num]["tradeId"];
}

function poloniex(....) {
  ...
  var dataAll = JSON.parse(response.getContentText());
  buyData = dataAll;
  ...
}

Then just create "get" functions for each part of the Trade data that you want:

Code:

getTradeType(num) {
  return buyData["resultingTrades"][num]["type"];
}
getTradeDate(num) {
  return buyData["resultingTrades"][num]["date"];
}
getTradeRate(num) {
  return buyData["resultingTrades"][num]["rate"];
}

etc etc
and do more =IF() statements:
CellB3=if($A$2>0,getTradeType(0),"")
CellC3=if($A$2>1,getTradeType(1),"")
CellD3=if($A$2>2,getTradeType(2),"")
...
Cellx3=if($A$2>x,getTradeType(x),"")

CellB4=if($A$2>0,getTradeDate(0),"")
CellC4=if($A$2>1,getTradeDate(1),"")
CellD4=if($A$2>2,getTradeDate(2),"")
...
Cellx4=if($A$2>x,getTradeDate(x),"")

etc

Another option could be to just return the API responses as strings, and use SPLIT() functions... but I'm not sure if the keys in the JSON objects are always in the same order... so that may cause issues as well...

manhdn

newbie

Activity: 62

Merit: 0

I dont want to use menu bcos I want sheet to place buy/sell command auto based on other cell value. With menu I can only do it manually.
So I have an idea to do that,
When the command place a BUY/SELL order succesfully, we will assign amount, date, total, rate to variables, let say a,b,c,d. Then we set other condition to write those variables back to the sheet "output". It means we dont setValue for "output" directly from other cell, the script did it. How do you think?
**Try the last time to help me on those probs to know how far we can do. And if we cant do any thing more, just tell me I will try other way to use this code. Anyway, I had a quite good result. THanks

HCP

legendary

Activity: 2086

Merit: 4363

Seems the options are:

Quote from: https://developers.google.com/apps-script/guides/sheets/functions?hl=fr-FR#return_values

- If a custom function returns a two-dimensional array of values, the values overflow into adjacent cells as long as those cells are empty. If this would cause the array to overwrite existing cell contents, the custom function will throw an error instead. For an example, see the section on optimizing custom functions.
- A custom function cannot affect cells other than those it returns a value to. In other words, a custom function cannot edit arbitrary cells, only the cells it is called from and their adjacent cells. To edit arbitrary cells, use a custom menu to run a function instead.

So it looks like you'd need to return a two-dimensional array of values which would "overflow" from the cell with the function call in it and populate the neighbouring cells... which is a bit clumsy and messy.

or

you'd need to create a custom menu: https://developers.google.com/apps-script/guides/menus

I think the custom menu approach is probably the better way to go. So you'd set the values ("buy", "currencyPair", "rate" etc) in a couple of cells, then select "Execute Buy" or whatever from the menu and the function would pick up the values from the cells, make the API and then edit the cells on the output sheet as required.

This little test code creates a custom menu (you may need to adjust your permissions when you run it)... it has two options, the first "Test Output" simply simulates getting the a JSON response string from a buy order... it parses it and outputs it all onto the "output" sheet.

The 2nd option in the menu is just from the example and pops up a dialog.

Code:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Custom Menu')
      .addItem('Test Output', 'menuItem1')
      .addSeparator()
      .addSubMenu(ui.createMenu('Sub-menu')
          .addItem('Second item', 'menuItem2'))
      .addToUi();
}

function menuItem1() {
  test()
}

function menuItem2() {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
     .alert('You clicked the second menu item!');
}

function test() {
  var dataAll = JSON.parse("{\"orderNumber\":31226040,\"resultingTrades\":[{\"amount\":\"338.8732\",\"date\":\"2014-10-18 23:03:21\",\"rate\":\"0.00000173\",\"total\":\"0.00058625\",\"tradeID\":\"16164\",\"type\":\"buy\"},{\"amount\":\"111.8732\",\"date\":\"2014-10-18 23:05:21\",\"rate\":\"0.00000175\",\"total\":\"0.00999\",\"tradeID\":\"16175\",\"type\":\"buy\"}]}");
  Logger.log(dataAll);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("output");
  
  sheet.getRange("B1").setValue(dataAll["orderNumber"]);
  var columnNum = 2
  for (i = 0; i < dataAll["resultingTrades"].length; i++) {
    sheet.getRange(2,columnNum).setValue(dataAll["resultingTrades"][i]["tradeID"]);
    sheet.getRange(3,columnNum).setValue(dataAll["resultingTrades"][i]["type"]);
    sheet.getRange(4,columnNum).setValue(dataAll["resultingTrades"][i]["date"]);
    sheet.getRange(5,columnNum).setValue(dataAll["resultingTrades"][i]["rate"]);
    sheet.getRange(6,columnNum).setValue(dataAll["resultingTrades"][i]["amount"]);
    sheet.getRange(7,columnNum).setValue(dataAll["resultingTrades"][i]["total"]);
    columnNum++
  }
}

manhdn

newbie

Activity: 62

Merit: 0

When I try to write trade data to sheet "output", it says: You dont have permission to setValue

And I found this: https://stackoverflow.com/questions/15933019/google-script-setvalue-permission

They said we can not setValue for a cell from a function placed in other cell.

Can we have another way for that?

**When I try to cancel an existed order, it said "Invalid command". I dont knw why?

HCP

legendary

Activity: 2086

Merit: 4363

Quote from: manhdn on July 17, 2017, 09:32:06 PM

Wowwww it really works, thank you a lot!!!!!
But It only returns order number. Can we fix it to show other details? It will be very useful if it can show amount that was accept, rate, total and date, one by one in a single cell.
I have done the same with SELL,
**Also, what about CANCEL an order with an order number?

To output each record to individual cells... you'd need to either set each cell value in the script or return the WHOLE output as a string and setup cells to parse the output string...

I would probably do it in the script... as there is a chance that the order may be fulfullied over multiple trades:

Code:

  var dataAll = JSON.parse(response.getContentText());

  if (command === "buy") {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("output");
  
    sheet.getRange("B1").setValue(dataAll["orderNumber"]);
    var columnNum = 2
    for (i = 0; i < dataAll["resultingTrades"].length; i++) {
      sheet.getRange(2,columnNum).setValue(dataAll["resultingTrades"][i]["tradeID"]);
      sheet.getRange(3,columnNum).setValue(dataAll["resultingTrades"][i]["type"]);
      sheet.getRange(4,columnNum).setValue(dataAll["resultingTrades"][i]["date"]);
      sheet.getRange(5,columnNum).setValue(dataAll["resultingTrades"][i]["rate"]);
      sheet.getRange(6,columnNum).setValue(dataAll["resultingTrades"][i]["amount"]);
      sheet.getRange(7,columnNum).setValue(dataAll["resultingTrades"][i]["total"]);
      columnNum++
    }
  }

This code assumes there is a sheet called "output"... it outputs, starting at Column B so you can put in the appropriate labels like this:

It will create as many columns worth of Trade Data as returned by the API... so if there are 3 resulting trades, you get 3 columns of data

Quote from: manhdn on July 17, 2017, 09:32:06 PM

**Also, what about CANCEL an order with an order number?

The API states that you just pass "orderNumber" for a CANCEL... so I guess just add in a "cancel" section when creating the payload:

Code:

 if (command === "buy") {
    var payload = {
      "nonce": nonce,
      "command": arguments[0],
      "currencyPair": arguments[1],
      "rate": arguments[2],
      "amount": arguments[3]
    } 
  } else if (command === "cancel") {
    var payload = {
      "nonce": nonce,
      "command": arguments[0],
      "orderNumber": arguments[1]
    }
  } else {
    var payload = {
      "nonce": nonce,
      "command": command
    }

call would be =poloniex("cancel","1234586")

The API says that CANCEL only returns:

Quote

{"success":1}

if it works... so I guess you'd just test if (dataAll["success"] == 1) and output whatever message you want for success or failure...

Code:

 if (dataAll["error"] != undefined) {
    //oh oh, something went wrong!
    Logger.log("Error: " + dataAll["error"]);
    return dataAll["error"];
  }
  if (parameter === undefined) { 
    Logger.log(JSON.stringify(dataAll)); 
    return JSON.stringify(dataAll);
  } else if (command === "buy") {
    return dataAll["orderNumber"];
  } else if (command === "cancel") {
    if (dataAll["success"] == 1) {
      return "Order: " + arguments[1] + " successfully cancelled";
    } else {
      return "Failed to cancel order: " + arguments[1];
    }
  } else if(parameter != undefined && subparam === undefined) { 
    return dataAll[parameter];
  } else if (parameter != undefined && subparam != undefined) { 
    return dataAll[parameter][subparam];
  }

manhdn

newbie

Activity: 62

Merit: 0

Quote from: HCP on July 17, 2017, 09:16:18 PM

Quote from: manhdn on July 17, 2017, 06:32:24 PM

I have made a try with your code and it seems to work bcos there is no "Too long header" error. But when I place a buy order it said: TypeError: Cannot read property "0.01" from undefined. (line 77). (Line 77: return dataAll[parameter][subparam] }; They said the Rate is not defined.

That error is just because it is attempting to parse the return JSON from Poloniex incorrectly...

Quote

if (parameter === undefined) {
   Logger.log(JSON.stringify(dataAll))
   return JSON.stringify(dataAll) }
  else if(parameter != undefined && subparam === undefined) {
   return dataAll[parameter] }
  else if (parameter != undefined && subparam != undefined) {
   return dataAll[parameter][subparam] }
  }

This code is assuming that the "parameter" and/or "subparam" that you pass into the poloniex() function is one of the keys that comes back in the JSON return string from Poloniex... however, the example output that the API docs give for a "buy" command is:

Quote from: https://poloniex.com/support/api/

{"orderNumber":31226040,"resultingTrades":[{"amount":"338.8732","date":"2014-10-18 23:03:21","rate":"0.00000173","total":"0.00058625","tradeID":"16164","type":"buy"}]}

So the "keys" are "orderNumber" and "resultingTrades"... resultingTrades has subkeys of "amount","date","rate","total","tradeID" and "type".

What has likely happened is that your BUY order has probably been placed, but your script just failed to read the result from Poloniex... as it was trying to find a key called ["BTC_ZEC"] with subkey ["0.01"] which won't exist. Check your poloniex order history and see if it got placed.

To fix the error, you'd need to put in some code that returns something that SHOULD be returned by a successful API "buy" call... Also, you should probably put some error checking in that checks to see if the API response was an error code:

Code:

  if (dataAll["error"] != undefined) {
    //oh oh, something went wrong!
    Logger.log("Error: " + dataAll["error"]);
    return dataAll["error"];
  }
  if (parameter === undefined) { 
    Logger.log(JSON.stringify(dataAll)); 
    return JSON.stringify(dataAll);
  } else if (command === "buy") {
    return dataAll["orderNumber"];
  } else if(parameter != undefined && subparam === undefined) { 
    return dataAll[parameter];
  } else if (parameter != undefined && subparam != undefined) { 
    return dataAll[parameter][subparam];
  }

I'm not sure what data from the response you actually want to display, so I just picked the "orderNumber"...

Quote

And I did not see you add more one params in the poloniex function (still has 3 params but we are sending 4?)

Instead of using the "named" parameters (command,parameter,subparam) listed in the function definition... I just used arguments[0], arguments[1], arguments[2], arguments[3]... and check that the "command" is "buy", so therefore there should be 4 "arguments" (0,1,2 and 3) passed in to the function.

Wowwww it really works, thank you a lot!!!!!
But It only returns order number. Can we fix it to show other details? It will be very useful if it can show amount that was accept, rate, total and date, one by one in a single cell.
I have done the same with SELL,
**Also, what about CANCEL an order with an order number?

HCP

legendary

Activity: 2086

Merit: 4363

Quote from: manhdn on July 17, 2017, 06:32:24 PM

I have made a try with your code and it seems to work bcos there is no "Too long header" error. But when I place a buy order it said: TypeError: Cannot read property "0.01" from undefined. (line 77). (Line 77: return dataAll[parameter][subparam] }; They said the Rate is not defined.

That error is just because it is attempting to parse the return JSON from Poloniex incorrectly...

Quote

if (parameter === undefined) {
Logger.log(JSON.stringify(dataAll))
return JSON.stringify(dataAll) }
else if(parameter != undefined && subparam === undefined) {
return dataAll[parameter] }
else if (parameter != undefined && subparam != undefined) {
return dataAll[parameter][subparam] }
}

This code is assuming that the "parameter" and/or "subparam" that you pass into the poloniex() function is one of the keys that comes back in the JSON return string from Poloniex... however, the example output that the API docs give for a "buy" command is:

Quote from: https://poloniex.com/support/api/

{"orderNumber":31226040,"resultingTrades":[{"amount":"338.8732","date":"2014-10-18 23:03:21","rate":"0.00000173","total":"0.00058625","tradeID":"16164","type":"buy"}]}

So the "keys" are "orderNumber" and "resultingTrades"... resultingTrades has subkeys of "amount","date","rate","total","tradeID" and "type".

What has likely happened is that your BUY order has probably been placed, but your script just failed to read the result from Poloniex... as it was trying to find a key called ["BTC_ZEC"] with subkey ["0.01"] which won't exist. Check your poloniex order history and see if it got placed.

To fix the error, you'd need to put in some code that returns something that SHOULD be returned by a successful API "buy" call... Also, you should probably put some error checking in that checks to see if the API response was an error code:

Code:

  if (dataAll["error"] != undefined) {
    //oh oh, something went wrong!
    Logger.log("Error: " + dataAll["error"]);
    return dataAll["error"];
  }
  if (parameter === undefined) { 
    Logger.log(JSON.stringify(dataAll)); 
    return JSON.stringify(dataAll);
  } else if (command === "buy") {
    return dataAll["orderNumber"];
  } else if(parameter != undefined && subparam === undefined) { 
    return dataAll[parameter];
  } else if (parameter != undefined && subparam != undefined) { 
    return dataAll[parameter][subparam];
  }

I'm not sure what data from the response you actually want to display, so I just picked the "orderNumber"...

Quote

And I did not see you add more one params in the poloniex function (still has 3 params but we are sending 4?)

Instead of using the "named" parameters (command,parameter,subparam) listed in the function definition... I just used arguments[0], arguments[1], arguments[2], arguments[3]... and check that the "command" is "buy", so therefore there should be 4 "arguments" (0,1,2 and 3) passed in to the function.

manhdn

newbie

Activity: 62

Merit: 0

I have made a try with your code and it seems to work bcos there is no "Too long header" error. But when I place a buy order it said: TypeError: Cannot read property "0.01" from undefined. (line 77). (Line 77: return dataAll[parameter][subparam] }; They said the Rate is not defined.
And I did not see you add more one params in the poloniex function (still has 3 params but we are sending 4?)
**For the prob with sheet recalculation you pointed out, I will use the function with some conditional function in the sheet so I think I can control.

HCP

legendary

Activity: 2086

Merit: 4363

I've had a quick play with Google App Script, to be honest, I'm not terribly familiar with it... but it seems to be Javascript based.

A couple of things I've found wrong with your script...

Code:

  for (i = 0; i < signature.length; i++) {
    var byte = signature;
    if (byte < 0)
      byte += 256;
    var byteStr = byte.toString(16);
    if (byteStr.length == 1) byteStr = '0'+byteStr;
    stringSignature += byteStr;
  }

This code isn't getting each character of the signature and converting to a hex string, as seems to be the intent... instead it is getting the ENTIRE signature array each time through the loop... so, I was getting "Header too long" errors... You need to change the 2nd line in the loop to:

Quote

var byte = signature[i];

So that it gets each character one at a time.

Secondly, as I expected, it isn't setting the parameters... so the payload ends up being "nonce=bigLongNumber&command=buy"... but it doesn't include the currencyPair, rate or amount.

So instead of this:

Quote

var payload = {
   "nonce": nonce,
   "command": command
   }

Try something like this:

Code:

  if (command === "buy") {
    var payload = {
      "nonce": nonce,
      "command": arguments[0],
      "currencyPair": arguments[1],
      "rate": arguments[2],
      "amount": arguments[3]
    } 
  } else {
    var payload = {
      "nonce": nonce,
      "command": command
    }
  }

NOTE: this is expecting the call to poloniex() function for a "buy" to be: =poloniex("buy","currencyPair",rate,amount)

for example: =poloniex("buy","BTC_ZEC",0.01,2)

Finally, I suspect that attempting to use a Google Spreadsheet may not be the best idea... it is quite possible that whenever the sheet is recalculated, that any cell you have with =poloniex() will be automatically rerun... and you could end up placing multiple buy orders unintentionally! Shocked

(or multiple sell orders if you implement that command).

manhdn

newbie

Activity: 62

Merit: 0

Quote from: HCP on July 16, 2017, 01:13:46 AM

It's hard to see where the appropriate values are being set... ie. "currencyPair", "rate", and "amount". Have you tried debugging the value of payloadEncoded? As this would appear to be where the values for the "currencyPair", "rate" and "amount" values are being set (I assume from cells in the spreadsheet? Huh

):

Code:

var payloadEncoded = Object.keys(payload).map(function(param) {
            return encodeURIComponent(param) + '=' +      encodeURIComponent(payload[param]);
          }).join('&');

Can you dump the value of payloadEncoded to the console and make sure that your parameters are actually being included properly...

Also, your "poloniex" function only takes 3 parameters... but you're sending 4? Huh

function poloniex(command,parameter,subparam)
vs
=poloniex("buy","BTC_ZEC",0.01,2)

Thank you for your advice. But honestly I am not really good at script, so can you help me to get this run? I would love to pay you fee for that.

HCP

legendary

Activity: 2086

Merit: 4363

It's hard to see where the appropriate values are being set... ie. "currencyPair", "rate", and "amount". Have you tried debugging the value of payloadEncoded? As this would appear to be where the values for the "currencyPair", "rate" and "amount" values are being set (I assume from cells in the spreadsheet? Huh

):

Code:

var payloadEncoded = Object.keys(payload).map(function(param) {
            return encodeURIComponent(param) + '=' +      encodeURIComponent(payload[param]);
          }).join('&');

Can you dump the value of payloadEncoded to the console and make sure that your parameters are actually being included properly...

Also, your "poloniex" function only takes 3 parameters... but you're sending 4? Huh

function poloniex(command,parameter,subparam)
vs
=poloniex("buy","BTC_ZEC",0.01,2)

manhdn

newbie

Activity: 62

Merit: 0

Maybe I have made wrong post. Pls help me to delete it if I did

manhdn

newbie

Activity: 62

Merit: 0

I used Google Apps Script and Spreadsheet to build a trading tool on Poloniex via its API. Everything seems to run well but the buy/sell command. Whenever I place an order by placing function =poloniex("buy","BTC_ZEC",0.01,2) at a cell in the sheet the API returns

Quote

Request failed for https://poloniex.com/tradingApi returned code 422.
Truncated server response: {"error":"Total must be at least 0.0001."}

All other command like get balances, deposit address...work well.
Please give me some suggestion to address this issue. Thanks a lot!
P/S: When I check the log, it said: error: Invalid command.
My code:

Quote

// work in progress

   // you need a poloniex API key and secret with trading option enabled
   // you can test it with:
   // = poloniex ("returnBalances","BTC")
   // or
   // = poloniex ("returnBalances")

   function poloniex(command,parameter,subparam) {

   // I assume that all the keys are in the "keys" spreadsheet. The key is in cell A1 and the secret in cell A2

   var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("keys");

   var key = sheet.getRange("A1").getValue();

   var secret = sheet.getRange("A2").getValue();

   var nonce = 1495932972127042 + new Date().getTime();

   var payload = {
   "nonce": nonce,
   "command": command
   }

   var payloadEncoded = Object.keys(payload).map(function(param) {
   return encodeURIComponent(param) + '=' + encodeURIComponent(payload[param]);
   }).join('&');

   var uri = "https://poloniex.com/tradingApi";

   var signature = Utilities.computeHmacSignature(Utilities.MacAlgorithm.HMAC_SHA_512, payloadEncoded, secret);

   var stringSignature = "";

   for (i = 0; i < signature.length; i++) {
   var byte = signature;
   if (byte < 0)
   byte += 256;
   var byteStr = byte.toString(16);
   if (byteStr.length == 1) byteStr = '0'+byteStr;
   stringSignature += byteStr;
   }

   var headers = {
   "key": key,
   "sign": stringSignature
   }

   var params = {
   "method": "post",
   "headers": headers,
   "payload": payloadEncoded
   }

   var response = UrlFetchApp.fetch(uri, params);

   var dataAll = JSON.parse(response.getContentText());

   if (parameter === undefined) {
   Logger.log(JSON.stringify(dataAll))
   return JSON.stringify(dataAll) }
   else if(parameter != undefined && subparam === undefined) {
   return dataAll[parameter] }
   else if (parameter != undefined && subparam != undefined) {
   return dataAll[parameter][subparam] }
   }

Topic: Need help (and ready to pay): Building Poloniex trading tool (Read 890 times)