Retaining desired ratio going forward

English
#1

Hey guys I need to create a spreadsheet that dynamically calculates the new balance between two tokens and works out the adjustment needed to bring the ratio back to the required ratio of 1 to 1.5.

Two tokens ABC and XYZ, The XYZ is paid as interest each week for holding both tokens and based on retaining close to the most valuable ratio of 1 to 1.5
The individual price of each token has to be considered in swapping/exchanging the XYZ token into the ABC token to rebalance back to the required 1 to 1.5 ratio.

The XYZ token will increase via an “interest payment” each week requiring say two weekly adjustments to keep the ratio as close to the 1 to 1.5 as possible.

So in my attached example sheet I need to take the 500 XYZ that is in excess of the 1 to 1.5 ratio and swap/exchange SOME OF THEM to ABC that will then retain the overall ratio of 1 ABC to 1.5 XYZ .
That is my dilemma?

Ultimately in the second phase of creating this I would like to be able to draw the token prices from a website but first I have to find out how to structure the calculation then deal with the hyperlink.
In the interim I will manually input the prices according to what I see on the website that provides the price feeds.

ABC to XYZ ratio.ods (10.5 KB)

#2

1 to 1.5 is 40% and 60 %
Add the quantities together and multiply the total by 0.6 and subtract that from the quantity of XYZ and add the same amount to ABC
ABC to XYZ ratioEA.ods (15.6 KB)

#3

Ok thank you Earnest I believe that will work. Sometimes overthinking the problem is a problem itself. just stripping down the base understanding is all that was required and you did just that. Very much appreciated and a big thank you.
Now onto the inserting the realtime data price feed for these tokens via API. Are you at all familiar with how this might be achieved within Calc. I have been trying Sheet/ External Data but after pasting in the URL and or API key nothing happens? Do I maybe need some sort of Extension to Calc do you know?

#4

Hi Earnest
Unfortunately that is not the right answer so I have found out. The calculation is confounded by the differences in the values of the two tokens.
The breakdown of allocating pure percentages to the numbers does not take into account these differences.
ie there is 500 XYZ surplus that we are trying to distribute to fit the 1 to 1.5 ratio BUT it takes 10 XYX to swap for 1 ABC so that is the base cost to “purchase” the ABC
But the end ratio has to end up at 1 to 1.5 in pure numbers disregarding individual values.
I cant quite figure it out but it would have to be something like the following which would be added into the total of the already 1 to 1.5 ration holdings?

XYZ spent Received ABC Balance of XYZ Ratio
435 43.5 65 1.49

I am using simple values here in this example of 0.1 and 0.01 but in reality the price values will fluctuate wildly so the eventual calculation will have to consider the prices when working out how many can be swapped/purchased each period.
Hopefully I have explained this clearly and you or someone else could chime in with an updated answer, fingers crossed.

#5

I can’t look at computer for a while. I was wondering if you wanted the ratio of values to be 1:1.5 but it wasn’t what the spreadsheet showed.
Add a column for the total value of ABC, that is CostABC*QtyABC. Do the same for XYZ. Add them together and apportion the values 40% and 60%.
Divide each allocation by its cost to get the quantity of each

#6

I don’t know what the ratio 1 to 1.5 refers to now but I am picking quantity x cost. Using that, the attached spreadsheet might surprise you as the current ratio in your sheet is 1:5
ABC to XYZ ratioEA2.ods (15.6 KB)

#7

Hey Earnest thank you for trying to help out with this as it is confusing as hell to me.

Ok let me try and explain in a clearer manner.

The aim is to adjust the holdings of ABC to XYZ at 1:1.5
Each period there is an interest payment received of 7% of the amount of ABC that is balanced by XYZ at the 1:1.5 ratio. This interest payment is in XYZ so the ratio will regularly require adjusting in an effort to maintain the 1:1.5 ratio.
When I accumulate an amount of XYZ the aim is to sell a portion of the XYZ and purchase more ABC thus increasing the amount of ABC that is balanced by the XYZ at the 1:1.5 ratio
The price values are very different and they are fluctuating regularly BUT the 1:1.5 ratio is to be worked out on the numerical amount held and not the values.
The values are critical in the amount that can be sold and purchased with a view to bringing the holdings back into the 1:1.5 ratio.
I hope that does not confuse you you much.

#8

Hey Earnest
I have sort of found the answer but I don’ know how to recreate the formula. I just did a list of possibilities in the light grey numbers and stumbled across the 1:1.49 which might be close enough?
Would you mind having a look and see what you think.
The idea is that I input the data each time at C6, D6 F6 G6 and the spreadsheet reveals the answer at M6 with P6 as a check that the required ratio is being used.
I have tried to do this but I cant just get there.
Hopefully you will see it clearer than I can.

ABC to XYZ ratio Working copy.ods (17.0 KB)

#9

This might be a bit simpler although you would probably want to have one row for each revision. The attached uses two rows only to avoid scrolling.

The green cells are the results. You should be able to change the quantity or the price of XYZ in the revision rows
ABC to XYZ ratio_EA3.ods (18.1 KB)

#10

Hi Earnest sorry I am having real trouble trying to understand the EA3 sheet.
For instance at H 10 you have added a token plus a value plus the other token and I am not sure why this would be requires. Also not sure where the 0.007 comes from?
I feel I was close to it in my earlier sheet but cannot quite get there.
I think I have to set the required ratio at 1:1.5 fill in the then current amounts held, the then current prices and the answer to how many ABC i need to buy is then shown at M6
I will keep working on it but I am appreciative of your valuable help thus far.
Regards
Wayne

#11

Hi Earnest
I have laid it out with some explanation of what I am trying to achieve as maybe I have not been doing the best job of explaining to you what my aim is.
Hopefully this helps
Wayne

Retain desired ratio going forward.ods (24.9 KB)

#12

It is in a sample row as an example of a change in value.

You obviously want to keep a record of the balancing, so your proposed layout is difficult.
I have re-arranged the spreadsheet to make it a bit clearer.

  • The main calculation cell is the darker green one.
  • If you need to change the ratio you can, just enter the new ratio in the pale yellow cell

Have a play with different scenarios
ABC to XYZ ratio_EA4.ods (17.1 KB)

#13

Hey Earnest
Thank you so much for all your effort. I will look at this in the morning.
It is actually not necessary to save a record of the rebalances as the blockchain will preserve these transactions when I need them for tax reporting.
I just need to be able to that tricky calculation at the end of each period so as I know what to swap and retain the ratio.
Many thanks🙏
Wayne

#14

Hey Earnest we have success now.
I cannot thank you enough for sticking with this which for me was very difficult to fully understand what was needed.
I knew what I wanted but not how to construct it so that the correct results showed.
I made a minor tweak and it all made complete sense.

I have then changed another copy to something like what I will ultimately use after i continue to test it for a while.
Both files attached
Super appreciative
I guess I will now re-post and seek some guidance with trying to gather the prices in real time from the internet. That’s going to be another challenge as well but I will do some more research before I post.

So I will mark this as Solution

ABC to XYZ ratio_EA4 (1).ods (15.6 KB)

ABC to XYZ ratio FINAL.ods (22.9 KB)

#15

I am afraid that your “correction” of my spreadsheet creates an incorrect total, and a ratio of 1.54

Fortunately, you ignored your “correction” in the Final sheet anyway so all is good.

#16

Hey Earnest just for the record I do not see the ratio of 1.54 from my change? Rather it is showing ratio 1.50? I am using the final but it is based on your EA4 ?
I have been testing it and it seems to be working perfectly thanks to your persistence :clap:

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.