• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help in calculating the profit of a virtual trade bot


New Member
This will be a tough one and I hope you can help me somehow. First of all, let me introduce you to the topic.

I have a trade bot which deals with trading in- and out specific items with different prices based on a price update every 6 hours. The bot currently deals with 100 specific items (which I put in manually) and it accepts 4 types of currency as exchange. Stock is limited to 1, which means that if the bot doesn't have for example "A Red Hat", it creates a Buy order for players and once it is traded in, it will cancel the Buy order and list it as a Sell order. It can take a couple of minutes until the item is traded away from the bot, but it can even take days during which other items are still traded.

The four currencies are: /with their values/
Scrap Metal (3x Scrap Metal = 1 Reclaimed Metal) /0,11/
Reclaimed Metal (3x Reclaimed Metal = 1 Refined Metal) /0,33/
Refined Metal (60 Refined Metal = 1 Key) /1,00/
Key /60,00/

I receive the data of the inventory history in excel format, for example:
Player ID | Name | Time | Items MOVED from the bot | Items RECEIVED
3978700 |xAtlasFBTue |Dec 01 2020 17:47:49 |6x Refined Metal, 1x Reclaimed Metal | Gift Wrap
Bot PAID: 6,33 value of Refined (EDIT: This last line is written manually by me, only the above stuff is received in the data!)

Player ID | Name | Time | Items MOVED from the bot | Items RECEIVED

2347700 | engineer gaming |Dec 01 2020 18:53:08 | Gift Wrap |6x Refined Metal, 4x Scrap Metal
Bot SOLD the item for: 6,44 value of Refined, which means 0,11 Refined profit. (EDIT: This last line is written manually by me, only the above stuff is received in the data!)

1. In my opinion it's wise to start calculating an item's life when it gets to the bot, but it can happen multiple times a day.
2. The problem is that it may take hours or days to be traded away, between these two trades there were many other trades. Counting the profit manually takes minutes of scrolls in case it gets traded away in 5 days...
3. Multiple of the same items are separated by commas, so first I started to search how many of the currencies are there in a single string, as you can see on the attached photo in the middle four columns.
4. In the middle I made calculations so that I added up the number of Refineds, Reclaimeds, Scraps and Keys on each sides (top side is what the bot paid, bottom side is what the bot received) and I wanted to subtract the received currency from the paid currency. It sounded like a great solution, but it turned out, that there are still items in the bot's inventory that are counted as loss of profit, because it paid for the items with the currency.
5. Then I somehow tried to differentiate the items that are still at the bot by using =OR to check if the trade line contains Refs/Recs/etc., if not, then it's FALSE. But I managed to find another problem: some people added extra currency with the items themselves, because otherwise they didn't have a change. It's like going to a shop and your bill is $1,10; you want to pay with a $10 banknote so you rather search an extra 10 cents to avoid lots of coins as a change. You still receive the same amount of change.

And that is where I am stuck now. I would like to count the profit within the 400 trades in the last 14 days. Just to avoid confusion, there are items with 5,00 Ref profit on a single trade, and similar looses can happen, too. So we can't say that divide 400 by 2 and you get 200 scraps which equal to 22 Refined.
How could I separate the trade in- and out of the items that I listed to trade with? Is it even possible, somehow?

My suggestions:

-I can type in the 100 names of the items and I can run a search in both sides to check how many times "A Red Hat" has been traded in and out. But still, the profit is missing.
-I should sort my list somehow, e.g first the trades that has no currencies and somehow link the other side of the trade with it. Because obviously in each side, there is always a case when it doesn't have a currency next to the item that got traded in or out (or yes, if I am unlucky and someone added currency because of a change).

Why do I want this, and why don't I talk with the programmer who programmed this stuff to implement such a thing?
Well, he built in a basic feature to show profit, but it also adds my own trades from my main account when I transfer in and out things. It also suffers with the change stuff. And also, while the 3 Metal currencies have a fix value, the Key's value always fluctuates so I really want to know the profit I made before I take out things from the bot.

I love using Excel 2003, forgive me, and unfortunately it doesn't recognize English templates (=OR and stuff).
Thanks for reading. I am eager to answer any questions, feel free to ask for anything if necessary.



Excel Ninja
As a new member, You've just read Forum Rules ...
You should reread those, especially part
How to get the Best Results at Chandoo.org
then You would notice ... what should You add to Your thread?
... with Your uploaded picture ... there are some challenges!