Prices refetched on every run

[jhoogstraat]

All prices are refetched every time the program is started.

This can take quite some time and I thought that's what the database is for.

[provinzio]

Nothing should be refetched from the API when a price for the correct timestamp exists in the database.

Only the prices from csv are checked on every run, but this shouldn't take long.

Virtual sells are fetched on every run, because they represent the sell of all coins at runtime. You can deactivate the virtual sell option in config.

[Griffsano]

Can your problem be resolved by setting CALCULATE_VIRTUAL_SELL = False in config.ini?
If not, it might be because the table names are not sorted in the get_price_db function, hence the prices for some pairs cannot be found in the database and are fetched again. This should be resolved by #106.

[jhoogstraat]

I did set it to False, but prices are refetched still... I'll try #106.

Edit: That produces other errors:
sqlite3.IntegrityError: UNIQUE constraint failed: DIA/USDT.utc_time

[Griffsano]

Hmm, that error should actually be caught. Could you provide more logging info (where this error is raised in the code) and maybe also the DIA/USDT lines of the export CSV that lead to this error (including header of the CSV). You can replace confidential info like username, timestamp, amount of DIA/USDT etc.

[Griffsano]

Before you do that, you could try one thing: Replace line 330 in database.py

CoinTaxman/src/database.py

Line 330 in b973530

if str(e) == f"UNIQUE constraint failed: {tablename}.utc_time":

by this and see if the error is resolved:
if f"UNIQUE constraint failed: {tablename}.utc_time" in str(e):

I don't think this resolves it, but it might be worth trying.

[jhoogstraat]

I'll try the code change first and then report the affecting lines.

Notice: I have to change a line in my statement, because of a crypto deposit, which would otherwise result in an "unmatched buy/sell positions". It does not work yet, but I changed:

86550091,2021-04-03 16:04:05,Spot,Deposit,EOS,31.20000000,""

to

86550091,2021-04-03 16:00:00,Spot,Deposit,EUR,164,""
86550091,2021-04-03 16:04:05,Spot,Buy,EOS,31.20000000,""
86550091,2021-04-03 16:04:05,Spot,Transaction Related,EUR,-164,""

which results in the error mentioned.

[jhoogstraat]

The error is not resolved by the code change. Here is a more complete log with the code change (notice the pair USDT/USDT).

The if statement seems to work, but the error handling does not.
Both variables price and price_db are 0 in this case.

2022-03-26 14:22:12,263 taxman       DEBUG    Starting evaluation...
2022-03-26 14:22:21,853 price_data   DEBUG    Calling https://api.binance.com/api/v3/aggTrades?symbol=DIAEUR&startTime=1618102920000&endTime=1618102980000
2022-03-26 14:22:22,162 price_data   DEBUG    Calling https://api.binance.com/api/v3/aggTrades?symbol=DIABTC&startTime=1618102920000&endTime=1618102980000
2022-03-26 14:22:22,428 price_data   WARNING  Binance offers no price for DIABTC at 2021-04-11 01:02:30+00:00. Trying DIA/USDT and BTC/USDT
2022-03-26 14:22:22,431 price_data   DEBUG    Calling https://api.binance.com/api/v3/aggTrades?symbol=DIAUSDT&startTime=1618102920000&endTime=1618102980000
2022-03-26 14:22:22,700 price_data   WARNING  Binance offers no price for DIAUSDT at 2021-04-11 01:02:30+00:00. Trying DIA/USDT and USDT/USDT
Traceback (most recent call last):
  File "C:\Users\jh\Developer\CoinTaxman\src\database.py", line 328, in set_price_db
    __set_price_db(db_path, tablename, utc_time, price)
  File "C:\Users\jh\Developer\CoinTaxman\src\database.py", line 275, in __set_price_db
    cur.execute(query, (utc_time, str(price)))
sqlite3.IntegrityError: UNIQUE constraint failed: DIA/USDT.utc_time

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\jh\Developer\CoinTaxman\src\main.py", line 54, in <module>
    main()
  File "C:\Users\jh\Developer\CoinTaxman\src\main.py", line 43, in main
    taxman.evaluate_taxation()
  File "C:\Users\jh\Developer\CoinTaxman\src\taxman.py", line 260, in evaluate_taxation
    self._evaluate_taxation_per_coin(operations)
  File "C:\Users\jh\Developer\CoinTaxman\src\taxman.py", line 249, in _evaluate_taxation_per_coin
    self.__evaluate_taxation(coin, coin_operations)
  File "C:\Users\jh\Developer\CoinTaxman\src\taxman.py", line 169, in _evaluate_taxation_GERMANY
    if tx_ := evaluate_sell(op):
  File "C:\Users\jh\Developer\CoinTaxman\src\taxman.py", line 106, in evaluate_sell
    sell_price = self.price_data.get_cost(op)
  File "C:\Users\jh\Developer\CoinTaxman\src\price_data.py", line 574, in get_cost
    price = self.get_price(op.platform, op.coin, op.utc_time, reference_coin)
  File "C:\Users\jh\Developer\CoinTaxman\src\price_data.py", line 554, in get_price
    price = get_price(coin, utc_time, reference_coin, **kwargs)
  File "C:\Users\jh\Developer\CoinTaxman\src\misc.py", line 204, in wrapper
    ret = func(*args, **kwargs)
  File "C:\Users\jh\Developer\CoinTaxman\src\price_data.py", line 116, in _get_price_binance
    btc = self.get_price("binance", base_asset, utc_time, "BTC")
  File "C:\Users\jh\Developer\CoinTaxman\src\price_data.py", line 554, in get_price
    price = get_price(coin, utc_time, reference_coin, **kwargs)
  File "C:\Users\jh\Developer\CoinTaxman\src\misc.py", line 204, in wrapper
    ret = func(*args, **kwargs)
  File "C:\Users\jh\Developer\CoinTaxman\src\price_data.py", line 135, in _get_price_binance
    usdt = self.get_price("binance", base_asset, utc_time, "USDT")
  File "C:\Users\jh\Developer\CoinTaxman\src\price_data.py", line 556, in get_price
    set_price_db(
  File "C:\Users\jh\Developer\CoinTaxman\src\database.py", line 334, in set_price_db
    rel_error = abs(price - price_db) / price * 100
decimal.InvalidOperation: [<class 'decimal.DivisionUndefined'>]

[Griffsano]

Ah ok, I see. The problem is not UNIQUE constraint failed, it's the zero price. This can be easily fixed.
However, the price fetching for DIABTC looks weird to me, I think this should not happen. Could you share the CSV entries for 2021-04-11 01:02:30+00:00?
The changes for the EOS deposit should be fine, I don't think this is causing it.

[jhoogstraat]

Sorry, forgot those! At the specific timestamp there are just conversions of small assets amounts to BNB, one of which is DIA.

86550091,2021-04-11 01:02:30,Spot,Small assets exchange BNB,DIA,-0.00031280,""

[Griffsano]

I made some fixes, could you try out the newest commit from #106?

[jhoogstraat]

That does make the error go away. Is setting the price to 0 not skewing with the result?

[Griffsano]

Yes, exactly. But now it should not be zero anymore, could you check this? For example, by adding this line after line 112:

CoinTaxman/src/database.py

Line 112 in b973530

price = __get_price_db(db_path, tablename, utc_time)

print("DB price: ", price)

If the database price is still zero, then I could not reproduce the problem. I think this is what happened:
As far as I could reproduce the problem, the price zero was written in your database for some reason (price_db). Once it's written in the database, it usually stays and is not overwritten (except for when a new price is found in the CSVs). With the quick fix that I implemented now, the price in the database (price_db) is automatically overwritten if it's zero, even if the source is not the CSV export. However, this logic also overwrites the DB price if the new price (price) is zero, so it's not the cleanest way to do this and no final solution.

---

Edit: I think I could reproduce it now with this CSV snippet:

User_ID,UTC_Time,Account,Operation,Coin,Change,Remark
,2021-04-11 01:02:00,Spot,Buy,DIA,60,""
,2021-04-11 01:02:00,Spot,Transaction Related,EUR,-100,""
,2021-04-11 01:02:30,Spot,Small assets exchange BNB,DIA,-30,""
,2021-04-11 01:02:30,Spot,Small assets exchange BNB,BNB,0.1,""
,2021-04-11 02:02:30,Spot,Small assets exchange BNB,DIA,-30,""
,2021-04-11 02:02:30,Spot,Small assets exchange BNB,BNB,0.1,""
,2021-04-11 03:02:30,Spot,Sell,BNB,0.1,""
,2021-04-11 03:02:30,Spot,Buy,EUR,50,""

In the first case (timestamp 01:02:30), no valid prices could be found and, due to a bug, zero is written in the database. This should definitely not happen. The reason that no price can be found is probably because you used the BNB conversion feature, while the API call looks for trading price data at this timestamp. In the second case (timestamp 02:02:30), a price can be found (coincidentally) and it works as intended. The bug that leads to the zero price being written in the database might have something to do with #74.

[jhoogstraat]

That makes sense, and I agree that this is what happened. Binance does not provide a price for DIA/EUR, nor DIA/BTC or DIA/USDT for the timeframe, so the price cannot be set (checked the api). As expected, 3 entries are 0 in the database.

[Griffsano]

It appears that once the price fetching falls back to USDT/USDT, zero is written in the DB:

CoinTaxman/src/price_data.py

Lines 130 to 131 in b973530

	if quote_asset == "USDT":
	return decimal.Decimal()

[jhoogstraat]

Which is a PR by me, coincidentally :P
I even wrote a check_database function to see if any entries are 0 and to try and fix them.

In the end, there are possible timeframes where binance does not provide prices, so we need a backup solution.