Pages: [1] :: one page |
|
Author |
Thread Statistics | Show CCP posts - 0 post(s) |
Tiberius Zol
21
|
Posted - 2014.07.06 11:18:00 -
[1] - Quote
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 :)
|
Wafflehead
Hurricane Research
96
|
Posted - 2014.07.06 22:31:00 -
[2] - Quote
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
3504
|
Posted - 2014.07.07 01:37:00 -
[3] - Quote
take a look into Yapeal.
It can take care of the population of the database for you (market orders, transactions) Woo! CSM 9! http://fuzzwork.enterprises/ Twitter: @fuzzysteve on Twitter |
Hel O'Ween
Men On A Mission
83
|
Posted - 2014.07.07 16:45:00 -
[4] - Quote
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
Hurricane Research
96
|
Posted - 2014.07.07 18:50:00 -
[5] - Quote
Why would it be prone to error? The only error would be the coder.
Using average is not acceptable in my view. |
Tiberius Zol
22
|
Posted - 2014.07.07 19:46:00 -
[6] - Quote
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.
|
Johnathan Roark
Quantum Industries Prime Orbital Systems
5
|
Posted - 2014.07.08 04:08:00 -
[7] - Quote
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
22
|
Posted - 2014.07.08 07:25:00 -
[8] - Quote
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. |
Hel O'Ween
Men On A Mission
83
|
Posted - 2014.07.08 16:21:00 -
[9] - Quote
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
345
|
Posted - 2014.07.11 04:05:00 -
[10] - Quote
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.
- Purchase Ingame
- API pull is initiated, script backchecks previous transactions and then saves the new transactions. I use "states", 0 being fresh, 1 being process.
- 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.)
- 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.
- With that data, I can generate other reports that I need.
EVE-Mogul: https://www.eve-mogul.com CEO/Programmer Trade Profit Tracking Service |
|
Hel O'Ween
Men On A Mission
83
|
Posted - 2014.07.11 07:47:00 -
[11] - Quote
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
22
|
Posted - 2014.07.11 07:58:00 -
[12] - Quote
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.
- Purchase Ingame
- API pull is initiated, script backchecks previous transactions and then saves the new transactions. I use "states", 0 being fresh, 1 being process.
- 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.)
- 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.
- 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. :) |
Tiberius Zol
22
|
Posted - 2014.07.11 08:02:00 -
[13] - Quote
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. |
|
|
|
Pages: [1] :: one page |
First page | Previous page | Next page | Last page |