search

dickwolff / Export-To-Ghostfolio

Convert transaction history export from your favorite broker to a format that can be imported in Ghostfolio.
https://hub.docker.com/r/dickwolff/export-to-ghostfolio
Apache License 2.0
46 stars 8 forks source link

De Giro stock split shows up as 2 SELL transactions #98

Open fugkco opened 2 months ago

fugkco commented 2 months ago

While investigating #93, I noticed stock split transactions in De Giro are incorrectly showing 2 sell transactions, instead of 1 sell and 1 buy.

These are all the transactions for the AV.L that were in my export (I've removed the dividends):

Date,Time,Value date,Product,ISIN,Description,FX,Change,,Balance,,Order Id
02-04-2024,09:00,02-04-2024,AVIVA,GB00BPQY8M80,DEGIRO Transaction and/or third party fees,,GBP,-2.75,GBP,111.56,86c1f17b-8a74-4126-af39-61049bcb6e33
02-04-2024,09:00,02-04-2024,AVIVA,GB00BPQY8M80,Sell 4 AVIVA@496 GBX (GB00BPQY8M80),,GBP,19.84,GBP,114.31,86c1f17b-8a74-4126-af39-61049bcb6e33
20-06-2023,16:05,20-06-2023,AVIVA,GB0002162385,Cost of Stock,,GBP,2.19,GBP,26.01,
24-05-2022,09:48,19-05-2022,AVIVA,GB0002162385,Capital Return,,GBP,6.10,GBP,24.85,
16-05-2022,09:32,16-05-2022,AVIVA,GB0002162385,STOCK SPLIT: Sell 6 Aviva@0 GBX (GB0002162385),,GBP,0.00,GBP,12.55,
16-05-2022,09:32,16-05-2022,AVIVA,GB00BPQY8M80,STOCK SPLIT: Buy 4 AVIVA@0 GBX (GB00BPQY8M80),,GBP,0.00,GBP,12.55,
18-03-2020,12:13,18-03-2020,AVIVA,GB0002162385,DEGIRO Transaction and/or third party fees,,GBP,-1.75,GBP,2.87,d6f48f0a-9422-4fac-b493-b01ae95fe9b6
18-03-2020,12:13,18-03-2020,AVIVA,GB0002162385,DEGIRO Transaction and/or third party fees,,GBP,0.00,GBP,4.62,d6f48f0a-9422-4fac-b493-b01ae95fe9b6
18-03-2020,12:13,18-03-2020,AVIVA,GB0002162385,London/Dublin Stamp Duty,,GBP,-0.07,GBP,4.62,d6f48f0a-9422-4fac-b493-b01ae95fe9b6
18-03-2020,12:13,18-03-2020,AVIVA,GB0002162385,Buy 6 Aviva@233.1 GBX (GB0002162385),,GBP,-13.99,GBP,4.69,d6f48f0a-9422-4fac-b493-b01ae95fe9b6

Which results in the following json:

{
  "activities": [
    {
      "accountId": "98ab2695-a513-4346-9836-8b4f70a15544",
      "comment": "",
      "currency": "GBP",
      "dataSource": "YAHOO",
      "date": "2024-04-02T09:00:00+00:00",
      "fee": 2.75,
      "quantity": 4,
      "symbol": "AV.L",
      "type": "SELL",
      "unitPrice": 4.96
    },
    {
      "accountId": "98ab2695-a513-4346-9836-8b4f70a15544",
      "comment": "",
      "currency": "GBP",
      "dataSource": "YAHOO",
      "date": "2022-05-16T09:32:00+00:00",
      "fee": 0,
      "quantity": 6,
      "symbol": "AV.L",
      "type": "SELL",
      "unitPrice": 0
    },
    {
      "accountId": "98ab2695-a513-4346-9836-8b4f70a15544",
      "comment": "",
      "currency": "GBP",
      "dataSource": "YAHOO",
      "date": "2022-05-16T09:32:00+00:00",
      "fee": 0,
      "quantity": 4,
      "symbol": "AV.L",
      "type": "SELL",
      "unitPrice": 0
    },
    {
      "accountId": "98ab2695-a513-4346-9836-8b4f70a15544",
      "comment": "",
      "currency": "GBP",
      "dataSource": "YAHOO",
      "date": "2020-03-18T12:13:00+00:00",
      "fee": 0,
      "quantity": 6,
      "symbol": "AV.L",
      "type": "BUY",
      "unitPrice": 2.332
    }
  ],
  "meta": {
    "date": "2024-07-31T07:20:58.967Z",
    "version": "v0"
  },
  "updateCashBalance": false
}

The third item in that list should be of type BUY.

dickwolff commented 2 months ago

Yeah, that doesn't look right. I'll look into it later this week!

Also I see the transaction fees at the bottom are "out of order", so I won't be suprised if that also causes some issues. The current detection mechanism for that is not fool proof, so I think I'll also need to rework that as well..

fugkco commented 2 months ago

The order id matches the fee transactions, so maybe the detection should be around that?

Looking further into this, it seems Aviva is the only stock split done like this. It seems it's the only item where the unit price has been set 0 for a stock split. Other transactions seems to be correctly set.

dickwolff commented 2 months ago

Currently I try to look ahead or back, so this won't work. Looking for an order Id is a better approach, so that would be the way to go. I have a feeling the code will become much less complex after this.

dickwolff commented 1 month ago

I have just made a beta image available for a V3 converter for the DEGIRO converter. Could you try this for me and see if it works for you? Much appreciated! I'm looking for if this specific issue is resolved, and if the rest of the converter works as it normally would (processing all the relevant records). any feedback is welcome.

One thing to note is to absolutely make sure orderId is set in the export. I currently have not added any checks for this (maybe will just skip records that don't have the orderId set, but that is for later).

If you run it locally, you can do so by running npm run start degiro-v3. If you use Docker, you will have to add an environment variable to force the tool to run V3, which is -e DEGIRO_FORCE_V3=true You can use this image.

dickwolff commented 1 month ago

I have fixed some things about dividends (they were duplicated as dividends don't have an order id. That is fixed now and I merged V3 to main (behind a feature flag).

I would however still love to get your feedback on this @fugkco, so please let me know if you find any issues!

fugkco commented 1 month ago

Hey! Sorry it took this long. I'm getting the following error for many entries it seems:

account_exporter_degiro-1  | [e]        activities.0.unitPrice must not be less than 0
account_exporter_degiro-1  | [e]        activities.0.unitPrice must be a number conforming to the specified constraints
account_exporter_degiro-1  | [e]        activities.1.fee must not be less than 0
account_exporter_degiro-1  | [e]        activities.1.fee must be a number conforming to the specified constraints

Interestingly the previous posted CSV works as expected.

Here's an excerpt of an export that is failing:

Date,Time,Value date,Product,ISIN,Description,FX,Change,,Balance,,Order Id
09-08-2024,16:10,09-08-2024,MORGAN STANLEY GBP LIQUIDITY FUND,LU0904784781,Money Market fund conversion: Buy 0.5 at 1 GBP,,,,GBP,116.31,
08-08-2024,15:27,31-07-2024,MORGAN STANLEY GBP LIQUIDITY FUND,LU0904784781,Fund Distribution,,GBP,0.50,GBP,116.31,
07-08-2024,15:30,06-08-2024,MORGAN STANLEY GBP LIQUIDITY FUND,LU0904784781,Money Market fund conversion: Sell 0.02 at 1 GBP,,,,GBP,115.81,
03-08-2024,06:42,02-08-2024,,,FX Debit,,GBP,-0.02,GBP,115.81,
03-08-2024,06:42,02-08-2024,,,FX Credit,1.1706,EUR,0.02,EUR,0.00,
02-08-2024,19:02,31-07-2024,,,DEGIRO Exchange Connection Fee 2024 (OTC Markets Group - OTC),,EUR,-0.01,EUR,-0.02,
02-08-2024,19:02,31-07-2024,,,DEGIRO Exchange Connection Fee 2024 (New York Stock Exchange - NSY),,EUR,-0.01,EUR,-0.01,
08-07-2024,15:24,08-07-2024,MORGAN STANLEY GBP LIQUIDITY FUND,LU0904784781,Money Market fund conversion: Buy 0.91 at 1 GBP,,,,GBP,115.83,
05-07-2024,07:27,04-07-2024,,,FX Credit,,GBP,0.91,GBP,115.83,
05-07-2024,07:27,04-07-2024,,,FX Debit,1.2791,USD,-1.17,USD,0.00,
04-07-2024,15:16,04-07-2024,MORGAN STANLEY GBP LIQUIDITY FUND,LU0904784781,Money Market fund conversion: Buy 0.48 at 1 GBP,,,,GBP,114.92,
04-07-2024,07:40,03-07-2024,HP INC,US40434L1052,Dividend Tax,,USD,-0.21,USD,1.17,
04-07-2024,07:40,03-07-2024,HP INC,US40434L1052,Dividend,,USD,1.38,USD,1.38,
03-07-2024,09:41,30-06-2024,MORGAN STANLEY GBP LIQUIDITY FUND,LU0904784781,Fund Distribution,,GBP,0.48,GBP,114.92,
26-06-2024,16:18,26-06-2024,MORGAN STANLEY GBP LIQUIDITY FUND,LU0904784781,Money Market fund conversion: Buy 0.07 at 1 GBP,,,,GBP,114.44,
25-06-2024,07:00,24-06-2024,,,FX Credit,,GBP,0.07,GBP,114.44,
25-06-2024,07:00,24-06-2024,,,FX Debit,1.2716,USD,-0.09,USD,0.00,

Logs for said excerpt:

account_exporter_degiro-1  | [i] Automatic import is allowed. Start importing..
account_exporter_degiro-1  | [i] THIS IS AN EXPERIMENTAL FEATURE!! Use this at your own risk!
account_exporter_degiro-1  | [e] Import failed!
account_exporter_degiro-1  | [e]        activities.0.unitPrice must not be less than 0
account_exporter_degiro-1  | [e]        activities.0.unitPrice must be a number conforming to the specified constraints
account_exporter_degiro-1  | [e]        activities.1.fee must not be less than 0
account_exporter_degiro-1  | [e]        activities.1.fee must be a number conforming to the specified constraints
account_exporter_degiro-1  | [e]        activities.2.unitPrice must not be less than 0
account_exporter_degiro-1  | [e]        activities.2.unitPrice must be a number conforming to the specified constraints
account_exporter_degiro-1  | [e]        activities.3.unitPrice must not be less than 0
account_exporter_degiro-1  | [e]        activities.3.unitPrice must be a number conforming to the specified constraints
account_exporter_degiro-1  | [e]        activities.4.unitPrice must not be less than 0
account_exporter_degiro-1  | [e]        activities.4.unitPrice must be a number conforming to the specified constraints
account_exporter_degiro-1  | [e]        activities.6.fee must not be less than 0
account_exporter_degiro-1  | [e]        activities.6.fee must be a number conforming to the specified constraints
account_exporter_degiro-1  | [e]        activities.7.unitPrice must not be less than 0
account_exporter_degiro-1  | [e]        activities.7.unitPrice must be a number conforming to the specified constraints
account_exporter_degiro-1  | [e] Did not complete automatic import & validation due to errors: Error: Automatic import failed! See the logs for more details.
account_exporter_degiro-1  | [i] Finished converting export.csv, removing file..
dickwolff commented 1 month ago

Thanks for getting back to me. I’ll take a look at it and let you know what I find.

arvalaan commented 1 week ago

I ran into a similar issue with a stock split, I just tried running it with the V3 importer and ran into the issue below:


      const numberSharesFromDescription = record.description.match(/([\d*\.?\,?\d*]+)/)[0];
                                                                                      ^

TypeError: Cannot read properties of null (reading '0')
    at DeGiroConverterV3.mapRecordToActivity (/app/src/converters/degiroConverterV3.ts:237:87)
    at <anonymous> (/app/src/converters/degiroConverterV3.ts:138:39)```

It seems as though a transaction comes through even though it shouldn't. When isolating that one transaction group it does work properly. Furthermore, after solving this in my local instance with some very hacky code it did import successfully but still very much inflated due to a stock split issue. I'll dive into this further when I have more time.