Commodity Trading? Solver maybe....

Hello all,

I have a spreadsheet to help me work out what commodities I should buy
to sell on at a profit.

In the system / world I'm dealing with you can order batches of
products to be made, and then sell them off individually, e.g. a pallet
of bricks at =A3100 contains 500 bricks, which you could sell at =A31
each, resulting in a profit of =A3400 - as long as the market can take
500 bricks...

All well and good. I tell my spreadsheet there is a market for 1000
bricks, and it tells me to buy 2 batches and I'll make a profit of
=A3800 - thank you spread sheet :-)

Now.... there are not only bricks, there are all kinds of commodities
available, some with more profit available than others, with different
market saturation points (is that the phrase?), different batch sizes
and so on and so forth, which brings me to my question.

Is there a way, possibly using 'solver' to say to my spreadsheet 'I
have =A310,000 here - what should I buy with that to make the most
profit, and what would that profit be?'??

Ideally I would like to add another column to my sheet, put in my
budget at the top and it tells me how many batches of each item I
should buy (e.g. 2 bricks, 1 carpet tiles, 7 lots of doors).

I'm not really sure what I should be searching for, so any comments at
all would be helpful!

Many thanks,

Amy

bonjella wrote:
> Hello all,
>
> I have a spreadsheet to help me work out what commodities I should buy
> to sell on at a profit.
>
> In the system / world I'm dealing with you can order batches of
> products to be made, and then sell them off individually, e.g. a pallet
> of bricks at £100 contains 500 bricks, which you could sell at £1
> each, resulting in a profit of £400 - as long as the market can take
> 500 bricks...
>
> All well and good. I tell my spreadsheet there is a market for 1000
> bricks, and it tells me to buy 2 batches and I'll make a profit of
> £800 - thank you spread sheet :-)
>
> Now.... there are not only bricks, there are all kinds of commodities
> available, some with more profit available than others, with different
> market saturation points (is that the phrase?), different batch sizes
> and so on and so forth, which brings me to my question.
>
> Is there a way, possibly using 'solver' to say to my spreadsheet 'I
> have £10,000 here - what should I buy with that to make the most
> profit, and what would that profit be?'??
>
> Ideally I would like to add another column to my sheet, put in my
> budget at the top and it tells me how many batches of each item I
> should buy (e.g. 2 bricks, 1 carpet tiles, 7 lots of doors).
>
> I'm not really sure what I should be searching for, so any comments at
> all would be helpful!
>
> Many thanks,
>
> Amy
>
-------------------------

You're getting into something a lot more complicated than just "Solver".
To answer your question directly, you need to create a formula that is
an "evaluation function". Something that allows you to have many input
variables (how many of what to buy) and a single output (your profit).
Then Solver will be happy to maximize that evaluation function by
manipulating the variables -- probably. Some big hairy trading problems
don't work well in Solver and you need to resort to random Monte Carlo
methods instead which you can also do in Excel.

That said, you still need to be careful. I'm not sure what it is you're
trying to do with commodities, but... It's rather easy to come up with
a stock trading algorithm that makes tons of money on past data, but
which performs miserably in the future. It's a problem of over
optimizing (or suboptimizing) your algorithm. You also need to look at
how your algorithm would have performed in the past with some variation
of parameters and other things to make sure it isn't overly sensitive to
random variations. You've simply got to remember that if it were as
simple as plugging some data about bricks into solver all the MBAs on
Wall Street (or brick yard employees) would have been doing it years ago.

Good luck...

Bill

Your problem sounds somewhat like the classic "product mix" problem taught in
basic linear programming courses.

Solver can do it. There's brief tutorial here:

http://www.solver.com/

You should also have an Excel file with Solver example on your PC called:

SolvSamp.xls

I'm using WinXP / Office 2003 and I found it here:

C:\Program Files\Microsoft Office\OFFICE11\SAMPLES

I'd also see what's availlable from Microsoft on-line. For example:

http://office.microsoft.com/en-us/as...245951033.aspx

HTH
Jason
Atlanta, GA




"bonjella" wrote:

> Hello all,
>
> I have a spreadsheet to help me work out what commodities I should buy
> to sell on at a profit.
>
> In the system / world I'm dealing with you can order batches of
> products to be made, and then sell them off individually, e.g. a pallet
> of bricks at £100 contains 500 bricks, which you could sell at £1
> each, resulting in a profit of £400 - as long as the market can take
> 500 bricks...
>
> All well and good. I tell my spreadsheet there is a market for 1000
> bricks, and it tells me to buy 2 batches and I'll make a profit of
> £800 - thank you spread sheet :-)
>
> Now.... there are not only bricks, there are all kinds of commodities
> available, some with more profit available than others, with different
> market saturation points (is that the phrase?), different batch sizes
> and so on and so forth, which brings me to my question.
>
> Is there a way, possibly using 'solver' to say to my spreadsheet 'I
> have £10,000 here - what should I buy with that to make the most
> profit, and what would that profit be?'??
>
> Ideally I would like to add another column to my sheet, put in my
> budget at the top and it tells me how many batches of each item I
> should buy (e.g. 2 bricks, 1 carpet tiles, 7 lots of doors).
>
> I'm not really sure what I should be searching for, so any comments at
> all would be helpful!
>
> Many thanks,
>
> Amy
>
>

Oo, thanks chaps - that's certainly send me in the right direction.

I know that real life is not ordered enough to be modled on a
spreadsheet, but it gives me a place to start.

Right, off to get my teeth into the Solver, and then investigate
evaluation functions.... what an exciting evening I have planned ;-)

Amy - who know's she'll love ever minute of it