Rewrite the Profit & Loss calculation to persist it in the database

[cryptoeax]

The current implementation of the P&L section is really a giant hack. Each time we want to satisfy a dynamic request from the web UI, we have to parse the entire log and look for trades. Even worse, the log will get deleted gradually, and its contents may be inaccurate due to past bugs and also in-flight transfers (since we currently use the asset balance difference before and after the trade to decide how much we have bought/sold so an incoming transfer can mislead us.)

I decided to spend the time to rewrite this properly. We need to persist the profit and loss information in the database so that we can quickly fetch it when needed. Also it would be nice to have a place where we have a record of each single transaction so that we can start to collect more information about transactions to see if we can come up with ideas on how to improve the bot. For example, I'm planning to record the order books at the times a trade is made to see if we could avoid incurring losses by detecting specific patterns in the order book...

There will be an import step require to import the current data into the database, which would be a one time operation. The bot will prompt about this on first launch after an update but otherwise these changes should remain invisible if everything goes successfully.

The way this will work is that we will try to match the information in the log against the order history data that we can get from the exchanges. Unfortunately the exchanges don't provide API access for the full order history, so the user will need to download the appropriate CSV files manually for this import process. I have a history of ~3200 transactions and the import code is now working well for this history...

[cryptoeax]

I've started to work on this in https://github.com/cryptoeax/arbbot/tree/pl-rewrite. Please note that running this branch before it's finished is dangerous if you don't know what you're doing.

[Scorpic1]

P&L calculate sometime work not correct. There are times when the order filled not full, and the calculator does not take this moment into account. Because of this we see wrong Profit / Loss. I think this is bug.

[cryptoeax]

Yes, this is an example of what this rewrite is trying to solve.

[Scorpic1]

Other example, if order is sold at different prices, the total selling price will be different and see wrong Profit / Loss.

[Scorpic1]

I do know why bot not sell 229.56399367 LBC/BTC on Poloniex, buy 229.56399367 LBC/BTC on Bittrex, but not sell on Poloniex. This is bug. If we have on two diffrent account on exchange total 500 LBC coin, after it we 729 LBC on two account on exchange and buy more more. I think that the total quantity of coins should not change and bot will need control it.

[cryptoeax]

Other example, if order is sold at different prices, the total selling price will be different and see wrong Profit / Loss.

@Scorpic1 you don't need to provide individual examples one by one, I am aware of the general issue. Like I said, this rewrite is about fixing the general problem. Right now the P&L section is an approximation.

[cryptoeax]

I do know why bot not sell 229.56399367 LBC/BTC on Poloniex, buy 229.56399367 LBC/BTC on Bittrex, but not sell on Poloniex. This is bug. If we have on two diffrent account on exchange total 500 LBC coin, after it we 729 LBC on two account on exchange and buy more more. I think that the total quantity of coins should not change and bot will need control it.

The bot always places limit orders, which means if the market moves too quickly beyond the price limit of your order, part or all of your orders may remain unfilled. This is an inherent risk of arbitrage. The bought could place a market order instead but that would be even riskier because then the trade could happen at any price. For example in the trade you are complaining about, you should be thankful that no LBC coins were sold, because what had happened was that the liquidity of the market at the limit price wasn't sufficient to satisfy your order, and had the bot placed a market order it might have had liquidated some LBC coins at a much cheaper price than desired. :-)

Note that the APIs that the bot is using to talk to the exchanges are REST APIs so the bot isn't a high frequency trade bot and there can be hundreds of milliseconds of delay between the time we place an order and the time at which we look at the market order book.

[remipaeta]

We can use the Websocket API no ?

[remipaeta]

@cryptoeax Can we now use the pl-rewrite branch or we have to wait ?

[cryptoeax]

We can use the Websocket API no ?

There two exchanges have no WebSocket API.

@cryptoeax Can we now use the pl-rewrite branch or we have to wait ?

Not at all, please wait. When this work is ready I will merge it to the production branch.

Matching the existing data against the trade history from the exchanges has turned much more difficult than I expected. I have something that mostly works now but is not yet good enough. The algorithm has a difficult time when there are several trades in a short window of time which haven't been completely filled. It currently sometimes picks the wrong trade for the wrong transaction.

I have tweaked the algorithm quite a bit and I'm unsure how to make it much better. I may end up employing a different strategy and give up on importing the entire trade history from the exchanges, and just stick to what we have in the database and focus on getting the profit/loss information correctly after the transition to store things into the database. It's less ideal than my original intended approach, but it's not the end of the world, the future is bigger than the past...

[Scorpic1]

About WebSocket API. Links below is not a WebSocket API? Sorry, I do not understand this and I can be wrong.

https://github.com/dparlevliet/node.bittrex.api https://github.com/dutu/poloniex-api-node

[cryptoeax]

This is completely off topic for the current issue, but let me elaborate.

Bittrex has a WebSocket API that their own web UI uses but it is currently undocumented. Apparently they are going to release it in the next few months. The node client you have found is using parts of that API, but reverse engineering enough of what we need is a lot of work and it makes sense to wait for them to release proper documentation.

Poloniex has some WebSocket APIs which we could use, specifically for the ticker and orderbook updates, but still no WebSocket API for actually placing the order so it would still not solve the problem I was referring to before. I have thought about using this API at some point in the future.

[remipaeta]

@cryptoeax Can we test the functionality or do we still have to wait ?

[cryptoeax]

The work is almost finished, it's just one more thing that I need to implement before it's finished, but in fact if you are willing to run some manual mysql commands on your bot's database, the pl-rewrite branch is already ready for testing. (If you don't run those mysql commands a future proper upgrade won't happen when the code gets merged to the production branch.) The mysql commands are totally safe to run and they just roll back all of the work that the new code on the pl-rewrite branch does.

Here is how to test this:

• Stop your bot.
• Either download a ZIP from the pl-rewrite branch or git checkout it.
• Start the bot again.

The bot will prompt you for some things the first time it runs after the upgrade. Please pay close attention to the prompts and follow them carefully. When the bot starts to run and become operational again you should notice that most things are working like before. The one difference with the code right now is that the P&L UI will load faster (especially if your bot has made a lot of trades) but will not be kept up to date any more (that is the unfinished part) but don't worry about it, it's just a data presentation issue, all of your data is being stored safe and sound, and once you go back to the production branch everything will go back to working normally. I have been testing this experimental branch with my live bot dozens of times daily. :-)

When you are done testing please run the following mysql commands:

mysql -u arbitrage -p arbitrage
mysql> DROP TABLE exchange_trades;
mysql> DROP TABLE profit_loss;
mysql> quit

Most of the changes made run during the first upgrade. I'm interested to know if you hit any errors there or after that while the bot is running. I'm also interested in feedback on the quality of the prompts, if you think they aren't clear, and if you have suggestions for improvements. Thanks!

[cryptoeax]

Some update fresh from the oven. I had some time to finish the work to calculate the profit/loss of each trade right after making one based on the information obtained from the two exchanges about how much actually was traded on each side which is the last big remaining piece. So right now if you test the pl-rewrite branch, the P&L interface should keep up to date, and in the log, after each trade, you should see a line like 02:28:54: Calculated P&L: 0.00000416.

One thing that is interesting to know while testing is that by going back to the production branch and comparing the rows in the P&L table for the trades the bot made while it was on the pl-rewrite branch, you can see how the bot used to calculate the P&L per trade (the inexact way) and how much difference it makes compared to when you use the information available from the exchange. I am seeing that usually each trade's profit used to be a bit underestimated before.

I would appreciate if people can test this a bit. I'm planning to run this branch for a bit to see if I spot any problems with it that I haven't seen before.

[Scorpic1]

cryptoeax Thank you very much for your work!

I want to start testing the pl-rewrite branch, but this is branch need history .csv files. If I do not want upload history files? What to do? I am setup it brash again and delete old database from production branch. Thanks.

[cryptoeax]

Why do you not want to upload your trade history files? Going forward the bot will need those files to be able to run correctly.

[cryptoeax]

(Note that this experimental branch will be merged to production soon.)

[Scorpic1]

Upload history files .csv
Before make:

sudo apt-get install ntpdate
sudo ntpdate ntp.ubuntu.com

Have Error below. With production branch not have it error.

00:01:51: ARBITRATOR V2.0 launching... 00:01:51: Loading config... 00:01:51: Enabling Bittrex... 00:01:51: Enabling Poloniex... 00:01:51: Configured 2 exchanges! 00:01:51: Testing exchange access... 00:01:52: BITTREX [OK] 00:01:52: POLONIEX [OK] 00:01:53: [POLONIEX] Invalid data received: ([])

Fatal error: Uncaught exception 'Exception' with message '[POLONIEX] API error response: Nonce must be greater than 1513296113282620. You provided 1513296113282620.' in /var/www/arbitrage-bot/bot/xchange/Poloniex.php:573 Stack trace:

0 /var/www/arbitrage-bot/bot/xchange/Poloniex.php(167): Poloniex->queryAPI()

1 /var/www/arbitrage-bot/bot/TradeMatcher.php(26): Poloniex->queryTradeHistory()

2 /var/www/arbitrage-bot/bot/TradeMatcher.php(48): TradeMatcher->getExchangeNewTrades()

3 /var/www/arbitrage-bot/main.php(79): TradeMatcher->hasExchangeNewTrades()

[cryptoeax]

We have seen this bug in many other issues. :-( I have been trying to figure it out but I haven't been able to reproduce it. Can you run the following command in /var/www/arbitrage-bot and paste the full output here please? ls -lR Thanks!

[Scorpic1]

root@h1685VM:/var/www/arbitrage-bot# ls -lR .: total 326 -rw-r--r-- 1 root root 128534 Dec 15 02:39 bittrex-fullOrders.csv drwxr-xr-x 3 root root 4096 Dec 14 22:47 bot -rw-r--r-- 1 root root 5588 Dec 9 20:22 config.ini -rw-r--r-- 1 root root 5665 Dec 6 08:45 config.ini.example -rw-r--r-- 1 root root 4818 Dec 11 23:44 database.sql -rw-r--r-- 1 root root 3559 Dec 11 23:44 import-profit-loss.php -rw-r--r-- 1 root root 422 Dec 11 23:44 import-profit-loss-prompt.txt drwxr-xr-x 2 root root 4096 Dec 11 23:44 lib -rw-r--r-- 1 root root 35150 Dec 6 08:45 LICENSE -rw-r--r-- 1 root root 3270 Dec 14 09:20 main.php -rw-r--r-- 1 root root 55046 Dec 15 02:47 poloniex-tradeHistory.csv -rw-r--r-- 1 root root 1751 Dec 11 23:44 profit_loss.sql -rw-r--r-- 1 root root 9818 Dec 6 08:45 README.md drwxr-xr-x 4 root root 4096 Dec 11 13:09 web

./bot: total 104 -rw-r--r-- 1 root root 20115 Dec 14 09:20 Arbitrator.php -rw-r--r-- 1 root root 24598 Dec 14 09:20 CoinManager.php -rw-r--r-- 1 root root 11803 Dec 6 12:07 Config.php -rw-r--r-- 1 root root 20169 Dec 14 09:20 Database.php -rw-r--r-- 1 root root 6629 Dec 14 09:20 Exchange.php -rw-r--r-- 1 root root 1078 Dec 14 09:20 Orderbook.php -rw-r--r-- 1 root root 1551 Dec 14 09:20 TradeMatcher.php -rw-r--r-- 1 root root 1206 Dec 14 09:20 utils.php drwxr-xr-x 2 root root 4096 Dec 15 03:19 xchange

./bot/xchange: total 224 -rw-r--r-- 1 root root 423 Dec 14 09:20 BITTREX-csv-missing.txt -rw-r--r-- 1 root root 128534 Dec 15 02:39 bittrex-fullOrders.csv -rw-r--r-- 1 root root 14365 Dec 14 09:20 Bittrex.php -rw-r--r-- 1 root root 428 Dec 14 09:20 POLONIEX-csv-missing.txt -rw-r--r-- 1 root root 16164 Dec 14 09:20 Poloniex.php -rw-r--r-- 1 root root 55046 Dec 15 02:47 poloniex-tradeHistory.csv

./lib: total 28 -rw-r--r-- 1 root root 27043 Dec 11 23:44 mysql.php

./web: total 108 -rw-r--r-- 1 root root 173 Dec 11 23:44 admin-ui.php -rw-r--r-- 1 root root 916 Dec 11 23:44 ajax.php -rw-r--r-- 1 root root 104 Dec 8 07:38 config.inc.php -rw-r--r-- 1 root root 109 Dec 6 08:45 config.inc.php.example drwxr-xr-x 2 root root 4096 Dec 11 23:44 flot -rw-r--r-- 1 root root 1068 Dec 11 23:44 header.inc.php -rw-r--r-- 1 root root 4247 Dec 11 23:44 index.html -rw-r--r-- 1 root root 323 Dec 11 23:44 logview.php -rw-r--r-- 1 root root 6056 Dec 11 23:44 ptoc.css drwxr-xr-x 2 root root 4096 Dec 11 23:44 scripts -rw-r--r-- 1 root root 40064 Dec 11 23:44 ui.js -rw-r--r-- 1 root root 16533 Dec 11 23:44 WebDB.php

./web/flot: total 188 -rw-r--r-- 1 root root 52966 Dec 11 23:44 jquery.flot.min.js -rw-r--r-- 1 root root 6568 Dec 11 23:44 jquery.flot.navigate.min.js -rw-r--r-- 1 root root 6919 Dec 11 23:44 jquery.flot.orderBars.js -rw-r--r-- 1 root root 6891 Dec 11 23:44 jquery.flot.time.min.js -rw-r--r-- 1 root root 23785 Dec 11 23:44 jquery.flot.tooltip.js -rw-r--r-- 1 root root 86659 Dec 11 23:44 jquery.min.js

./web/scripts: total 4 -rw-r--r-- 1 root root 1932 Dec 11 23:44 js.cookie.min.js

[Scorpic1]

Generated NEW API key, error same. =(

[cryptoeax]

Thanks for posting this. This unfortunately shows the previous guess I had for why this happens isn't correct either. I will keep thinking. :-/

[cryptoeax]

BTW you only need to place the CSV files in the root directory not bot/xchange. Was this not clear from the instructions?

[Scorpic1]

In root have same files. I am delete from bot/xchange error same.

[cryptoeax]

Yes, deleting those won't fix the bug, just thought to mention that the bot will never read the CSV files you have placed in bot/xchange...

[cryptoeax]

For those following along, the problems we were discussing with the Poloniex API issues should all be fixed now. I have also pushed a whole bunch of new fixes to the pl-rewrite branch. At this point this branch should be very close to the final version that I will hopefully merge to production in the next few days.

[Scorpic1]

cryptoeax thank you for your work!

I am start testing pl-rewrite branch and have some problem.

After bot buy SYS on Bittrex and sell on Poloniex 23.53015660 SYS What bot waiting so long time about 30min. And do not write record about this orders in P&L. Get some issued cyclically message from bot:

23:37:10: WARNING: not reciving all sell trades from the exchange in time, waiting a bit and retrying...
23:37:10: Received 823.53015660 SYS from the exchange while our wallets show a balance difference of 23.53015660 (23.53015660 - 0.00000000 finished deposits)
23:37:10: WARNING: not reciving all sell trades from the exchange in time, waiting a bit and retrying...
23:37:11: Received 823.53015660 SYS from the exchange while our wallets show a balance difference of 23.53015660 (23.53015660 - 0.00000000 finished deposits)
23:37:11: WARNING: not reciving all sell trades from the exchange in time, waiting a bit and retrying...
23:37:13: Received 823.53015660 SYS from the exchange while our wallets show a balance difference of 23.53015660 (23.53015660 - 0.00000000 finished deposits)
23:37:13: WARNING: not reciving all sell trades from the exchange in time, waiting a bit and retrying...
23:37:14: Received 823.53015660 SYS from the exchange while our wallets show a balance difference of 23.53015660 (23.53015660 - 0.00000000 finished deposits)
23:37:14: WARNING: not reciving all sell trades from the exchange in time, waiting a bit and retrying...
23:37:14: Received 823.53015660 SYS from the exchange while our wallets show a balance difference of 23.53015660 (23.53015660 - 0.00000000 finished deposits)
23:37:14: WARNING: not reciving all sell trades from the exchange in time, waiting a bit and retrying...
23:37:15: Received 823.53015660 SYS from the exchange while our wallets show a balance difference of 23.53015660 (23.53015660 - 0.00000000 finished deposits)
23:37:15: WARNING: not reciving all sell trades from the exchange in time, waiting a bit and retrying...
23:37:16: Received 823.53015660 SYS from the exchange while our wallets show a balance difference of 23.53015660 (23.53015660 - 0.00000000 finished deposits)
23:37:16: WARNING: not reciving all sell trades from the exchange in time, waiting a bit and retrying...
23:37:17: Received 823.53015660 SYS from the exchange while our wallets show a balance difference of 23.53015660 (23.53015660 - 0.00000000 finished deposits)
23:37:17: WARNING: not reciving all sell trades from the exchange in time, waiting a bit and retrying...
23:37:19: Received 823.53015660 SYS from the exchange while our wallets show a balance difference of 23.53015660 (23.53015660 - 0.00000000 finished deposits)
23:37:19: WARNING: not reciving all sell trades from the exchange in time, waiting a bit and retrying...
23:37:19: Received 823.53015660 SYS from the exchange while our wallets show a balance difference of 23.53015660 (23.53015660 - 0.00000000 finished deposits)
23:37:19: WARNING: not reciving all sell trades from the exchange in time, waiting a bit and retrying...
23:37:21: Received 823.53015660 SYS from the exchange while our wallets show a balance difference of 23.53015660 (23.53015660 - 0.00000000 finished deposits)
23:37:21: WARNING: not reciving all sell trades from the exchange in time, waiting a bit and retrying...
23:37:22: Received 823.53015660 SYS from the exchange while our wallets show a balance difference of 23.53015660 (23.53015660 - 0.00000000 finished deposits)
23:37:22: WARNING: not reciving all sell trades from the exchange in time, waiting a bit and retrying...
23:37:23: Received 823.53015660 SYS from the exchange while our wallets show a balance difference of 23.53015660 (23.53015660 - 0.00000000 finished deposits)
23:37:23: WARNING: not reciving all sell trades from the exchange in time, waiting a bit and retrying...
23:37:23: Received 823.53015660 SYS from the exchange while our wallets show a balance difference of 23.53015660 (23.53015660 - 0.00000000 finished deposits)
23:37:23: WARNING: not reciving all sell trades from the exchange in time, waiting a bit and retrying...
23:37:24: Received 823.53015660 SYS from the exchange while our wallets show a balance difference of 23.53015660 (23.53015660 - 0.00000000 finished deposits)
23:37:24: WARNING: not reciving all sell trades from the exchange in time, waiting a bit and retrying...

What's happening?

And have other problem re-balance worked is not correct and bot do not write info xfers window.

09:02:21: BITTREX: 10 SC (3 usages)
09:02:21: POLONIEX: 1600.42439517 SC (0 usages)
09:02:21: TOTAL: 1610.42439517 SC
09:02:21: AVERAGE: 1610.42439517 SC
09:02:21: XFER THRES.: 1000 SC
09:02:21: BITTREX is missing -1600.42439517 SC
09:02:21: POLONIEX could give 1600.42439517 SC
09:02:21: Transfering 1600.42439517 SC POLONIEX => BITTREX
09:02:22: Deposit address: 88918c50a572633c14a467bf89d489ba82a654663440db48e4672ea373674cf591c06b5c8aad
09:02:22: ERROR during management task: database insertion error: Data too long for column 'address' at row 1
#0 /var/www/arbitrage-bot/bot/CoinManager.php(761): Database::saveWithdrawal()
#1 /var/www/arbitrage-bot/bot/CoinManager.php(348): CoinManager->withdraw()
#2 /var/www/arbitrage-bot/bot/CoinManager.php(629): CoinManager->balance()
#3 /var/www/arbitrage-bot/bot/CoinManager.php(604): CoinManager->balanceAltcoins()
#4 /var/www/arbitrage-bot/bot/CoinManager.php(56): CoinManager->manageWallets()
#5 /var/www/arbitrage-bot/bot/Arbitrator.php(49): CoinManager->doManage()
#6 /var/www/arbitrage-bot/bot/Arbitrator.php(472): Arbitrator->loop()
#7 /var/www/arbitrage-bot/main.php(112): Arbitrator->run()
#8 {main}
09:02:22: Refreshing wallets...

Why bot send all SC coin to Bittrex account? Why not half of all SC coins?

Thanks.

[cryptoeax]

This was fixed in #33.

[cryptoeax]

I pushed another set of fixes to this branch today based on #31 and #32 which were revealed over the weekend when FLO went offline on Bittrex to make the code more robust.

[cryptoeax]

With #47 fixed, the tip of the pl-rewrite branch should now be able to recognize the orders that get fulfilled in multiple trades on Poloniex correctly. Also, the problems that I was aware of with ongoing withdrawals have all been addressed. At this point the tip of this branch is considered to be stable and close to ready to be merged into production.

If you see any final problems or bugs on this branch that have not been fixed with the latest code update that I just pushed, please raise them now. Thanks!

[cryptoeax]

I'm planning to merge the pl-rewrite branch to production tomorrow unless if I find any last minute issues, and with that, this issue can be finally closed!

[cryptoeax]

I've merged the pl-rewrite branch into production and deleted the branch. If you were using the pl-rewrite branch, you can update the bot as follows:

• Leave the bot running.
• git checkout production
• git pull
• Restart the bot

If not, the next time you update to the tip of the production branch, the bot will prompt that it wants to update your database, and it needs your trade history files from the exchanges. It will tell you how to download and install them in the right place.

I think I can call this fixed now.