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

 

Advertisements