THORChain Skittles: Liquidity Provider Google Sheets Script & Report
What can you do with the Google Sheet Script?
- Take background snapshots of your THORChain’s BEPswap Liquidity Pooling data on a 1 hour interval (doesn’t need to stay open, you can close the sheet and it’ll keep pulling your data).
- Generate reports and visualize charts based on the selected asset/pools, intervals and date ranges.
- Basically, you can track your Liquidity Pooling performance over time.
How do I set it up?
a) Visit the sheet’s template via this link.
b) Click on “File” → “Make a copy”. Name your copy whatever you want, and save it wherever you want in your Google Drive.
c) Close the window with my original sheet so you don’t confuse yourself.
With your new copy:
d) Click on “Settings” tab at the bottom of the sheet.
e) In the B1 cell paste the BNB wallet address which you stake-with in THORChain Liquidity Pools (as seen below):
f) Look at your new Google Sheet’s URL, and look for an ssID composed of 44 characters in your URL. It’ll look something like this:
https://docs.google.com/spreadsheets/d/1sl0FDnEYAMohkMAFmrLPACnGK0RA9pjPUjBYkYyGAPw/edit
ssID = 1sl0FDnEYAMohkMAFmrLPACnGK0RA9pjPUjBYkYyGAPw
g) Copy your ssID into your clipboard so that you can paste in the next steps.
h) Click “Tools” → “Script Editor”.
i) On line #7 of the Code.gs locate the old ssID.
j) Replace the old ssID with your own ssID you just copied in steps f) and g). Make sure to paste it between the single quotes and do not remove the quotes.
k) Press the floppy disk icon to save the script.
l) Click on the clock icon. A new window will open.
m) In the Google Apps Scripts / Triggers window, click on “Add Trigger” (blue button at the bottom right of the window). A popup will appear.
n) For the first named “Choose which function to run”, select “getData”.
o) For the field named “Choose which deployment should run”, leave “Head” selected.
p) For the field named “Select event source”, select “Time-driven”.
q) Important: For the field named “Select type of time based trigger”, select “Hour timer”.
r) Important: For the field named “Select hour interval”, select “Every hour”.
s) Click on “Save” to finish.
A popup “Sign in with Google” may appear. It’s normal.
t) Click on *your Google account* in the list.
u) You will get a notification “This app isn’t verified”. It’s fine, simply click on “Advanced” and then click on “Go to THORChain Skittles (unsafe)”.
v) Click on “Allow” to grant the sheet permissions.
w) Close your Triggers window and Script windows and return to your Google Sheet.
x) Click on “File” → “Spreadsheet settings”. Pick your time zone if you’d like the snapshots to appear in your local time. DO NOT change the Locale of the sheet as it’ll mess it up completely. Keep it as “United States”.
y) Click on the“Snapshots” tab at the bottom of your Google Sheet, you will see an empty table with some headings.
z) Click on “THORChain Skittles” in the Google Sheet’s menu. Click on “Pull Snapshot”.
A popup will appear, prompting you to grant permissions like in steps t) to v).
The script will make some API calls and collect your data from that point on, every hour. You will see the snapshots appear in your “Snapshots” sheet.
You may close the sheet, the script will run in the background as well.
How do I generate a report?
Once you have a few hours of data accumulated:
a) Click on the“Report” tab at the bottom of your Google Sheet, you will see a bunch of empty graphs and a table at the bottom. Don’t edit anything. This report is fixed this way. It would require modification to the script if you were to delete/move any cells, columns or rows.
b) Your only tools are at the top right of the “Report” sheet in cells Q1, Q2, Q3 and Q4.
c) The “Asset” field allows you to pick the pool you’d like to filter.
d) The “Interval” field allows you to pick the interval you’d like to average data-to and filter. The lower the interval, the more data points you’ll see if you pick a large date range. The higher the interval, the less data points you’ll see if you pick a large date range.
e) The “From Date” and “To Date” allows you to pick a date range you’d like to filter.
f) The “Apply” button allows you to execute the filter.
Google Sheets are slow!
This is Google Sheets, not some well-designed database and web tool. Filtering will take time. It can actually take up to 6 minutes to run the script (before Google Sheets actually kills the script and dies — yep that’s the limit).
So what can you do to speed it up? If you pick a large date range, you have a lot more data to process. If you pick a higher interval i.e. 12h, it’ll have to compute less data than if for example you’d like to see all your 1h interval data points for a large date range.
Rule of thumb:
- If you’re filtering one month of data, you can get away with 1 or 2h interval filters.
- If you’re filtering more than one month of data, you’ll have to play with 4h-24h intervals.
- If you’re filtering many months of data, well it just may be too much for Google Sheets to execute in under 6 minutes, and you won’t be able to visualize the whole in one chart/table.
Bugs
We’ve been testing this sheet for over a week. It is possible that we missed a bug though! If you see anything funky, feel free to reach out to MehowBrains.
Frequently Asked Questions
At the bottom of your sheet you have a tab named “FAQ”. If you come across any problems or questions, you may find your answer in that sheet. Otherwise feel free to DM MehowBrains on Twitter or Telegram, or join the THORChain Telegram community.
Why Skittles?
By coincidence the colours we used for charts were the exact same as the Skittles rainbow. So we called it Skittles.
Credits
This Google Sheet Script and Report is a collaboration between several THORChain community members, namely:
- MehowBrains (hired a dev for Google Sheet Script and filter features).
- Larrypcdotcom and Bitcoin_Sage (built the Reports table and charts).
- Tox (helped bug test the sheet).
- Four4Newt (helped with API endpoints and formulas for snapshots script).
Product of the community-driven THORChain App Dev Team. Want us to build more tools? Leave us your feedback via this link.
