Profit Tailer JSON Tracker: Automated Google Sheet

If you are running Profit Trailer, you know how hard it is to keep track of everything your bot does. There are a few Spreadsheet templates floating on the net to help, but they require daily manual input, and generally don't give you "the full picture". If you are ready to spend some money, there is a web-based add-on called https://pttracker.net/

After looking at all the available solutions, I decided to create my own. The result: probably the most complicated Google Spreadsheet I have ever done. It was all worth it though.

If you would like to set it up for yourself, follow the steps below and thank me later ;)

•••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••

• Keeping all trading history in your Profit Trailer JSON file

Profit Trailer updates a file within its main folder called "ProfitTrailerData.JSON". This file contains all of your trades history and is where we want to pull data from. The first step is to open your "application.properties" file and change the "trading.logHistory" to enough days so that your JSON file never erases old data.

•••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••

• Copying this JSON file to a web-based folder

We need to copy the "ProfitTrailerData.JSON" automatically every day to a web-based folder so that we can access it from Google Sheets. Thankfully, Windows has built-in functions called Robocopy and Task Scheduler to do just that, and Dropbox offers the web-based folder we need.

If you do not have https://www.dropbox.com set up, do so and install it on your computer / VPS. The process is rather quick, and if you are using a VPS, you can choose to only sync one specific folder.

Once this is done, open Notepad++ and create a new .bat file containing the following line (note the spaces are important):
robocopy C:\address\of\your\profit\trailer\folder C:\address\of\your\dropbox\folder ProfitTrailerData.json > C:\address\of\your\dropbox\folder\externalbackup.log

The picture below shows my backup (I am using two bots so am backing up two files).

•••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••

• Schedule the copy every day

We now need to run this .bat file once a day. To that end, open Windows' Task Scheduler.

Under the menu titled Action, click "Create Basic Task..."

Give the new task a name.

Schedule it daily.

At a particular time (11.59pm might be a good time, but really, any time is fine).

The action: Start a program.

Locate the .bat file on your computer.

And finally, review and finish!

•••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••

• Setting up the Google Spreadsheet's initial data

Now for the easy part. Click on the following Google Spreadsheet and make a copy of it for yourself:
https://docs.google.com/spreadsheets/d/1Q7LuRKGFDbj59lOjxNqyuGE3rD1v35lwO9Pj8KmLdNo/edit?usp=sharing

In there, there are only a handful of cells that you can / should edit:

Cell AV1: The URL of your "ProfiTrailerData.JSON" file.
*note: when you copy the URL of a dropbox file it will look like the following:
https://www.dropbox.com/s/3hhjcnjhfs0i1tp/ProfitTrailerData.json?dl=0
This needs to be changed to:
https://dl.dropboxusercontent.com/s/3hhjcnjhfs0i1tp/ProfitTrailerData.json

Cell D5: The Exchange your bot is on (The file is currently set up for Binance, Poloniex, Bittrex).
*note: each exchange affect the way the JSON file writes data: select the right one!

Cell F5: The Currency you want to see all prices in.
*note: I have made every effort to include all possible currencies (over 100 currencies currently in there)

Cells D7 onwards: Manual deposits and withdrawals.
*note: positive numbers for deposits, negative numbers for withdrawals

Cells F7 onwards: Manual deposits and withdrawals dates.
*note: for the most accurate calculations, I recommend you deposit or withdraw at midnight (00.00am)

•••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••

• We also need to authorize the scripts and schedule their automatic running.

First go to Tools > Script Editor

Then Run > Run Function > Refresh

Wait a little while and you will be prompted to authorize the script to run.

Once it is authorized, wait for the script to finish (might take a minute), and go to Edit > Current project's triggers

From there we want to schedule triggers so that the sheet auto updates:

• Refresh > Time-driven > Hour timer > Every hour
• Refresh > From spreadsheet > On open
• Snapshot > Time-driven > Hour timer > Every 12 hours
  *note: the first two triggers check for an update to your JSON file. Realistically, it will only change once every 24 hours, but if you decide to copy it more often in the future, the triggers are already there. The third trigger is used to get the 7-days chart of your base coin

•••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••

That's it! Phew! It's a decent amount of setting up, but the results are well worth it!

As a quick overview of what the sheet has to offer:

• Summary Tab: This is where you get all the stats. Daily profits, monthly profits, total current value, number of transactions per day, and you can even select which coins are your top or lowest performing, and by how much!
• All_Trades Tab: This is a log of every buy and sell from your bot. How much it has spent, the profits made, whether you have some of the coin left, etc.
• Pending_Orders Tab: What the bot is currently trying to sell, but the orders haven't been filled yet. If there is going to be leftover coin, this info is there too.
• Current_DCA Tab: As the name suggests, what bags are you currently holding, how many times you have DCA'ed each coin, and your current profit on it.
• All-Time_DCA Tab: As the name suggests, any coin that's ever been DCA'ed.

One thing to note: The base coin is pulled automatically from the file, BUT if you decide to change base coins, Profit Trailer will delete all the data in your JSON file. As such, if you decide to change base coins, back up the current JSON file so you have all the current data somewhere. After that, just create a new copy of the spreadsheet and run through the process again.

•••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••

That's it. This thing has taken so much of my time that I felt I couldn't just keep it to myself. So enjoy it, and send any recommendations / feedback my way. I'm always happy to improve.

If you end up using this a lot, consider donating :)

Bitcoin (Segwit): 3Et1fdzYFd3aV5AuMRYHvEY2csBgDj4y9q
Bitcoin (Legacy): 1JcWDKzn2UX1uueGqfqNok8bAhu8iGJdEy
Bitcoin Cash: 1GYRLoLDqpPyebrV54VAkEHhwat3YQN815
Litecoin (Segwit): MWroemjoKcW6ZXKHPqrXMf7pQ6SU8YSfzc
Litecoin (Legacy): LhcN6kgPzgSthtVHQiFKTxb7FEZYzi2Gn4
Ethereum: 0xaD339cb8a0EE6074ac4092F87A6DD17aEcF88d70
Ethereum Classic: 0x846a0B6cFf5fa9D2Bb44B6Fa953e0D0934B00145
Ripple: rKpzAExtC8VqwegmW7KkAKYvbgsxk4bScF