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

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

Reminder: Please remember to copy and paste your script code from the link at the bottom of this page.  If you do not you may get an ‘illegal character’ message when you try to save your script.

We are now at the point where we have a program that will update the current date and time, along with the Order Bid Total on Poloniex whenever it is run.  Next let’s tell our script to run every 10 minutes.  To do this we need to go back to our Script page.  Under Resources, select ‘Current project’s triggers.’  It will tell you ‘No triggers set up.  Click here to add one now.’  Do that.

part7image1

 

Create your timer like I have in the screenshot below for now, then click Save.  You can come back and update it later if you want to run it more or less often.

part7image2

 

As soon as you click ‘Save’. this program is going to run automatically every 10 minutes until you or Google stops it for some reason.  If you have a spare 10 minutes in your life, flip over to your spreadsheet and watch it happen as the values in your TableData sheet get updated.  It is like magic.

We want to store 24 hours of the updated values, one snapshot every 10 minutes, for this exercise.  Then we can use the information to create a chart.  24 hours * 60 minutes = 1440 minutes in a day.  1440 minutes / 10 minutes = 144 snapshots we need to show in our table to display 24 hours of data.

Let’s keep the 144 most recent snapshots in Columns A & B on the TableData sheet.  To do this, we simply put the values each time the script runs after the last row in Columns A & B.  Adding a record to the end of a file is called Appending.  In the next step, we will add the latest data to the table and program the script to only keep the most recent 144 records.

We have not named our newest sheet within our script, so that will be the first line.  The next line will tell it where the data we want to append is located (TableData!F1:G1).  Third, we will tell it to grab the values in F1 & G1 and call them latestData.  In the last line we Append the values to the end of Column A & B on the TableData sheet.

*

// Append Latest Data to End of the File
var tableData = ss.getSheetByName(“TableData”);
var rangeData = tableData.getRange(“F1:G1”);
var latestData = rangeData.getValues(); // Put F1 & G1 in latestData variable
tableData.appendRow(latestData[0]); // Put the data at the bottom of the spreadsheet

*

It is a good time to Save.  Just for fun, Run your script every 30 seconds or so and watch the data in Column A & B stack up.  Also, if you want to put some headings into A1 & B1, like I did in the picture below, you can.  These headings can be used later to define the lines on your chart.

part7image3

If you look at the information in your spreadsheet now you will see the oldest record is at the top in row 2, and new (recent) records are being added to the bottom.  To keep 144 records in the file, first we need to see if there are 144 records, then we need to delete the oldest one from row 2.  The deleteRows command removes the row completely, causing the other rows to move up, so row 3, and all the other rows, will move up one row after 2 is gone.

Remember when we put our totals we moved to the TableData sheet into row 1?  If we had put it in any other row it would be deleted by this code.  Here is the code for deleting the oldest data in row 2.

*

var rowsToKeep = 144; // 144 * 10 Minutes = 24 Hours of Data
var totalRows = tableData.getLastRow();
var numToDelete = totalRows – rowsToKeep;
if (numToDelete > 0)
{
tableData.deleteRows(2, numToDelete);  // Starting at row 2 Delete a number of rows
}

*

Obviously,if you want to keep more or less than 24 hours you can change the number from 144.  If you run your script more or less than at 10 minute intervals, you would need to adjust how many rows you keep in the spreadsheet to reflect the total amount of time you want to see in your chart.  To keep 24 hours running at 5 minute intervals instead of 10, you would need to double 144 to 288.

 

Here we also have our first ‘if’ statement, also known as a conditional statement.  The syntax is very strict and was challenging for me to learn.  The letter i in the word ‘if’ must be lower case.  The ‘if’ statement itself, and the brackets specifying the code within, do not require the semi-colon like most other code.  You will probably be using ‘if’ statements frequently as you continue your coding journey.  The simple example above should start you along the path of learning.

A last note on ‘if’ statements I had to adjust to.  The ‘=’ within an ‘if’ statement is actually defined by using 2 ‘=’ signs.  A ‘not =’ is defined with ‘!=’.  Here are a couple examples to illustrate this.

// If = 0

if (NumToDelete == 0)

 

// if <> 0  (not equal)

if (NumToDelete != 0)

 

Code Academy’s JavaScript training has a really good section on using conditional statements, as well as many other basic coding techniques you will want to know.

https://www.codecademy.com/learn/learn-javascript

 

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

Click Here to continue to Part Eight.

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.

1EPyFDbRtfxMvpAhCyXBH2xwMuJ77Wwb41

 

Advertisements