Read on Twitter
Google Sheet Template / Script to Snapshot your @THORChain_org ChaosNet Liquidity Pooling Data on a Time-based IntervalFull usage guide begins as of point #9 in this thread. Please read it carefully and follow all steps.
Setup time is about 15 minutes for first timers.
2/ What can you do with this sheet / script?
1) Input your BNB address.
2) Request a snapshot of your stakes in liquidity pools.
3) Set time-based events to aggregate snapshots on minute, hourly, daily, weekly, monthly intervals.
4) Doesn't pull retroactively. Only future data!
1) Input your BNB address.
2) Request a snapshot of your stakes in liquidity pools.
3) Set time-based events to aggregate snapshots on minute, hourly, daily, weekly, monthly intervals.
4) Doesn't pull retroactively. Only future data!
3/ The snapshots pull all your address' pools and their respective:
a) RUNE Share
b) Asset Share
c) RUNE Share in BUSD
d) Asset Share in BUSD
a) RUNE Share
b) Asset Share
c) RUNE Share in BUSD
d) Asset Share in BUSD
4/ Using this raw data you can create new sheets, query the data and create new reports about your performance including graphs and all! You can also track others address' performance, and make sense of their activity and potentially strategies!
5/ @THORChain_org is still in ChaosNet and is actively looking for the community to find issues. If you identify a problem with the the API or even the CLP model, please submit a bug or contact a core dev.
6/ Data will be pulled in the following chronological format:
7/ Early tests show it has an 0.75 error rate using a 1 minute interval: https://twitter.com/mehowbrains/status/1311653644920672257?s=20
8/ You can edit and extend the script as you wish. I'm hoping the community will rally together to come up with meaningful reports and share amongst one another in order to help educate others about LP'ing on @THORChain_org. The goal is to build confidence in the network.
9/ So how do you get your own copy, set it up and run it?
a) Visit the following URL: https://docs.google.com/spreadsheets/d/1ayjrkBoSSd7oMhMWg3rnuCFO6bSJ0ptKWQh1GewZpvU/edit?usp=sharing
a) Visit the following URL: https://docs.google.com/spreadsheets/d/1ayjrkBoSSd7oMhMWg3rnuCFO6bSJ0ptKWQh1GewZpvU/edit?usp=sharing
10/ IMPORTANT:
b) Click "File" > "Make a copy". Name and save it wherever you want in your Google Drive.
c) Close the window with my original sheet so you don't confuse yourself.
b) Click "File" > "Make a copy". Name and save it wherever you want in your Google Drive.
c) Close the window with my original sheet so you don't confuse yourself.
11/ With your new copy:
d) Click on > "Settings" tab at the bottom of the sheet.
e) In the B1 cell enter your BNB Address which you stake-with in Liquidity Pools on @THORChain_org ChaosNet.
d) Click on > "Settings" tab at the bottom of the sheet.
e) In the B1 cell enter your BNB Address which you stake-with in Liquidity Pools on @THORChain_org ChaosNet.
11/
f) You will get a new URL for your sheet (since you copied it). It'll include a ssID composed of 44 characters after "spreadsheets/d/" in your URL.
g) Copy that sheet ID.
f) You will get a new URL for your sheet (since you copied it). It'll include a ssID composed of 44 characters after "spreadsheets/d/" in your URL.
g) Copy that sheet ID.
12/
h) Click "Tools" > "Script Editor".
h) Click "Tools" > "Script Editor".
13/
i) On line #7 of the script locate the old ssID.
j) Replace the ssID with your own ssID you just copied in steps f) and g). Make sure to paste it between the single quotes. Do not remove the quotes.
i) On line #7 of the script locate the old ssID.
j) Replace the ssID with your own ssID you just copied in steps f) and g). Make sure to paste it between the single quotes. Do not remove the quotes.
14/
k) Press the floppy disk icon to save the script.
k) Press the floppy disk icon
to save the script.
15/ Next you'll want to setup your trigger which'll pull the data automatically on an interval.
l) Click on the clock icon. A new window will open.
l) Click on the clock icon
. A new window will open.
16/
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.
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.
17/
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".
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".
18/
q) For the field named "Select type of time based trigger", select whichever interval you think you'll select.
r) For the field named "Select hour interval", select the interval you want the script to pull your address' pool data.
s) Click on > "Save" to finish.
q) For the field named "Select type of time based trigger", select whichever interval you think you'll select.
r) For the field named "Select hour interval", select the interval you want the script to pull your address' pool data.
s) Click on > "Save" to finish.
19) 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 LP Snapshots via API (unsafe)".
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 LP Snapshots via API (unsafe)".
20)
v) Click on > "Allow" to grant the sheet permissions.
v) Click on > "Allow" to grant the sheet permissions.
21/
w) You may close your Triggers window and Script windows and return to your Google Sheet.
x) If you visit the "Snapshots" tab/sheet at the bottom of your Google Sheet, you will see an empty table with some headings.
w) You may close your Triggers window and Script windows and return to your Google Sheet.
x) If you visit the "Snapshots" tab/sheet at the bottom of your Google Sheet, you will see an empty table with some headings.
22/ Next, you need to trigger your first pull.
y) Click on > "THORChain LP" in the Google Sheet's menu.
z) Click on > "Pull Snapshot".
y) Click on > "THORChain LP" in the Google Sheet's menu.
z) Click on > "Pull Snapshot".
23/ A popup will appear, prompting you to grant permissions like on part 19/ steps t) to v).
Depending on the time interval selected, your snapshot pulls should begin to aggregate in the sheet.
You may close the sheet, it will run in the background as well.
Depending on the time interval selected, your snapshot pulls should begin to aggregate in the sheet.
You may close the sheet, it will run in the background as well.
24/ Google sheets limits you to 5 million cells per sheet. They have no row limits. So depending on the time interval chosen, you can fill up 5 million cells quite fast.
Special thanks to @Four4Newt for the collaboration re: formulas and API endpoints.
Make some reports!
Special thanks to @Four4Newt for the collaboration re: formulas and API endpoints.
Make some reports!
25/ P.S. Since this script does not pull retroactively, it's best if you set it up for your wallet ASAP so that you can gather your data going forward.
26/ Once @THORChain_org goes MainNet, we likely will need to update the script with new endpoints. You'll all have to update your scripts, it won't be difficult.
27/ If you don't know how to query data in Google Sheets, you can always hire an analyst / data scientist on @Upwork or some other freelance site to help you report things. Talent around the world is available at your fingertips. You can also always speak to others in community.
28/ Whoever has data to show, feel free to reach out to me. Things like this need to be on Twitter and outbound to generate excitement about @THORChain_org pooling.
29/ This thread has been included in the “Staker Starter Pack” if you want to onboard new Liquidity Providers, just tweet the whole starter pack using the @THORChainHammer command:
@THORChainHammer starter-pack-thorchain-stakers https://twitter.com/mehowbrains/status/1302820807610118144
@THORChainHammer starter-pack-thorchain-stakers https://twitter.com/mehowbrains/status/1302820807610118144
@THORChainHammer starter-pack-thorchain-stakers
30/ 
Important:
Best to not use 1m interval as eventually you may run out of computational power by Google Apps. @Bitcoin_Sage ran into such issue. Likely anything bigger than 5m - 1h is much better.
Important: Best to not use 1m interval as eventually you may run out of computational power by Google Apps. @Bitcoin_Sage ran into such issue. Likely anything bigger than 5m - 1h is much better.
