Moving forward with my Market Spreadsheet

I’ve been at a stalemate with my Eve Online Market spreadsheet for some time now.

When I originally created it it was detailed and worked like a charm, but was messy and vastly data intensive. After I finished adding “the rest” of the item’s on the EVE Market I ran into the Google Quota limit issue and this caused me to stop development. Since then I’ve been attempting to find a work around but have come to the conclusion that the sheet cannot work in it’s current state and having multiple sheets across multiple Google accounts is not the answer.

There are a few things I could do but each has it’s own catch.

  1. Split the sheet up into multiple sheets across multiple accounts. I don’t need to explain why this is not going to happen.
  2. Cut the item lists back so fewer data calls are made resulting in less chance of hitting the Quota but an incomplete item list and therefore missed potential profit.
  3. Cut the data calls back significantly resulting in less detailed data but having the very basics to make an investment decision.

Cutting the data calls back significantly would result in a far less detailed view of each market item but should enable me to have each item listed and I’ll at least be able to see the margin on them all. The current sheet calls the price data as well as yesterday’s low and high values, makes a partially accurate calculation for yesterday’s profit and then by use of simple parameters and “if” statements  it tells you if you should invest or not. There are 6 data calls per item and roughly 7000 items it’s calling data for – more than double Google’s daily quota.

The changes I currently have in mind would cut back the 42,000 data calls to about 14,000. Whilst heavily simplifying things and getting to within the Quota limit, it could still hit that limit if the data is loaded more than once per day. I’m currently messing around with refresh values to see if it is possible to have it hard coded only to load once per day but seeing as it refreshes whenever somebody loads the workbook I don’t think cutting back the data calls to the bare minimum is going to be the solution to the problem, putting us back to square 1.

I am exploring the idea of having a cell you can use to choose which data set to load (Drones, Equipment, Ships etc.) which would mean data calls are only made when told to, but switching between data sets could result in a higher number of data calls than we’re currently seeing.

Anything I try seems to be putting me back where I started, but I’m confident I’ll find a solution that will result in a usable trading sheet.