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

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


We technically have all the data we need to create a chart, but let’s add one more piece of information.  We can add a line to the chart showing the relation of the current Bid Price to the Bid Order Book total amount we already have in column B on the TableData sheet.  This will visually show us if there is a correlation between how many open bid orders there are and the price.  Does price increase when bid orders increase?  We will soon know.

We already have the current Bid Price in cell C2 of the APIPull sheet.  It is the highest price someone is offering the last time the data was pulled from Poloniex.  The problem we have to overcome is we can’t put a number up around 2000 (the total bid orders) on the same chart with a price near .008.  The lower number will just appear as a straight line way at the bottom of the chart.  To make it relevant, we need to multiply the price by some number to get it up in the same range as the other value.  This will not accurately show the price as .008 on the chart, but it will visually show the relationship between the two values.

We just need to get our price value up in the general area of the bid order total.  I usually see XMR at around the 2000 level for the bid order total, so I need to multiply the price of around .008 to get it near that level.  For simplicity I am going to multiply by 200,000, which will get it close enough to chart.  I will ultimately put this in column C on the TableData sheet, but first I want to put it in H1, next to the values we have in F1 and G1.  I do this because we need to also Append this field to the end of the data our chart is based on.  In H1 on my spreadsheet in the TabelData tab I am putting in this calculation:


=SUM(APIPull!C2 * 200000)


Then I have to go back to my script and add H1 to the range I append.  Search for this code in your script:


var rangeData = TableData.getRange(“F1:G1”);


Then simply change it to go from F1 to H1 and Save.


var rangeData = TableData.getRange(“F1:H1”);


This is all you need to do.  The next time your data updates it will also update the latest Bid Price (times 200000) value in the ‘C’ column.  You will probably want to delete all the rows, starting with row 2, populated in your spreadsheet up to this point because they will be missing column C.


Now we are ready to create a chart.  Let’s do this in another sheet (tab) within our spreadsheet.  Go ahead and create a new tab, like you did in Part 6 a little while ago, and name it ‘Chart.’

In the new sheet go to Insert, then choose Chart.



In the Chart Editor screen that appears, enter the location of the data you want to put into the chart.  The information is on the TableData sheet, starting in cell A2, and going down 144 rows to column C145.  If you want to use the Heading names you put in row 1, you can include row 1 as well.  This is specified in the top left field as:



or, to include headings use:



Then choose the Line Chart you like, and press Insert.


Depending on how many rows you have in your TableData sheet, you will see a chart pop up on your new tab. Right Click your chart and mess around with the settings to make it appear like you want it to.  After 24 hours, my chart ended up looking like the image below, but you can configure it however you want.


On the same sheet as your chart, experiment with including additional data.  For example, when looking at your chart you may want to see the current time.  Add =Now() to a cell to display this.  Or, you may want to see the Current Bid and Ask Price.  You can pull those in from your APIPull sheet into a cell here using a command such as:




Let’s go ahead and display a Min, Max, and Average number for the Order Bids Value somewhere.  Choose three open cells in your Chart sheet and enter these calculations in those cells:






Let’s also show our current Bid Order Total.  Choose any open cell and pull the value from your TableData sheet, cell G1.




Whatever you think will be helpful, you can add it here to your sheet.  At the very end we are going to publish your new chart and all of these additional fields to the Web where others can see it, but they will not be able to see all the data used to create the chart in the background.  Be sure whoever looks at your chart has all the data they need to make sense of it.


Click Here to see what my Script looks like at this point.

Click Here to continue to Part Nine.

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.