R help - Sep 2013 - the problem of buying and selling

I own a lot to the folks on r-help list, especially arun who answered every 
of my question and was never wrong. I am disinclined to once again ask this 
question, since it is more arithmatic than technical. But, having worked 2 
days on it, I realized my brain is just not juicy enough....

Here is the problem.

 	Trust not for freedom to the Franks---
 	They have a king who buys and sells.
 			- Lord Byron: The Isles of Greece

Suppose the French King commands you to buy and sell, and tells you only to
deal if the profit is higher than 2%. Question: how much quantity will be 
dealt, and what is the actual profit? In fact, the King wants to see the 
relationship between his minimum-profit requirement and your result, in 
order to better his decision.

Let's look at the input data - a dump of which is attached to this mail.

Column 1 is the price of the market where you buy goods from, column 2 is 
the quantity of goods that is being sold at that price.

Column 3 is the price of the market where you sell goods to, column 4 is the 
quantity the buyers willing to buy at that price.
> cbind(t(to_buy_from), t(to_sell_to))
 	 [,1]  [,2]   [,3]   [,4]
  [1,] 61.7050   190 63.170   2500
  [2,] 61.7500    29 63.150    799
  [3,] 61.8050   166 63.110    500
  [4,] 61.8950   166 63.060  10000
  [5,] 61.9450   166 63.020   7840
  [6,] 61.9805  6150 62.995   2000
  [7,] 62.0000  3069 62.930   2000
  [8,] 62.0600   166 62.860  10811
  [9,] 62.1100   166 62.780  18054
[10,] 62.1450   166 62.755   9000
[11,] 62.1750   166 62.690  10960
[12,] 62.2250   166 62.635    100
[13,] 62.2450   166 62.585   2380
[14,] 62.2720   100 62.550   2119
[15,] 62.2830  4000 62.525 108091
[16,] 62.2875   100 62.505   2000
[17,] 62.2955   100 62.485    816
[18,] 62.3250   307 62.435    600
[19,] 62.3800  2906 62.400    300
[20,] 62.3940  1969 62.375   4611
[21,] 62.4250   166 62.355   5111
[22,] 62.4505  2000 62.335   1969
[23,] 62.4700   259 62.315    500
[24,] 62.4755    50 62.250   5142
[25,] 62.4800   166 62.165    660
[26,] 62.4935   305 62.115   2428
[27,] 62.4975  7786 62.085    779
[28,] 62.4995 50049 62.050  12811
[29,] 62.5045   914 62.015    192
[30,] 62.5150  1110 61.975   1200
[31,] 62.5285   400 61.895  40000
[32,] 62.5500  6352 61.835    100
[33,] 62.5750     9 61.775    133
[34,] 62.6000   394 61.750   7723

For the simpliest case, if the King had commanded that the minimum profit 
should be 2.3742%, which is equal to 63.170/61.7050 (look at the first row), 
then you can easily project that 190 quantity of goods will be dealt (the 
minmum of [1,2] and [1,4]), and that the actual profit is 2.3742%.

If the king, however, has commanded that a deal should only be carried out 
if the profit is higher than 2%, the calculation will be more complicated. I 
don't know the right method, but I can demonstrate the wrong method and 
explain why it is wrong.

The wrong approach is the following:

The idea is to write a function that asks how much volume (total quantity) 
you want to deal, and returns the profit. This generates a relationship 
between volume and profit, and with interpolation you can get the volumen 
for any given minimum-profit requirement.


revenues <- function(open_orders, volumes) {
# calculate revenue using a list of open orders and desirable
"volumes" of goods

# expecting volumnes as a vector, to test the revenue (total amont of money)
# for each volume (total amount of goods to deal) in the 'volumes'

 	volume  <- sapply(1:length(open_orders[2,]),
 		function(x) { sum(open_orders[2,1:x])})
 	revenue <- sapply(1:length(open_orders[2,]),
 		function(x) { sum(open_orders[1, 1:x] * open_orders[2,1:x])})
 	i <- findInterval(volumes, c(0, volume))
 	c(0, revenue)[i] + c(open_orders[1,], 0)[i]*(
 		volumes - c(0, volume)[i])
}

data.frame(volume = volumes, profit = revenues(to_sell_to, volumes) /
 	                              revenues(to_buy_from, volumes) - 1)

With the above routine, let us test the profit with the following volumes:
> volumes = c(10, 100, 500, 1000, 5000, 10000, 30000, 50000, 70000, 90000)
And the result:
> data.frame(volume = volumes, profit = revenues(to_sell_to, volumes) /
+                                       revenues(to_buy_from, volumes) - 1)
    volume      profit
    1      10 0.023741938
    2     100 0.023741938
    3     500 0.022424508
    4    1000 0.020974612
    5    5000 0.018972785
    6   10000 0.018087976
    7   30000 0.012223652
    8   50000 0.009288480
    9   70000 0.007729286
    10  90000 0.006204251

So, by looking up the table, if the king requires minimum profit of 2%, the 
volume (total quantity) of goods being dealt should be a bit more than 1000. 
This answer is inexact, but our French King should get by with it. After 
all, he remembers nothing more than the number of digits.

Now let's look at why it is wrong. This answer is, actually, correct, but 
the method won't hold.

Suppose our greedy King asks what volume should be deal if he requires 
ANY deal should be done as long as there is a tiny bit of profit to be made, 
then, according to our lookup-table, we need to deal 90000 volume of goods, 
which is about all the goods you can buy from the market (look at the 
to_buy_from vector, the sum of all goods is some 90000). Now look at the 
bottom rows:
> cbind(t(to_buy_from), t(to_sell_to))
          [,1]  [,2]   [,3]   [,4]
  [1,] 61.7050   190 63.170   2500
  [2,] 61.7500    29 63.150    799
  [3,] 61.8050   166 63.110    500
...
[31,] 62.5285   400 61.895  40000
[32,] 62.5500  6352 61.835    100
[33,] 62.5750     9 61.775    133
[34,] 62.6000   394 61.750   7723

It suggests that if you buys up the whole market, the last a few hands of 
deals are perhaps not profiting at all - they are losing money - since the 
price you buy may be higher than the price you sell - consider for example 
buying at 62.6000 and selling at 61.750. This can be verified.?

So what is the right approach? I don't know. I exhused my brain. Perhaps you
can shed some lights.

And in case you wonder why I do the calculation in R: that's because I am 
studying hundreds of markets, having calculation in one place and statistics 
in another is not convenient, besides noone said R isn't good for 
calculation.

--

? To verify:

   When you buy up the whole market, and sell all goods, the worse price you
   sell at will be 62.525, at [15,3], the point where the market-to-sell-in
   volume reaches that of the market-to-buy-from, this is lower than the
   price you buy at 62.6000 ([34,1]), indicating that you are, indeed, in
   this last hand of deal, buying at a higher price than you sell it. The
   lose is small, but can be much wore with a different set of data.
-------------- next part --------------
to_sell_to <-
structure(c(63.1699981689453, 2500, 63.1500015258789, 799, 63.1100006103516, 
500, 63.060001373291, 10000, 63.0200004577637, 7840, 62.9949989318848, 
2000, 62.9300003051758, 2000, 62.8600006103516, 10811, 62.7799987792969, 
18054, 62.7550010681152, 9000, 62.689998626709, 10960, 62.6349983215332, 
100, 62.5849990844727, 2380, 62.5499992370605, 2119, 62.5250015258789, 
108091, 62.5050010681152, 2000, 62.4850006103516, 816, 62.435001373291, 
600, 62.4000015258789, 300, 62.375, 4611, 62.3549995422363, 5111, 
62.3349990844727, 1969, 62.314998626709, 500, 62.25, 5142, 62.1650009155273, 
660, 62.1150016784668, 2428, 62.0849990844727, 779, 62.0499992370605, 
12811, 62.0149993896484, 192, 61.9749984741211, 1200, 61.8950004577637, 
40000, 61.8349990844727, 100, 61.7750015258789, 133, 61.75, 7723
), .Dim = c(2L, 34L))

On Sat, 14 Sep 2013, Zhang Weiwu wrote:
> I own a lot to the folks on r-help list, especially arun who answered 
> every of my question and was never wrong. I am disinclined to once again 
> ask this question, since it is more arithmatic than technical. But, having 
> worked 2 days on it, I realized my brain is just not juicy enough....
>
> Here is the problem.
>
> 	Trust not for freedom to the Franks---
> 	They have a king who buys and sells.
> 			- Lord Byron: The Isles of Greece
>
> Suppose the French King commands you to buy and sell, and tells you only to
> deal if the profit is higher than 2%. Question: how much quantity will be 
> dealt, and what is the actual profit? In fact, the King wants to see the 
> relationship between his minimum-profit requirement and your result, in
order
> to better his decision.
>
> Let's look at the input data - a dump of which is attached to this
mail.
>
> Column 1 is the price of the market where you buy goods from, column 2 is
the
> quantity of goods that is being sold at that price.
>
> Column 3 is the price of the market where you sell goods to, column 4 is
the
> quantity the buyers willing to buy at that price.
Forgive my carelessness. I should emphasize that there is only one type 
goods to be dealt. The below table should be read in this way: there is 190 
quantity of goods being sold at 61.7050 that you can buy from, and 29 
quantity of goods beign sold at 61.7500 that you can buy from (row 1 and 2, 
column 1 and 2).  They are exactly the same type of goods, that you can sell 
the total volume of 219 at the price of 63.170.
>> cbind(t(to_buy_from), t(to_sell_to))
>
> 	 [,1]  [,2]   [,3]   [,4]
> [1,] 61.7050   190 63.170   2500
> [2,] 61.7500    29 63.150    799
> [3,] 61.8050   166 63.110    500
> [4,] 61.8950   166 63.060  10000
> [5,] 61.9450   166 63.020   7840
> [6,] 61.9805  6150 62.995   2000
> [7,] 62.0000  3069 62.930   2000
> [8,] 62.0600   166 62.860  10811
> [9,] 62.1100   166 62.780  18054
> [10,] 62.1450   166 62.755   9000
> [11,] 62.1750   166 62.690  10960
> [12,] 62.2250   166 62.635    100
> [13,] 62.2450   166 62.585   2380
> [14,] 62.2720   100 62.550   2119
> [15,] 62.2830  4000 62.525 108091
> [16,] 62.2875   100 62.505   2000
> [17,] 62.2955   100 62.485    816
> [18,] 62.3250   307 62.435    600
> [19,] 62.3800  2906 62.400    300
> [20,] 62.3940  1969 62.375   4611
> [21,] 62.4250   166 62.355   5111
> [22,] 62.4505  2000 62.335   1969
> [23,] 62.4700   259 62.315    500
> [24,] 62.4755    50 62.250   5142
> [25,] 62.4800   166 62.165    660
> [26,] 62.4935   305 62.115   2428
> [27,] 62.4975  7786 62.085    779
> [28,] 62.4995 50049 62.050  12811
> [29,] 62.5045   914 62.015    192
> [30,] 62.5150  1110 61.975   1200
> [31,] 62.5285   400 61.895  40000
> [32,] 62.5500  6352 61.835    100
> [33,] 62.5750     9 61.775    133
> [34,] 62.6000   394 61.750   7723
>
> For the simpliest case, if the King had commanded that the minimum profit 
> should be 2.3742%, which is equal to 63.170/61.7050 (look at the first
row),
> then you can easily project that 190 quantity of goods will be dealt (the 
> minmum of [1,2] and [1,4]), and that the actual profit is 2.3742%.
>
> If the king, however, has commanded that a deal should only be carried out
if
> the profit is higher than 2%, the calculation will be more complicated. I 
> don't know the right method, but I can demonstrate the wrong method and
> explain why it is wrong.
>
> The wrong approach is the following:
>
> The idea is to write a function that asks how much volume (total quantity) 
> you want to deal, and returns the profit. This generates a relationship 
> between volume and profit, and with interpolation you can get the volumen
for
> any given minimum-profit requirement.
>
>
> revenues <- function(open_orders, volumes) {
> # calculate revenue using a list of open orders and desirable
"volumes" of
> goods
>
> # expecting volumnes as a vector, to test the revenue (total amont of
money)
> # for each volume (total amount of goods to deal) in the 'volumes'
>
> 	volume  <- sapply(1:length(open_orders[2,]),
> 		function(x) { sum(open_orders[2,1:x])})
> 	revenue <- sapply(1:length(open_orders[2,]),
> 		function(x) { sum(open_orders[1, 1:x] * open_orders[2,1:x])})
> 	i <- findInterval(volumes, c(0, volume))
> 	c(0, revenue)[i] + c(open_orders[1,], 0)[i]*(
> 		volumes - c(0, volume)[i])
> }
>
> data.frame(volume = volumes, profit = revenues(to_sell_to, volumes) /
> 	                              revenues(to_buy_from, volumes) - 1)
>
> With the above routine, let us test the profit with the following volumes:
>
>> volumes = c(10, 100, 500, 1000, 5000, 10000, 30000, 50000, 70000,
90000)
>
> And the result:
>
>> data.frame(volume = volumes, profit = revenues(to_sell_to, volumes) /
> +                                       revenues(to_buy_from, volumes) - 1)
>   volume      profit
>   1      10 0.023741938
>   2     100 0.023741938
>   3     500 0.022424508
>   4    1000 0.020974612
>   5    5000 0.018972785
>   6   10000 0.018087976
>   7   30000 0.012223652
>   8   50000 0.009288480
>   9   70000 0.007729286
>   10  90000 0.006204251

Your problem is very easy to solve, it fits well a first-lesson programming
introduction course. The reason you wasted 2 days, is because you try to do it
like R, while this problem is best solved in C.




In fact, I think this problem demonstrated why R still maintain all the C
features like for/if/while -- because there are problems that should not be
solved in matrix thinking.




You want to know the "sales volme" in relation to the "minimum
profit of each hand of deals". Here is the code and here is the result in
two columnes




> get_volume_by_minimum_profit_rate_per_hand_of_deal <- function(A, B,
profit) {
+     i = 1

+     j = 1

+     while (A[1,i] / B[1,j] > profit + 1) {

+         # step by step find the suitable volume

+         if (sum(A[2, 1:i]) > sum(B[2, 1:j])) { 

+             j = j + 1

+         } else {

+             i = i + 1

+         }

+     }

+     min(sum(A[2, 1:i]), sum(B[2, 1:j]))

+ }
> 
> for (i in seq(0.01, 0.023, 0.001)) {
+ print(c(paste(i*100, "%"),
get_volume_by_minimum_profit_rate_per_hand_of_deal(to_sell_to, to_buy_from, i)))

+ }

[1] "1 %"   "20580"

[1] "1.1 %" "18445"

[1] "1.2 %" "15032"

[1] "1.3 %" "11032"

[1] "1.4 %" "10766"

[1] "1.5 %" "10434"

[1] "1.6 %" "10268"

[1] "1.7 %" "10102"

[1] "1.8 %" "6867" 

[1] "1.9 %" "3299" 

[1] "2 %" "717"

[1] "2.1 %" "551"  

[1] "2.2 %" "551"  

[1] "2.3 %" "219"  



See how this corrects your calculation? You calculated that "if the king
requires minimum profit of 2%, the volume (total quantity) of goods being dealt
should be a bit more than 1000", but look at your data, that means you
dealet five hands to buy, and one hand to sell -- the 5th hand you buy is at the
price of 61.9450, the one hand you sell is at the price of 63.170


(63.17/61.945 - 1) = 19.776%


so the last hand you buy doesn't quanlify minimum requirement. My
calculation is right, with the minimum requirement of 2% profit, the volume you
can deal is only 717.






	[[alternative HTML version deleted]]