search

anthonyk1225 / robinhood-to-xlsx

Python package that grabs your robinhood data and exports it to excel
MIT License
15 stars 3 forks source link

ZeroDivisionError: division by zero #20

Closed jdwall closed 3 years ago

jdwall commented 4 years ago

Hi,

Thank you so much for this project. Exporting to JSON presents no problems, however, when doing it to Excel I receive a ZeroDivsionError. It only happens for Orders. This was previously referenced in #12, however it's resurfaced it looks like. 

Traceback (most recent call last):
  File "app.py", line 48, in <module>
    run()
  File "app.py", line 41, in run
    json_to_xlsx.run(answer)
  File "/Users/jacobwall/GitHub/robinhood-to-xlsx/controllers/json_to_xlsx.py", line 81, in run
    formula_pipelines[entity](formula_worksheet, workbook, sorted_data)
  File "/Users/jacobwall/GitHub/robinhood-to-xlsx/formulas/orders.py", line 82, in handle_formulas
    write_aggregates(worksheet, workbook, data)
  File "/Users/jacobwall/GitHub/robinhood-to-xlsx/formulas/orders.py", line 33, in write_aggregates
    aggregates = aggregate_data(data)
  File "/Users/jacobwall/GitHub/robinhood-to-xlsx/formulas/orders.py", line 23, in aggregate_data
    equity_average = aggregates[symbol]['equity'] / aggregates[symbol]['quantity']
ZeroDivisionError: division by zero
anthonyk1225 commented 4 years ago

Hey, @jdwall. Thanks for the feedback. I'll look into that this weekend and try to get a fix for it.

anthonyk1225 commented 4 years ago

I've made an update on what data gets recorded and passed into the formulas function for orders. I've also made sure to except on zerodivisionerror, although this is only to see how many instances you run into while exporting to xlsx. Let me know if you still run into the same issue. If you do, let me know how many times it prints out for the zerodivisionerror exception. Thanks a lot!

jdwall commented 4 years ago

Thanks for continuing to look into this. I've hit the ZeroDivisionError exception again. I tried looking to see what would cause the error for brands but I couldn't find a reference in the code.

Some could be helpful, could not information... I hold USO options and UCO stock. USO recently went a reverse split so my USO options are now USO1, which may have weird implications on the backend. UCO went a traditional reverse split and I'm long, so I think RH should correctly report that but thought it might help track why my account is different than the others. 

(venv) robinhood-to-xlsx % python3 app.py -v
? What would you like to run  xlsx(Excel file)
? What would you like to generate?  done (4 selections)
Starting to write dividends to xlsx
Finished writing dividends to xlsx
Starting to write events to xlsx
Finished writing events to xlsx
Starting to write options to xlsx
SENDING REQUEST FOR THE OPTION INSTRUMENT URL
SENDING REQUEST FOR THE OPTION INSTRUMENT URL
SENDING REQUEST FOR THE OPTION INSTRUMENT URL
SENDING REQUEST FOR THE OPTION INSTRUMENT URL
SENDING REQUEST FOR THE OPTION INSTRUMENT URL
SENDING REQUEST FOR THE OPTION INSTRUMENT URL
Finished writing options to xlsx
Starting to write orders to xlsx
SENDING REQUEST FOR THE INSTRUMENT URL
SENDING REQUEST FOR THE INSTRUMENT URL
There was an error creating an instrument near "Brands": syntax error
SENDING REQUEST FOR THE INSTRUMENT URL
There was an error creating an instrument near "Brands": syntax error
There was an error tallying up profit/loss in orders
Finished writing orders to xlsx
anthonyk1225 commented 4 years ago

Ahhh, that actually is super helpful. Unfortunately, I don't have any data for what that looks like. I'm assuming there's a row in events that represents splits? It would be in the type column.

If that's the case, if you can provide me the fields for a row where there's a split, I can fix that.

This is good stuff, man. Thanks a lot for bringing this to my attention.

jdwall commented 4 years ago

Looking at events there is no split type right now. Digging on their website, I see no reference to the reverse split for my long shares. However, under Corporate Actions I was able to find how they're handling options that underlying symbol has a corporate action. The call is made at https://api.robinhood.com/options/corp_actions/

For options, the new types are: cash_in_lieu_settlement reverse_stock_split

{
  "next": null,
  "previous": null,
  "results": [{
    "id": "7b32efc6-5428-427b-878e-89fe51beaf08",
    "type": "cash_in_lieu_settlement",
    "effective_date": "2020-05-11",
    "state": "handled",
    "chain": "https:\/\/api.robinhood.com\/options\/chains\/f35e58d9-2f88-4985-adf5-6aff15d7067f\/",
    "new_cash_component": "8.1800",
    "old_cash_component": null,
    "new_symbol": "USO1",
    "old_symbol": "USO1",
    "new_trade_value_multiplier": "100.0000",
    "old_trade_value_multiplier": "100.0000",
    "underlying_instruments": [{
      "id": "d93e6a87-fbdb-4d29-92c3-bd01422ef86f",
      "instrument": "https:\/\/api.robinhood.com\/instruments\/cb39e60a-dfc6-44db-89b4-980d7aea608c\/",
      "symbol": "USO",
      "new_quantity": 12,
      "old_quantity": 12
    }],
    "affected_positions": [{
      "new_quantity": "2.0000",
      "old_quantity": "2.0000",
      "position": "https:\/\/api.robinhood.com\/options\/positions\/825e7ee3-1306-45b7-98f5-26829f72a2eb\/",
      "position_type": "long",
      "option": "https:\/\/api.robinhood.com\/options\/instruments\/d76c31af-8f62-4c25-8f4f-355f76006e91\/",
      "option_type": "call",
      "new_strike_price": "8.0000",
      "old_strike_price": "8.0000",
      "new_expiration_date": "2020-07-17",
      "old_expiration_date": "2020-07-17"
    }, {
      "new_quantity": "4.0000",
      "old_quantity": "4.0000",
      "position": "https:\/\/api.robinhood.com\/options\/positions\/f7248a2f-e5d1-471a-ba1a-ffb957ba3fa1\/",
      "position_type": "short",
      "option": "https:\/\/api.robinhood.com\/options\/instruments\/1974915a-c932-4a5d-b610-714c975c09bf\/",
      "option_type": "call",
      "new_strike_price": "8.5000",
      "old_strike_price": "8.5000",
      "new_expiration_date": "2021-01-15",
      "old_expiration_date": "2021-01-15"
    }, {
      "new_quantity": "2.0000",
      "old_quantity": "2.0000",
      "position": "https:\/\/api.robinhood.com\/options\/positions\/ed5a8561-673c-40d4-8d38-7e58777543ad\/",
      "position_type": "long",
      "option": "https:\/\/api.robinhood.com\/options\/instruments\/6316885a-b7f1-4746-8b56-c2b7f62f982f\/",
      "option_type": "call",
      "new_strike_price": "8.0000",
      "old_strike_price": "8.0000",
      "new_expiration_date": "2021-01-15",
      "old_expiration_date": "2021-01-15"
    }, {
      "new_quantity": "2.0000",
      "old_quantity": "2.0000",
      "position": "https:\/\/api.robinhood.com\/options\/positions\/4f3e2d3d-2eaa-4165-8c45-a697000f1362\/",
      "position_type": "long",
      "option": "https:\/\/api.robinhood.com\/options\/instruments\/a2f0d573-31b8-42da-8b68-ccb107c5be7d\/",
      "option_type": "call",
      "new_strike_price": "7.5000",
      "old_strike_price": "7.5000",
      "new_expiration_date": "2021-01-15",
      "old_expiration_date": "2021-01-15"
    }],
    "created_at": "2020-05-09T00:11:05.708259Z",
    "updated_at": "2020-05-11T11:11:29.227597Z"
  }, {
    "id": "230c1875-35aa-4ceb-b4ba-124b219b71db",
    "type": "reverse_stock_split",
    "effective_date": "2020-04-29",
    "state": "handled",
    "chain": "https:\/\/api.robinhood.com\/options\/chains\/5b710b1e-1ca2-42b2-8900-0d44175e25ca\/",
    "new_cash_component": null,
    "old_cash_component": null,
    "new_symbol": "USO1",
    "old_symbol": "USO",
    "new_trade_value_multiplier": "100.0000",
    "old_trade_value_multiplier": "100.0000",
    "underlying_instruments": [{
      "id": "6c8ece34-4ace-4862-b3fb-e9b000ba0191",
      "instrument": "https:\/\/api.robinhood.com\/instruments\/01f33471-51d9-4afe-850f-b5f13d58c459\/",
      "symbol": "USO",
      "new_quantity": 0,
      "old_quantity": 100
    }, {
      "id": "e829662c-669d-43fa-83ef-7421f8dd0b26",
      "instrument": "https:\/\/api.robinhood.com\/instruments\/cb39e60a-dfc6-44db-89b4-980d7aea608c\/",
      "symbol": "USO",
      "new_quantity": 12,
      "old_quantity": 0
    }],
    "affected_positions": [{
      "new_quantity": "2.0000",
      "old_quantity": "2.0000",
      "position": "https:\/\/api.robinhood.com\/options\/positions\/d99fe79b-99b9-426b-9915-ed030ed9f526\/",
      "position_type": "long",
      "option": "https:\/\/api.robinhood.com\/options\/instruments\/9ae81854-3a23-4b77-a014-ebca2c8690ab\/",
      "option_type": "call",
      "new_strike_price": "8.0000",
      "old_strike_price": "8.0000",
      "new_expiration_date": "2021-01-15",
      "old_expiration_date": "2021-01-15"
    }, {
      "new_quantity": "4.0000",
      "old_quantity": "4.0000",
      "position": "https:\/\/api.robinhood.com\/options\/positions\/46b80124-2b59-4585-9823-02c0e80035db\/",
      "position_type": "short",
      "option": "https:\/\/api.robinhood.com\/options\/instruments\/63c1d986-f835-4a50-ae97-2e6973cefaa5\/",
      "option_type": "call",
      "new_strike_price": "8.5000",
      "old_strike_price": "8.5000",
      "new_expiration_date": "2021-01-15",
      "old_expiration_date": "2021-01-15"
    }, {
      "new_quantity": "2.0000",
      "old_quantity": "2.0000",
      "position": "https:\/\/api.robinhood.com\/options\/positions\/24077ebd-aa5f-4083-8b14-d8dcccb62677\/",
      "position_type": "long",
      "option": "https:\/\/api.robinhood.com\/options\/instruments\/c9f67dc5-3db3-416a-a483-1ddd3d2061a7\/",
      "option_type": "call",
      "new_strike_price": "7.5000",
      "old_strike_price": "7.5000",
      "new_expiration_date": "2021-01-15",
      "old_expiration_date": "2021-01-15"
    }, {
      "new_quantity": "2.0000",
      "old_quantity": "2.0000",
      "position": "https:\/\/api.robinhood.com\/options\/positions\/dc9a2a2e-bc2f-4ee3-b2c3-836613a04419\/",
      "position_type": "long",
      "option": "https:\/\/api.robinhood.com\/options\/instruments\/0a44ea13-5e00-487f-9a78-7a403d574f5a\/",
      "option_type": "call",
      "new_strike_price": "8.0000",
      "old_strike_price": "8.0000",
      "new_expiration_date": "2020-07-17",
      "old_expiration_date": "2020-07-17"
    }],
    "created_at": "2020-04-29T00:11:56.678982Z",
    "updated_at": "2020-04-29T09:08:17.314714Z"
  }]
}
anthonyk1225 commented 4 years ago

Hey, just an update. I needed more info in terms of what corp_actions look like and what it does to an account. So I'm doing some research and purchasing small positions that are scheduled to split. Hopefully I can get an initial solution that works for you and others.

anthonyk1225 commented 3 years ago

Root of problem here is #21