Creating Your Own Spreadsheets and Charts Using Poloniex API – Part 5

If you have reached this page without going through the previous instructions, please Click Here to start at Part 1.

When you reach this part of the instructions, you should have your Ask & Bid Order data pulled from Poloniex and populated in a sheet called APIPull in your spreadsheet.  You will have the asksRate in column A, the askAmount in B, the bidRate in C, and the bidAmount in D.

We are at the point where we are going to switch over to the Spreadsheet and do some work there.  We have done the tough part and you might already be familiar enough with spreadsheets to be able to do things on your own at this point.  Later I will switch back to the Script and add some more features such as creating a table (array) for a Chart and sending Email notifications.  You may want to follow along below to see where I am putting the calculations so your cells match mine for future scripting lessons.



Google Sheets sometimes acts up and will not show the totals for calculations.  If this is happening to you close your sheet and reopen it.  The calculations should work then.  If that still does not help, open a new sheet and simply paste it’s identifier into your script.


Our next step is to calculate the total Asks and the total Bids.  If the instruction here does not make sense, look at the example picture below to see what it will look like.


Populate the calculation into the F2 and H2.  Then Copy and Paste them down to row 19999.  The easy way to copy all the way to the bottom is to:

  1. Copy F2 (Ctrl-C)
  2. Go down to cell F3 and press Shift-Ctrl-Down Arrow.  This will highlight all of the rows in Column F all the way to the bottom.
  3. Then Paste (Ctrl-V) will put your calculation from F2 into all the other cells in column F.

Do the same with H2, all the way down to H19999.  Your spreadsheet will look like this:


Cell F3 has ‘=SUM(A3 * B3)’ after the Paste operation.  The same pattern follows all the way to the bottom of the column for F and H.

Now we simply want to add all the values in columns F & H.  Put these totals in G2 and I2 like shown below.  This will give us the total Ask & Order books for Poloniex at the moment.  Here is how we add the columns.



You can compare the total you calculated in cell I2 (2142.957434 in my example above) to what is showing on Poloniex in the Buy Orders (2039.77424157 below).  The amounts should be nearly identical, except the number on your spreadsheet is a snapshot in time and the number on Poloniex is constantly updating so there is probably a small difference.


You might notice the Sell Order Total is much higher on Poloniex than the amount in your spreadsheet in cell G2 (the ‘asks’ total).  On the Polo site they list the Sell Total in the quantity of the currency (XMR in my case), and our spreadsheet has the amount in Bitcoin.  You would have to pull or calculate the BTC_XMR exchange rate and multiply it by your total to show the same number Poloniex shows.

Click Here to continue to Part Six.

Please recognize a lot of effort went into figuring this process out and documenting it to save you time, with the goal of giving you information you can use to make more BTC in the future.  If this is useful information for you, I would appreciate any tips you are able to send to the following BTC address.