Re: Share calculation in VBA?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Tom Ogilvy (twogilvy_at_msn.com)
Date: 12/14/04 

Date: Tue, 14 Dec 2004 07:50:15 -0500

That's the logic. You would either have to record the results on the sheet
or always work from the first buy/sell all the way down each time you ran
it. 

-- 
Regards,
Tom Ogilvy
"NorTor" <toa@nospam_subnett.no> wrote in message
news:9k9tr0140c1gbl8o9c4gvbv7gvglsk6891@4ax.com...
> Dear David!
>
> Thank you for your two replys; no, I have not yet solved it, I need to
> solve it though :-)
>
> My purpose is to put back the corresponding cost in a column in the
> sell sheet (table). This again, will be used for calculating the
> profit / loss for all sales transactions. There will only be one sell
> pr day here, so the only reason for the date column in both the sell
> and buy sheets (tables) is to be able to be sort the transactions
> historically, of course so that the FIFO principle can be used on the
> correct transactions.
>
> I am not sure, but the only feasible way I can think of, is a do until
> loop in the sell sheet, with a do until loop inside the buy sheet, to
> test and subtract the quantities from the first sell compared to the
> first buy, if the sell is larger in quantity, going to the second buy
> transaction, continuing this to the n buy transaction. Inside this
> there must be some calculation that adds buy cost for all quantity
> that adds up to the sold quantity. And also there must be some other
> logic if the sell is smaller than the first buy, making sure the
> remains of the first buy then is used to calculate the buy of the next
> sell transaction.
>
> This explanation might just contribute to confuse, but it is kind of
> hard to explain... hope it helped though :-)
>
>
> Cheers,
> -NorTor
>
>
>
>
> On Tue, 14 Dec 2004 00:25:01 -0800, "David"
> <David@discussions.microsoft.com> wrote:
>
> >Hi Nor,
> >One more question, what do you want to do with this FIFO-Avgeraged Cost.
> >when you get it? This is the average cost for the whole days Sales or was
> >your intent to bring it back to the Sell sheet and put it next to each
sell,
> >assuming you can have more than one Sell in a day?
> >
> >"David" wrote:
> >
> >> Hi Nor, Have you solved this problem yet? I am just curious. I would
work on
> >> this, but maybe you are done?
> >>
> >> "NorTor" wrote:
> >>
> >> > Great; I hope you will find a way... :-)
> >> >
> >> >
> >> > I have tried for very long now, and I feel that I am *almost* there,
> >> > using do until loops in both tables (sheets), subracting sales from
> >> > the buy if it is smaller than the buy (talking of quantities), and
> >> > "emptying" the buy going to the next if it is bigger than the buy /
> >> > remaining of the buy.
> >> >
> >> > Problem I got stuck in, was when the sell was bigger than two buys
> >> > together (or more than two buys), how to build the calculation for
all
> >> > these buys, in theory n buys.
> >> >
> >> > Hope to hear from you, thank you so much!
> >> >
> >> >
> >> >
> >> > Cheers,
> >> > -NorTor-
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > On Mon, 13 Dec 2004 16:02:22 -0500, "Michael Malinsky"
> >> > <joeqcpa@yahoo.com> wrote:
> >> >
> >> > >I'm sure you know the principle, but when you intermixed the two, it
was
> >> > >difficult to determine which you really meant as the code used to
calculate
> >> > >the gain/loss would be different.
> >> > >
> >> > >Let me chew on this one for a while.
> >> > >
> >> > >"NorTor" <toa@nospam_subnett.no> wrote in message
> >> > >news:11qrr0thomb86budmpg4f7q5bft9m5cl3k@4ax.com...
> >> > >> Hello Michael, thank you for your reply.
> >> > >>
> >> > >> I mean the FIFO, and my example is only to show that the cost
price
> >> > >> (following the FIFO), must be 'weighted together' by the quantity
of
> >> > >> the buys that together is the quantity of the sale.
> >> > >>
> >> > >> But the principle is not the problem, I know that very well, but
how
> >> > >> to program this to be completely dynamic in Excel VBA.
> >> > >>
> >> > >> Again thank you for your reply.
> >> > >>
> >> > >>
> >> > >> Regards
> >> > >> NorTor
> >> > >>
> >> > >>
> >> > >>
> >> > >> On Mon, 13 Dec 2004 13:13:55 -0500, "Michael Malinsky"
> >> > >> <joeqcpa@yahoo.com> wrote:
> >> > >>
> >> > >> >You are intermixing the FIFO and weighted average methods in your
post
> >> > >but
> >> > >> >it can only be one or the other.  If you want to use the FIFO
method, the
> >> > >> >gain on your first sale would be calculated as:
> >> > >> >
> >> > >> >5000 * (11.40-10.50) +
> >> > >> >2000 * (11.40-11.20)
> >> > >> >
> >> > >> >If you want to use weighted average, you would calculate a
weighted
> >> > >average
> >> > >> >of the total holdings of a particular item and use that to
calcluate the
> >> > >> >gain/loss on the sale.  The weighted average would then have to
be
> >> > >adjusted
> >> > >> >whenever you buy/sell that item.  Some clarification on this
might help.
> >> > >> >
> >> > >> >"NorTor" <toa@nospam_subnett.no> wrote in message
> >> > >> >news:i0krr0hpbh8k9bbfdf6kt2fq6a4i23i641@4ax.com...
> >> > >> >> Hello everybody!
> >> > >> >>
> >> > >> >>
> >> > >> >> I am stuck in how to solve the following problem in Excel VBA.
> >> > >> >>
> >> > >> >> Here is the situation;
> >> > >> >>
> >> > >> >> I have two tables, the first one contains all the 'buy'
transactions
> >> > >> >> of one particular financial share. Every row of the table is
one
> >> > >> >> buy-transaction of the share. The columns gives info on
buy-date,
> >> > >> >> number of shares bought, price per share, and total cost.
> >> > >> >>
> >> > >> >> The second table contains the same info, but with the 'sales'
of the
> >> > >> >> same particular stock. (Same here, e.g. every row is one
transaction,
> >> > >> >> the columns of every row containing the same information as for
the
> >> > >> >> 'buy' table.
> >> > >> >>
> >> > >> >> Now here is my challenge; I need to find a way to calculate a
weighted
> >> > >> >> average of the buy costs related to every 'sales' row... this
is to be
> >> > >> >> able to calculate profit / loss for every transaction (row) in
the
> >> > >> >> 'sales' table. In other words, calculation of profit / loss
should be
> >> > >> >> according to the FIFO (First In First Out) principle.
> >> > >> >>
> >> > >> >> This is easy of course if there is one sale for every buy, e.g.
a buy
> >> > >> >> of 1000 shares is followed by a sale of 1000 share; then the
buy price
> >> > >> >> of that sale would be the exact price of the corresponding buy.
> >> > >> >>
> >> > >> >> But in fact the quantities of the 'sale' transaction is totally
> >> > >> >> independent of the quantities of the 'buy' transactions, the
only
> >> > >> >> restriction is that there at all times can be no larger sale
quantity
> >> > >> >> then the stock of shares in total (no short-selling allowed).
> >> > >> >>
> >> > >> >> So for instance the buys can be like:
> >> > >> >>
> >> > >> >> Date Qty. Share price
> >> > >> >> 01.01.02 5,000 10.50
> >> > >> >> 04.01.02 8,000 11.20
> >> > >> >> 09.10.03 4,000 11.40
> >> > >> >> 03.08.04 9,500 11.20
> >> > >> >>
> >> > >> >> And the sales can be like:
> >> > >> >> 05.08.03   7,000 11.40
> >> > >> >> 10.09.04 19,500 11.60
> >> > >> >>
> >> > >> >> In this example the cost price for the first sale should be
calculated
> >> > >> >> as follows:
> >> > >> >>
> >> > >> >> ((5000 / 7000) * 10.50 + (2000 / 7000) * 11.20) = 10.70
> >> > >> >>
> >> > >> >> Reason: Sale of 7,000 "eats" first buy of 5,000 + 2,000 of the
next
> >> > >> >> buy (of 8,000) and corresponding cost price for this sale is
hereby
> >> > >> >> the weighted average of these two factors.
> >> > >> >>
> >> > >> >> The second sale, the corresponding cost is:
> >> > >> >> ((6000 / 19500) * 11.20) + (4000 / 19,500) * 11.40) +
> >> > >> >> (9500 / 19500) * 11.20)) = 11.241
> >> > >> >>
> >> > >> >> Reason is the same: Sale of 19,500 remaining share "eats" the
> >> > >> >> remaining 6,000 from the second buy, and all other remaining
buys.
> >> > >> >>
> >> > >> >> I need a VBA procedure that can handle these fully flexible buy
/ sell
> >> > >> >> structures, and calculate the weighted buy-average and have
that
> >> > >> >> inserted as a new column in the 'sales' table.
> >> > >> >>
> >> > >> >> Please pretty please help me if you can; I have headaches and
> >> > >> >> nightmares (well, almost ;-)) trying to solve this myself...
> >> > >> >>
> >> > >> >>
> >> > >> >>
> >> > >> >> Best regards
> >> > >> >> -NorTor-
> >> > >> >
> >> > >>
> >> > >
> >> >
> >> >
>

Relevant Pages

  • Re: Share calculation in VBA?
    ... My purpose is to put back the corresponding cost in a column in the ... profit / loss for all sales transactions. ... There will only be one sell ... loop in the sell sheet, with a do until loop inside the buy sheet, to ...
    (microsoft.public.excel.programming)
  • Automatic filling of data
    ... I have a 3 columns of pre-defined data - Name, Cost and Sell. ... of the sheet I want to select from a drop-down list the name from the first ... suggestion of how I might accomplish this? ...
    (microsoft.public.excel.programming)
  • FS: Tempest *complete* & *pristine* manual & drawing set
    ... I'm getting ready to sell my upright Tempest (Pasadena, ... manuals separately, so that anybody anywhere can have a shot at buying ... Sheet of unused coin door stickers (they seem to have receded a bit ...
    (rec.games.video.arcade.collecting)
  • Re: FS: Tempest *complete* & *pristine* manual & drawing set
    ... I'm getting ready to sell my uprightTempest(Pasadena, ... clean & working great & upgraded monitor, in case any locals want to ... Sheet of unused coin door stickers (they seem to have receded a bit ...
    (rec.games.video.arcade.collecting)
  • Re: NEW T2 Hunter Killer Ships!
    ... So there's no reason they should cost 80$+ either..... ... sheet of .177 PETG before saying 50 cents worth of plastic. ... average $7 per ramp, the flaps cost on average $4, the brackets ... Take into account we don't sell all 150 new ramps right away, ...
    (rec.games.pinball)