Macro to Match buys and sells

[pjw23]

I'm looking for some help writing a Macro that will match buys and sells using the FIFO method (First in First Out)

Specifically, what I'm looking for is a macro that will take a raw data sample and cronologically (Top down) match each buy and sell of a specific product in order to make basic calculations. I have been messing with this on and off for a couple of months and cannot figure out how to make it work.


So as an example,

RAW DATA SAMPLE WOULD LOOK LIKE THIS:
-------------------------------------
Date________Action______Item______Amount______Price______Fees
4/10/2012___Buy________XYZ_______100_________$47.09_____$1.07
4/11/2012___Sell________XYZ_______100_________$47.52_____$1.07

NEW DATA SET CREATED BY RUNNING MACRO WOULD LOOK LIKE THIS:
-------------------------------------
Item______SellDate______Amount______Cost______Proceeds______Total Fees
XYZ_______4/11/12______100_________$4709.00__$4752.00______$2.14


Any help with this is greatly appreciated. Thank you!

[Richard Buttrey]

Will the sell quantity always be the same quantity as the buy? i.e. are they equally matched or is there a necessity to carry forward a balance if the sell is less than the buy?

[pjw23]

Will the sell quantity always be the same quantity as the buy? i.e. are they equally matched or is there a necessity to carry forward a balance if the sell is less than the buy?

No, the buys and sells are not always equal...Sometimes the balance will need to carry forward....Thanks for any help you can provide.

[pjw23]

So, For example:
Step 1 - Match Buys and Sells
If the amounts are an exact match close both transactions (Zero Out) and goto Step 2
If the amounts are NOT an exact match close a portion of the Buy and carry forward the extra inventory for future Sell transactions

Step 2 - Enter Transaction to ledger
Enter Item
Enter Sell Date
Enter Amount Sold
Enter Original Cost of goods (Amt * Price)
Enter the Proceeds from sale (Amt * Price)
Enter the Sum of both tranaction fees