These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

Need help with SQL Statements - Profit calculation

First post
Author
Tiberius Zol
Moira.
Villore Accords
#1 - 2014-07-06 11:18:55 UTC
Hi guys,

i try to build my trackingtool for my industrystuff and want to track my profits for my trader also. My idea was the following:
I write all my buy transactions via API in a stack table (with quantity, quantity left field). No problem.
But now, i want to read all my sell transactions against this table and get all the profits and write them somewhere else. I want to use the FiFo concept.
But there are not only 1:1 relations between buy and sell orders also. Sometimes there 2 stacks with a 5 items stack and a 10 items stack in my stack table and now i sell 2 items, then 7 items, thean 6 items. All with different prices.
How can i get the relevant stack tables only? I really have no idea how to build the right sql statements here. My rudimentary Mysql skill end somewhere after join statements. :)

Maybe some of you guys can help me here :)

Mr. Tibbers on twitter: @Mr_Tibbers

Mr. Tibbers Blog: www.eve-versum.de

Wafflehead
Garoun Investment Bank
Gallente Federation
#2 - 2014-07-06 22:31:48 UTC
Tiberius Zol wrote:
Hi guys,

i try to build my trackingtool for my industrystuff and want to track my profits for my trader also. My idea was the following:
I write all my buy transactions via API in a stack table (with quantity, quantity left field). No problem.
But now, i want to read all my sell transactions against this table and get all the profits and write them somewhere else. I want to use the FiFo concept.
But there are not only 1:1 relations between buy and sell orders also. Sometimes there 2 stacks with a 5 items stack and a 10 items stack in my stack table and now i sell 2 items, then 7 items, thean 6 items. All with different prices.
How can i get the relevant stack tables only? I really have no idea how to build the right sql statements here. My rudimentary Mysql skill end somewhere after join statements. :)

Maybe some of you guys can help me here :)


You just need to walk through all the sell orders, while also going through the buy orders, check if there is a match, calc how many you need to fulfull this sell order, calc how many items left on buy order, determine if sell <= buy or sell > buy
After that you need to simply calc how many items have been sold, calc broker and taxes save profit to array/key.

Once all transactions have been done then you need to update the database with profits.
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#3 - 2014-07-07 01:37:13 UTC
take a look into Yapeal.

It can take care of the population of the database for you (market orders, transactions)

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter

Hel O'Ween
Men On A Mission
#4 - 2014-07-07 16:45:50 UTC
Tiberius Zol wrote:

But now, i want to read all my sell transactions against this table and get all the profits and write them somewhere else. I want to use the FiFo concept.
But there are not only 1:1 relations between buy and sell orders also. Sometimes there 2 stacks with a 5 items stack and a 10 items stack in my stack table and now i sell 2 items, then 7 items, thean 6 items. All with different prices.


That's why I'm not using the FIFO concept for my application. It's to error-prone for my taste. Instead I went the route of averages, bcause no matter what, to turn a profit on an item, your average sell price needs to be higher than your average buy price.

Which leads us to a very simple formula:

SELECT (SUM(price * quantity) / SUM(quantity))

Refine that to the typeID you'Re interested in, transaction type (sell or buy), date range, solar system, station name etc.

EVEWalletAware - an offline wallet manager.

Wafflehead
Garoun Investment Bank
Gallente Federation
#5 - 2014-07-07 18:50:08 UTC
Why would it be prone to error? The only error would be the coder.

Using average is not acceptable in my view.
Tiberius Zol
Moira.
Villore Accords
#6 - 2014-07-07 19:46:59 UTC
Wafflehead wrote:
Why would it be prone to error? The only error would be the coder.

Using average is not acceptable in my view.


For me neither... want to have exact numbers. Think i figured it out... maybe on a to complicated way.. but it should work.

@Steve: yeah i'm using yapeal already. Helps me a lot for all my stuff. Hopefully it will get an update soon after the crius update.

Mr. Tibbers on twitter: @Mr_Tibbers

Mr. Tibbers Blog: www.eve-versum.de

Johnathan Roark
Quantum Industries
#7 - 2014-07-08 04:08:10 UTC
Tiberius Zol wrote:
Wafflehead wrote:
Why would it be prone to error? The only error would be the coder.

Using average is not acceptable in my view.


For me neither... want to have exact numbers. Think i figured it out... maybe on a to complicated way.. but it should work.

@Steve: yeah i'm using yapeal already. Helps me a lot for all my stuff. Hopefully it will get an update soon after the crius update.


I've been helping Dragonaire with some of the updates with the next version. We have looked at the new endpoints. I am hoping to add them to the 1.1.x-WIP branch either sometime this week or early next week. I do not know when it will get merged with a branch that you would actually want to use.

EVEVERIFY - A recruiting API Verification and Audit Tool

Also try out Yapeal for your php api needs

Tiberius Zol
Moira.
Villore Accords
#8 - 2014-07-08 07:25:40 UTC  |  Edited by: Tiberius Zol
Johnathan Roark wrote:


I've been helping Dragonaire with some of the updates with the next version. We have looked at the new endpoints. I am hoping to add them to the 1.1.x-WIP branch either sometime this week or early next week. I do not know when it will get merged with a branch that you would actually want to use.


Using the masterbranch atm. Will use 1.1.x. if all industry and walletthings will work.

Mr. Tibbers on twitter: @Mr_Tibbers

Mr. Tibbers Blog: www.eve-versum.de

Hel O'Ween
Men On A Mission
#9 - 2014-07-08 16:21:37 UTC
Wafflehead wrote:
Why would it be prone to error? The only error would be the coder.


Make that user instead of coder and I agree.

FIFO gets you terrible wrong results when items materialize from nowhere and/or vanish into nowhere.

EVEWalletAware - an offline wallet manager.

Jeronica
Tackled In Belt
xXPlease Pandemic Citizens Reloaded Alliance.Xx
#10 - 2014-07-11 04:05:27 UTC  |  Edited by: Jeronica
Hel O'Ween wrote:
Wafflehead wrote:
Why would it be prone to error? The only error would be the coder.


Make that user instead of coder and I agree.

FIFO gets you terrible wrong results when items materialize from nowhere and/or vanish into nowhere.


Why would the API give you false quantities?

To OP, what I did with eve-mogul is do a stock system. This is the workflow of an item purchased to sold.


  1. Purchase Ingame
  2. API pull is initiated, script backchecks previous transactions and then saves the new transactions. I use "states", 0 being fresh, 1 being process.
  3. I have another process that cycles through the transactions where state = 0, if its a buy it then makes a "stock" row, in a new table (with relevant details needed to calc profit.)
  4. If it's a sell transaction, it checks the "stock" table for stored stock in chronological order FIFO and then calculates the profit from that, then saves a new "profit" row, while destroying the stock it used, where I can do reports/display data from.
  5. With that data, I can generate other reports that I need.

EVE-MOGUL.COM

Trade Profit Tracking&Analytics

Offering Sotiyo Services In

New Caldari | Ashab

IPOs & Investments

Hel O'Ween
Men On A Mission
#11 - 2014-07-11 07:47:10 UTC
Jeronica wrote:
Hel O'Ween wrote:
Wafflehead wrote:
Why would it be prone to error? The only error would be the coder.


Make that user instead of coder and I agree.

FIFO gets you terrible wrong results when items materialize from nowhere and/or vanish into nowhere.


Why would the API give you false quantities?


The API doesn't give you wrong results - your assumption (with FIFO) does. The API doesn't distinguish between transactions for personal consumption and transactions "for profit", for example.

- That expensive fitting you bought in a hurry in far away dead-end low sec system for your PvP ship ... it's First In ... but if you match that to the supposed to be First Out, well your margin is f*****.

If you aware of all these special cases and code around them/handle them manualy ... FIFO does work.

tl;dr
Know what you're doing and all is fine.

EVEWalletAware - an offline wallet manager.

Tiberius Zol
Moira.
Villore Accords
#12 - 2014-07-11 07:58:15 UTC
Jeronica wrote:


Why would the API give you false quantities?

To OP, what I did with eve-mogul is do a stock system. This is the workflow of an item purchased to sold.


  1. Purchase Ingame
  2. API pull is initiated, script backchecks previous transactions and then saves the new transactions. I use "states", 0 being fresh, 1 being process.
  3. I have another process that cycles through the transactions where state = 0, if its a buy it then makes a "stock" row, in a new table (with relevant details needed to calc profit.)
  4. If it's a sell transaction, it checks the "stock" table for stored stock in chronological order FIFO and then calculates the profit from that, then saves a new "profit" row, while destroying the stock it used, where I can do reports/display data from.
  5. With that data, I can generate other reports that I need.


yeah. This is exactly what ive done also. Good to see, that i was right with it. :-)

Stil workin on it. have to include taxes and fee now. :)

Mr. Tibbers on twitter: @Mr_Tibbers

Mr. Tibbers Blog: www.eve-versum.de

Tiberius Zol
Moira.
Villore Accords
#13 - 2014-07-11 08:02:57 UTC
Hel O'Ween wrote:
Jeronica wrote:
Hel O'Ween wrote:
Wafflehead wrote:
Why would it be prone to error? The only error would be the coder.


Make that user instead of coder and I agree.

FIFO gets you terrible wrong results when items materialize from nowhere and/or vanish into nowhere.


Why would the API give you false quantities?


The API doesn't give you wrong results - your assumption (with FIFO) does. The API doesn't distinguish between transactions for personal consumption and transactions "for profit", for example.

- That expensive fitting you bought in a hurry in far away dead-end low sec system for your PvP ship ... it's First In ... but if you match that to the supposed to be First Out, well your margin is f*****.

If you aware of all these special cases and code around them/handle them manualy ... FIFO does work.

tl;dr
Know what you're doing and all is fine.



For personal stuff i use a boolean column and set it to true in this case. But mostly my trader trades no personal stuff.

Mr. Tibbers on twitter: @Mr_Tibbers

Mr. Tibbers Blog: www.eve-versum.de