Bitcoin Forum

I'm trying to create a Google Sheets tracker or my Crypto trades.

How would I calculate a percentage gain or loss on my account balance?

Let's say I have $2000 in my trading account, and I place a trade that goes from 0.6000 to 0.6100 (100 ticks).

How would I calculate that as a percentage gain and a monetary gain on my account?

Appreciate the help,

JH

Final price divided by the initial price.
0.6 1÷ 0.6 = 1.0167

(Final price divided by initial price) multiply by your trading money.
1.0167 * 2000 = 2033

You trade with $2000
You make $2033
You earn $33

$33 of $2000 makes it $33 of 100% ($2000) money used to trade.

33 = x
2000 = 100

Cross multiply

X * 2000 = 33 * 100

X = 33 * 100 ÷ 2000

X = 1.65%

Which means $33 that you gained is 1.65% of $2000 that you used to trade.

I do not know if this can have a single formula that can be used.

This is great. Really helpful. I'm getting closer with your method.

But I'm just wondering how to create a Google Sheets formula to come out with the percentage gain/loss.

If I have $2000 in my account, and I am trading with 1% of my account balance, then my formula so far is:

(Final Price / Initial Price) * (Account Capital * 1%)

This gives me a dollar figure.

How do I convert this into a % of the total account size ($2000)?

JH

you can try with this formula
Final price - Starting price / Starting price * 100

to simplify in terms of google sheets
A1 = Initial price
A2 = Final price

Therefore

= (A2 - A1) / A1*100 0r 1%

or another formula

=TO_PERCENTAGE(A2-A1)/A1

obviously the percentage is calculated on the dollar if you use dollars as a reference in the cells, but if you want to know the gain on satoshis use satoshis as a reference

Try this formula.

Percentage gain or loss=(Initial account balance/Difference)×100

To find the Difference, subtract the Initial account balance from the Final account balance.

So if the result is positive, that's a percentage gain, and if it's negative, that's a percentage loss.

Thank you very much.

Using the above information, I used this formula which seems to work:

=(E2-C2)*(A2*B2)*100

(Exit price - Entry price) * (Account Capital * Amount Risked %) * 100

Thanks a lot

JH

Instead of trying to formulate each equation individually, I suggest you download a template with all the details you need and then use it directly to get a result like:


You can find the detailed explanation here https://www.coingecko.com/learn/crypto-portfolio-tracker-google-sheets
Then you can search for open source codes on github that enable you to perform several tasks by downloading a simple template in a more professional manner.

You can use Portfolio tracker tools from Coingecko, Coinmarketcap.

Coinmarketcap Portfolio tracker (https://coinmarketcap.com/portfolio-tracker/) which is also available on iOS and Android.

If you like open source, you can consider this one.
Portfolio Performance [Open Source] - Keep track of your Crypto Portfolio! (https://bitcointalk.org/index.php?topic=5381647.0).

You can easily implement such a calculator in your spreadsheets, I have seen such a topic before on this platform made by one of our local community members snowpega. This Sheet May Help you to calculate Your Portfolio Easily. (https://bitcointalk.org/index.php?topic=5471875.msg63062579#msg63062579) OP did not gave us the link to that spreadsheet but I hope by mentioning his name he would walk here and might give you the link or maybe a tutorial in dept to make a calculator.

In short, you can make that, by integrating some price API of the coins you want to calculate, but if you don't want to insert any API and you just want to enter the amount you want to invest, and entry and exit point, and thus without API you also have to give the actually or current price of the token so you would get the profit. To do that, you have to use basic mathematical formulas in your sheet and if you are new to sheets then watching a tutorial will do the work for you.

If you trade on binance, you don't need to bother calculating this. because there is already a menu, you can directly check whether the period is 1 month or how long the trade can be directly seen profit and loss.
or if you want to keep calculating you can use this crypto calculator, the link is https://www.cryptoprofitcalculator.com/

Calculating profits and loss can be achievable if one restrategize his plans and ensure he grab significant profits in the system. We can not keep trading for months without evidence, there's certainly a day we can reap good profits, I call them good paying days while there are some days that doesn't end in profits at our end, I call it bad days because there's nothing exciting about not milking the system. Binance is the world top crypto exchange and we have everything at our end to ensure to start reaping gigantic profits in the system.  I'm not aware that binance have this imperative features on the exchange. Now it's brought to my notice and I'll ensure to work diligently on the details on becoming very confident in dealing with the system whenever I'm been given the chance.

I did not see anyone shared this https://blockchair.com/markets

It is an Anonymous portfolio tracker from Blockchair.com. You can choose a cryptocurrency into a Quick Lookup box, get a cryptocurrency you want to add to your Portfolio tracker, type the amount and finish.

You only have to bookmark this page on your browser and can check the portfolio change any time when you have access to Internet.

Looks like @op has finished his "homemade" portfolio tracker. G-sheets does allow you to customize the portfolio display that you want in such a way. And I prefer to use the portfolio API of any price aggregator site that provides as a reference source, this can display an accurate portfolio based on current prices rather than changing it manually.

I'm still here, and I prefer to use the manual spreadsheet as it has more data.

But I'm trying to figure out how to calculate this for the sheet...

I'm going long on a trade.

I enter XRP at 0.6221 and I intend to exit at 0.6247 (TP).
But I also intend that when the price reaches halfway (0.6234), I sell half my position and let the other half ride on until it hits the profit target.
I am risking 1% of my $2000 account ($20) on the trade.

How would you make a formula for this, in words?

Appreciate the help,

JH

https://www.talkimg.com/images/2023/12/22/IM42b.png

Its not a hard thing to do, i can help you but i need to know what are the data you are going to insert and what do you expect to be the return of the things you add.

To calculate the percentage gain or loss on your account balance, you would need to know the initial and final values of your trade. Here's how you can do it:
- Calculate the difference between the final value and the initial value of your trade. In your case, the difference is 0.6100 - 0.6000 = 0.0100.
- Calculate the gain or loss by multiplying this difference with your account balance and the number of units traded. If you traded 1 unit, the gain would be 0.0100 * $2000 = $20.
- Calculate the percentage gain or loss by dividing the gain or loss by the initial account balance and multiplying by 100. The percentage gain would be ($20 / $2000) * 100 = 1%.
Here's how you can do this in Google Sheets:

A       B       C       D       E       F
1   Initial   Final   Difference   Balance   Gain    Percentage Gain
2   0.6000    0.6100  =B2-A2       $2000     =C2*D2  =(E2/D2)*100

In this table:
- Column A is the initial value of the trade.
- Column B is the final value of the trade.
- Column C calculates the difference between the final and initial values.
- Column D is your account balance.
- Column E calculates the gain by multiplying the difference with the balance.
- Column F calculates the percentage gain by dividing the gain by the balance and multiplying by 100.
I hope this helps!   ;D ;D ;D

So i am trying to understand what you said and everyone who comment in this post.

I usually trade on binance future and still i didnt understand why you take small trade with small ROE.
So i am assuming that you trade with leverage but i dont understand which broker/app you use

No, I trade spot and I don't use Leverage.

What would be the best way to trade to make the most gains with say $2000 in the account, without leverage?

What would be the best strategy for this situation?

JH

ok, i get it. include fee or no fee?

it's good if you didn't use leverage but you can't short i mean sell something that you don't have right?
i have spot trading but i hold it for longer period of time, it's been a month now for bull and i made 49%

Without leverage there will be very less opportunities to take a trade and resist you to make more profits in less time.
You can use 2x leverage which is not too risky and in thIs case you can short the market too  which will give you more opportunities to enter a trade. More opportunities= more profit. But in last you need to be right most of the time to make profits.

That is another option, the more interesting thing is you can run more than 1 trade at the same time.
For example
If you want to take 1% risk, you have more option to trade like short/long up to 100x leverage and you dont have to use full balance
another 99% you can use to trade another asset class

Visit babypips.com use their risk calculator I think that will help you can further put your answer in percentage but the calculator gives your answers with respect to monetary value also