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

bitcoinbanner

Join Coinbase & Get $10 Free with your 1st Deposit

 

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

 

I am thinking about adding some more information here, but I don’t have it ready yet.

 

 

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

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

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.

The final cool feature I want to show you is how to send yourself an Email when a certain level or target indicator is reached.  You may find when the Bid Order Total goes up to its high for the day, you want to know what the price is to see if it has reacted to the move.  This coding is done in the script, so let’s head back there.

First we need a value to Trigger our Email.  For this example, whenever the Max Bid Order Total is set, we will send it.  In real life, this will drive you nuts because you will receive too many Emails, but you can customize it after we code it to do what you want.

First, we want to name our new sheet ‘ChartSheet’ in the script.  Next, in the last step of Part 8 we set up a cell in our Chart sheet with the Maximum Bid Order Total from column B of our TableData sheet, and another cell with the current Bid Order Total.  Let’s grab those fields to compare in the next step.  Your cell values will probably be different than H4 & H6 depending on where you put the information on your Chart sheet.

*

var ChartSheet = ss.getSheetByName(“Chart”);
var maxBid = ChartSheet.getRange(“H4”).getDisplayValue();
var currentBid = ChartSheet.getRange(“H6”).getDisplayValue();

*

We need an ‘if’ statement here to compare the two values.  If they are equal, then we have set a new Max value and need to send an Email.  You can include any information you want in the message.  You just have to calculate it and pull it in first.  You may also want to include the URL for your chart so you can easily click to see your chart whenever the Email arrives.  The code for this looks like:

*

if (currentBid == maxBid) {
var subject = “XMR Buy Order New Maximum Value”;
var message = “Current: ” + currentBid + ” *** https://docs.google.com/spreadsheets/d/1usaMtuJ6eq0DbPaHZ7BDspgoWMcr0akEzaDZfKZHL/edit#gid=510852470″;
MailApp.sendEmail(“author@GMAIL.com”, subject, message);
}

*

The MailApp.sendEmail command is built into Google scripts.  You don’t need to add anything to your code to use it.  There are also some other things you can do with it, so I recommend looking it up to see what kind of parameters you can also utilize.

The last thing I want to show you is how to publish your new chart to the web.  Go to your chart and under File, choose Publish to the web.

part9image1

This will pull up a window where you can specify just to publish your Chart sheet (see below).  You can also make your Chart sheet available in other formats besides a web page, such as PDF.  Once you click Publish you will receive a URL where anyone can view the latest version of your Chart sheet.  Your published chart should also update every ten minutes when your script runs, but this seems to work sometimes, and not at others.

part9image2

 

I have learned a lot researching and creating this document.  Before I am done I will have spent over a month, and close to 200 hours, from the time I started looking into this until I get it published for everyone to use.  I was frustrated there was nobody who had already created a document with this information, but most people do not want to share information if they perceive it gives them an advantage over everyone else.

My final script is in the link below.  I would appreciate it if you were able to make a small donation to

Bitcoin address: 1EPyFDbRtfxMvpAhCyXBH2xwMuJ77Wwb41

or

ETH address: 0x2F4203691f48a95D96A261f3453f39fdf490c386

if you found this helpful.  Also, don’t forget, there is a cheat sheet I have available which shows how to pull every Public Poloniex API to anyone sending a donation.  Just send me a message using the Contact Form letting me know your Email address and when you sent the BTC or ETH.  I will reply with the document as soon as I see your message.

 

Here is the final version of my Script.

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

 

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.

part8image1

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.

part8image2

 

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:

*

TableData!A2:C145

or, to include headings use:

TableData!A1:C145

*

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

part8image3

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.

part8image4

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:

*

=APIPull!C2

*

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:

*

=Average(TableData!B2:B19999)

=Min(TableData!B2:B19999)

=Max(TableData!B2:B19999)

*

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

*

=TableData!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.

1EPyFDbRtfxMvpAhCyXBH2xwMuJ77Wwb41

 

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

 

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

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

 

Hopefully you have some experience with charts, even if it is just what you remember from high school.  Let’s look at a typical financial chart and see what is being measured.  We will want to do something similar for our chart.  Here is a chart for XMR Price, with calculations every 5 minutes, over a 24 hour period.

part6image1

There are three pieces of data you need to know to create and read this chart.

  1. Price (fluctuates)
  2. Time (5 minute intervals in the table above)
  3. Total length of time represented (24 hours)

Only one of these values, 24 hours, will actually be static.  The time and price will be different every five minutes.  We need to capture these different values for time and price in a table.  We only need to keep 24 hours of data in the table.  Once we have these values, Google Sheets has a simple tool to create the chart using them.

In Part 5 we took a snapshot of the current Poloniex Buy Order Total when we hit the Run button in our script.  If you look at your spreadsheet in cell I2 you should see the total you calculated for the currency you are using.  If you hit the Run button again you will get an updated snapshot of the current total.

We need to create a new Sheet (tab) to move this total to in our spreadsheet.  To do this click the + button in the bottom left corner.

part6image2

 

This will create a new tab named Sheet2.  Double click the tab name to rename it.  I will call mine TableData.  You can call it whatever you want, but we will be referring to it in the script in the near future, and I will be using TableData as the Sheet name there.

part6image3

 

The first piece of data I am going to populate in this sheet is the Buy Order Total from the APIPull sheet.  I am just going to recommend you put this value in G1.  It will make sense in a few minutes why we choose this cell.  In G1 enter the following to pull in cell I2 from the APIPull sheet.

*

=APIPull!I2

*

Your I2 value from APIPull should pop up in G1 after you hit enter.

The next piece of data we need is the Date and Time, which I will refer to as a Timestamp.  Google Sheets is not great about transferring the date fields it generates from one cell to another.  The easiest way to overcome this is to change the timestamp to a text field for our table.  This value needs to update whenever the sheet changes.

To do this we want to use the Now() command in Google Sheets to generate the current time.  Whenever the sheet changes in any way, this Now() value will update with the current time, but it will be treated as a Date field by Google Sheets, which is different than a text or numeric field.  Let’s populate this Now() value in J1 using…

*

=Now()

*

At the time I am writing this, the timestamp generated for me is 11/4/2016 15:03:04.  I can’t use that value as text in a table though, because one time every year it will cause a big problem.  If I get 12/31/2016 at 11:59 as a timestamp, in 5 minutes I am going to get 1/1/2017 at 12:04, and this is going to cause my times to be out of sequence in my table.  Since we are using a text field, 12/31/2016 is larger than 1/1/2017 alphabetically because the Month is listed first, but we need the latest date to be the largest.  To overcome this I am going to do a little switching around when I convert the Timestamp to text.  Use this command in F1.

*

=text(J1,“YYYY/MM/DD_HH:MM:SS”)

*

part6image4

 

Notice this command moved the 4 digit year first.  Now, alphabetically, 2017/01/01 at 12:04 is larger than 2016/12/31 at 11:59, and our Bid Order Total values will all be in the correct sequence.

Don’t worry too much if that did not make sense.  Just copy it in and know it will work on New Year’s Eve.

Take a look at the values in columns F & G now.  Go ahead and run your script again and watch as the Date and Total updates.  If only you could capture 24 hours of these values, 10 minutes at a time, and somehow populate the data into a chart…

 

Click Here to continue to Part Seven.

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

 

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.

*********************************************

IMPORTANT NOTE – DON’T MISS THIS

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.

part5image1

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:

Part5Image2.png

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.

part5image3

part5image4

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.

part5image5

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.

1EPyFDbRtfxMvpAhCyXBH2xwMuJ77Wwb41

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

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.

At this point we have received the data from Poloniex, and created a large field called parcedData.  It contains all of the data in one huge field and needs to be broken down into individual records (rows) and fields (cells) to be populated in the spreadsheet.  I am going to simply give you the command to do this, but I can’t leave you without understanding what is being done.  You will need to know in case you are planning to use the other APIs.

Here is the ‘asks’ command we will use in our script.  This will take just the two fields passed in the ‘asks’ section of the JSON file, and define the first field as ‘askRate’ and the second field as ‘askAmount.’  These headers are put in the first two fields of the ‘asks’ array with the push command.

*

// Define the ‘asks’ array, and populate it from the parced data pulled
var asks = [];
asks.push([‘askRate’, ‘askAmount’]);

for(var key in parcedData.asks)
{
asks.push(parcedData.asks[key]);
}

*

Take a close look at the code above and make sure you understand it.  The section “parcedData.asks” is actually saying “only look at the data in the asks key within the field parcedData.”  The for command says to loop, and the field key simply accumulates… 0, 1, 2, 3, etc.  So, you end up looping through each record in the asks data, pushing each out to an array we have called asks.

Next we will put the data into the spreadsheet.  In the first line we tell it where to put the columns and how many to put out there.  The portion in the next lines of code saying (1, 1, asks.length, 2) is defined as:

  1. Start Row (1)
  2. Start Column (1 – Column A)
  3. Number of Rows (asks.length – however many rows were in the ‘asks’ section)
  4. Number of Columns (2 – A & B)

You do not have to know the value of asks.length.  The script already knows how many rows there are.  The second line says Set Values, or more simply stated: ‘write it’ to the range we defined (A & B).

*

// Put the ‘ask’ data into the spreadsheet columns A & B
askRange = APIPullSheet.getRange(1, 1, asks.length, 2);
askRange.setValues(asks);

*

At this point you can finally run your script and see the ‘asks’ data populate in the spreadsheet columns A & B, just like you told it to.  To run your script, save it first, then press the ‘play’ arrow.

part4image1

 

Let’s go ahead and do the same things for the ‘bids.’  Give it a try yourself by copying the ‘asks’ code above, pasting it next in your Script, and modifying it to work for ‘bids.’  We want to write to columns C & D, so you will need to set the Start Column to 3 in your bidRange.  If you run your code and don’t get the result you are looking for, compare it to the code below and see where the difference is.

*

// Get the ‘bids’ data into the spreadsheet columns C & D
var bids = [];
bids.push([‘bidRate’, ‘bidAmount’]);

for(var key in parcedData.bids)
{
bids.push(parcedData.bids[key]);
}

bidRange = APIPullSheet.getRange(1, 3, bids.length, 2);
bidRange.setValues(bids);

*

It is probably a good idea to ‘Make a copy’ (under the File menu) here.

The reason I wanted to devote an entire lesson to this one piece is because different JSON files sent from Poloniex are formatted differently.  For example, if you ask for ‘ALL’ currency pairs, like in the command below, the returned JSON file will be formatted differently.  The first field in each section will define the currency pair (example “BTC_1CR”).  After this, all of the ‘asks’ for BTC_1CR will be listed, followed by all of the ‘bids.’  Then the JSON file will specify the next currency pair (“BTC_AMP”), and its data, etc, etc, etc.

https://poloniex.com/public?command=returnOrderBook&currencyPair=ALL

 

There are many different APIs at Poloniex, and each is different.  The code to handle pulling each one and putting it into a table is complicated and would take a thorough understanding of JavaScript.  In order for you to figure it out, you will have to dive deeper into coding than I am going to go into here.

I strongly recommend you learn the JavaScript needed yourself to pull other APIs in.  If you do not, you will not be able to pull JSON API information from other sites, or even the other available Poloniex APIs, when you need it.  There are many sites offering free online classes for JavaScript, such as https://www.codecademy.com.

If you just do not have the time or patience, I have created a Poloniex Public API Cheat Sheet and will provide it to anyone who sends a tip to the Bitcoin address below.  This cheat sheet covers all Public APIs provided by Poloniex.  Be sure to look on the Poloniex API page at the information you will be able to pull before sending any BTC.

part4image2

Send me a message using the Contact Form with your Email you want me to send the Cheat Sheet to, and the time you sent the tip.  I will send the document your way as soon as I see your request.

 

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

Click Here to continue to Part Five.

 

 

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

ETH Address: 0x2F4203691f48a95D96A261f3453f39fdf490c386

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

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 just finished linking the Script with our code in it to the Spreadsheet we want to store the data on.  There are a couple more preliminary steps we need to take before we pull and populate the data into our spreadsheet.  The first of these is to define within the Script the sheet (tab) name within the spreadsheet I want to work with.  As we move forward, we are going to be creating other sheets (tabs) with different names.  By defining each name I can keep the script simple.

Putting the line of code below next I am telling the script any time I refer to APIPullSheet, I want it to work with the tab I have named APIPull in the spreadsheet.

*

var APIPullSheet = ss.getSheetByName(“APIPull”);

*

The second preliminary step is to clear the data currently in the cells we are going to populate in the spreadsheet.  When our script runs, let’s say there are 1000 lines in the Ask columns.  Ten minutes from now the script runs again automatically, but this time there are only 990 lines.  If we don’t clear the cells first, the process will overwrite the first 990 rows with the new data, but will keep the last 10 rows from the previous run.  This can throw off our numbers in a big way.

I put in there to clear columns A through D, from Row 2 to 19,999 to be on the safe side.  We should never have that many rows when pulling a single currency, but you can change that number to be smaller or larger if you want.  Here is the code used to clear the cells.

*

// Clear Columns A, B, C & D
APIPullSheet.getRange(‘A2:D19999’).clearContent();

*

Now we are ready to get the numbers.  We need to define the URL from Poloniex to use to pull data.  In Part 1 I pointed you toward the Poloniex API Documentation.  In there you learned you can pull all kinds of information from the Public APIs.  I am going to be pulling the ‘returnOrderBook’ data, which will give me the Ask (sell) and Bid (buy) information you can see on the Poloniex Exchange for any coin.  I chose this because it is data you can use and the API for it is simple.  If you need a refresher on how the data is pulled and what it looks like, please refer back to Part 1.

As previously discussed, I want to pull all of the data from the Order Book for XMR (Monero) from Poloniex.  The URL I would use to see the data (which I created using the Poloniex API Documentation) is below.  This is also the same URL I can use in my Script to reference the data.  I put a depth of 19999 because Poloniex only returns the top 50 asks and bids if you do not specify.  19999 is sure to return every row available.

https://poloniex.com/public?command=returnOrderBook&currencyPair=BTC_XMR&depth=19999

Defining this URL in the script just requires the line of code below.  This is the simplest code within the script, and we will be defining many variables this way.  It simply says…

  1. var create a variable (AKA a field)
  2. url – call the variable ‘url’
  3. =”http” make the field equal to the Polo API web address

*

var url = “https://poloniex.com/public?command=returnOrderBook&currencyPair=BTC_XMR&depth=19999 “;

*

Next, we have to use a couple commands Google Scripts offers which perform specific tasks.  These functions are:

  1. fetch Pulls data from a URL
  2. parse Decipher that data from JSON into something the program can use

We are simply going to pull data using these commands into new variables we define.  In the first line we create a variable named responseAPI, and put the raw data from the Poloniex URL in that field.  This data is still in JSON format and we cannot really use it in the spreadsheet yet.

*

var responseAPI = UrlFetchApp.fetch(url);

*

And then we take that JSON formatted data, and parse (break) it into fields we can use on a Spreadsheet.  The data is put into a field called parcedData.

*

var parcedData = JSON.parse(responseAPI.getContentText());

*

Once these steps are run, all of the information fetched (pulled) from Poloniex is in text format in the parcedData field.  We can’t really work with the data yet because while Google Scripts knows there are two sets of data at this point, asks & bids, it needs the fields within these broad categories defined.  This is a lengthy topic to discuss, so I will cover it in Part 4 by itself.

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

Click Here to continue to Part Four.

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

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

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

We need to open a new Google Sheet to get started.  To do this, go to your Google Drive and click New.  A Dropdown will appear.  Click Google Sheets and a new spreadsheet will open for you.

part2image1

In the top left of your new Spreadsheet you will see the name is ‘Untitled Spreadsheet’.  Click that Text and give it a new name such as XMRBuyOrders.   Next, go to the bottom of the spreadsheet and find the Tab named Sheet1.  Double click the text and rename it to APIPull.  This is the sheet we are going to put the API data from Poloniex into.  You can name them whatever you choose, and though I do not use spaces in my names out of habit, spaces are fine also.  Every time you make a change in a Google Sheet it is automatically saved.  There is an Undo and Redo button on the top row next to the printer.  I have needed these many times.

Let me remind you here of the first rule of working on any document, spreadsheet or programming on a computer.  Save a copy periodically.  This can be done by clicking File, and choosing Make a Copy from any Google Application you are using.  When trying to clear data about five days into my programming, I accidentally chose the delete column function instead of the clear column function.  This wiped out everything I had been working on in my spreadsheet, and I would have had to start from scratch if not for my saved copy.

Next, we need to do our first piece of programming.  The language used in Google Scripts is very similar to Java, though not all the functions work exactly the same and not all Java options are available in Scripts.  If you want to do some research into how Java programming is done, you can visit W3Schools at the link below.  This is not needed to do what I am going to show you, but it is informative, and may be helpful as you try things on your own and need to use functions I have not covered.  Also, Google Search is your friend when you need to figure out something new.

http://www.w3schools.com/js/default.asp

Open a new Google Apps Script from the New button on your main Google Drive screen.  On the New pull down menu, select More >, then Google Apps Script.  You may see this referred to as GAS when you are searching the Internet for help on Scripts.

part2image2

A new scripts screen will open.  Click ‘Untitled Project’ and change the name to something like XMRScript.

part2image3

We can name our function, or routine, at this time.  In the script you just opened, the function name defaulted to myFunction.  You can have many functions within a single Script, but we are going to use just one to do everything here.  I am changing the name myFunction to processXMRAPI.  Everything within the top and bottom bracket { } following this name is part of the function.

******************************

VERY IMPORTANT – DO NOT MISS THIS

We are about to start writing code into our script.  At the bottom of each Page where any Script is modified is a link like this:

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

Please open that link to copy the code you use into your own script.  For some reason, when you copy from a web page directly into your code, Google Scripts sometimes sees an ‘illegal character’. This is due to the single quote ( ‘ ) not being interpreted correctly on the copy. Usually if you retype each single quote in your script the error will disappear.

******************************

We need to tell the Script where to find the Spreadsheet so the two can interact.  This will be our first line of code.  The link we will use is found in the Web Address of the Spreadsheet.  There is a long 44 character unique identifier consisting of random numbers and letters in the URL address itself.  You need to copy that identifier.  For the Spreadsheet I created I have…

Address:

https://docs.google.com/spreadsheets/d/1eE2qlIszcpIysIdy4QVgPpm0pKixswzYtVXKobP8dUk/edit#gid=0

Identifier:

1eE2qlIszcpIysIdy4QVgPpm0pKixswzYtVXKobP8dUk

Using this identifier, insert the following code in your function to link your Script to your Spreadsheet.  The // before text denotes a comment.  You may want to add to the comments to document what each step in the process does as you go.  This way when you look back later you can easily remember what you were doing.

*

// Link the script with a spreadsheet using the identifier found in the spreadsheet URL
var ss = SpreadsheetApp.openById(‘Paste Your 44 Character Identifier Here’);

*

Your Script will look something like this when you are done…

part2image4

Please click Save at this point and be sure you are not receiving the ‘illegal charater’ error message.  If you are, you can use the code from the ‘see what my script looks like’ link at the bottom of each page.  It will copy and paste the exact same text without an error from Google.  I do not know why.

Now we are able to write code in this script that uses and changes values in the associated spreadsheet.  For example, you might want to pull data from Poloniex and populate the spreadsheet with the information.  We will be doing that in the next lesson.

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

Click Here to continue to Part Three.

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

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

If I do this right, when you are done reading through it you should have an automated process pulling information you want to see in an easy to read chart from the Poloniex Exchange, even if you have never programmed anything before.  The process will also alert you via Email when it meets criteria you specify.  I am providing this information freely and publicly, but it took me weeks to figure it all out, and it took me many hours more to put together a guide simple and comprehensive enough for anyone to use… one that will save you the same weeks worth of work.

For that reason, I am going to request a small voluntary donation if you find this guide easy to understand and useful.  I am new to Crypto and learning as I go.  Figuring out how to pull this information and use it is one way I am personally trying to better understand this market.  I hope it will help you also, and give you the data to make better investment decisions.

I appreciate your contribution.  My Bitcoin Address for any support is:

1EPyFDbRtfxMvpAhCyXBH2xwMuJ77Wwb41

 –

I tend to get wordy, and I am going to go into a lot of detail.  I don’t just want to give you code to copy and paste.  I also want you to understand what you are doing so you can create a table of your own design using any of the available information Poloniex offers in their API feed.

I am going to break this into many pieces and explain each one.  At the end of these lessons you should end up with a chart showing the relationship between the Buy Order Total and the Price of any Altcoin on Poloniex you choose.  I would recommend using one with a lot of action for your first attempt.  For my examples, I am going to use Monero (XMR).  In addition, your finished program is going to send you an Email notification when the Total Buy Orders exceed a value you assign.

Step one is to set up a Google Drive Account if you do not already have one.  If you have a GMAIL account you already have a Drive.   You need this account because you need access to Google Scripts (applications) and Sheets (spreadsheets).  This is mainly because the API information from Poloniex is in JSON format, which Microsoft Excel and Access have trouble deciphering, but Google Scripts handles easily.  Everything I go through in this tutorial will be based on using these Google tools.  If you are opposed to using Google, I don’t blame you, but I do not know how to make this work any other way.  I spent days trying to do the same thing I am about to show you using Excel and Access, and could not get the raw data from the API to pull in correctly.

Google Drive

Step two is to familiarize yourself with the Poloniex API data you can pull, and even manipulate.  I am not going to go into, or even recommend you use, the API to trade with.  Though I am planning to experiment with it, I am not familiar enough with these features to give you accurate information on how it is done, and I don’t want to be responsible for causing you to lose any investments based on my incomplete information.

Poloniex API Documentation

Pay particular attention to the ‘returnOrderBook’ API in the Public API Methods section.  To use these Public APIs you do not need to activate the API Feature within your Poloniex account, or even have an account for that matter.  Anyone can use these.  Even if you only trade on another exchange such as Bittrex, you can still use the Poloniex Public APIs to gather information free of charge.

Try following this link:

https://poloniex.com/public?command=returnOrderBook&currencyPair=BTC_ETH

What you see returned from Poloniex should look like the data pasted below and is a complete list of all the current Buy (bid) orders and Sell (ask) orders for Ethereum in JSON format.  It is relatively easy to understand visually.  In the screen shot I pasted below, notice the first line starts with “asks” and about halfway down I have circled the word “bids.”  Everything after “bids” are the Buy orders, and everything before is the “asks” or Sell orders.

If you look on the Poloniex Exchange at the ETH Buy Orders and Sell Orders, then compare these to the results you see in your own JSON data, you will see the same information in both places.  The first “ask” order is also the lowest Sell price, so when I retrieved the data below, this order was offering 1.1 ETH for 0.01510990 BTC each.  The Highest Buy order (just after “bids”) was offering to purchase 2.65584403 ETH for 0.01510989 BTC each.

part1image1

The numbers on a coin with as much volume as ETH are constantly fluctuating.  You could hit refresh every ten seconds and they would be different.  I don’t want to be overwhelmed with data every ten seconds, and Poloniex will not be happy if I pull too much data, so I have to decide on how often I want to pull the information.  The chart I am going to show you how to create is going to pull data every ten minutes, and keep twenty four hours at any one time.  I will also explain how to change those times to meet the needs for your own charts.

The first challenge we will overcome is pulling the data you see on the screen into a Google Sheet where we can work with it.  We do this using a Google Script.  I will begin to cover this important step in the next lesson.

Click Here to continue to Part Two

About Me:  I have been employed as a programmer for twenty years now, though I do not program in the same language I am going to teach in this lesson.  This programming experience has helped me understand the logic necessary, but the syntax is very different than what I am used to (RPGLE).  On the side, I like to write and document.  If you searched the web for me you would find articles and interviews on everything from Poker to Prepping… from Blockchain to Boolberry.  You might also find some mindless Zombie short stories I wrote a few years ago.  I would be open to doing some side work for you if you like my style and are in need of someone to write for you… Will work for Crypto.