Formula Based on Record Number -Recordset Required? (1 Viewer)

[AJR]

Hi

I have been slowly plodding away at the process of learning Access and VBA.

I have come to the point that I think I will have learn how to use Recordsets and was wondering if someone could confirm this before I embark on the somewhat daunting (to me) task.

In order to arrive at the required solution I have to perform a calculation, using data from each successive table record (in chronological order). The result of each calculation must then be used to arrive at the result for the next calculation.

In other words I have to update a variable based on data from each record, sorted in chronological order, and use the final result to populate a field on a form.

Is it time to break out my copy of "VBA For Dummies" and start learning how to use Recordsets?

Thanks

A/R

 

[CJ_London]

perhaps, perhaps not.

It is perfectly feasible to do calculations based on the previous record in sql, providing you can define previous.

However what you are describing sounds simpler than this. So to answer your question - we need to know more about what you are trying to do. Provide the background to what you want to do, a sample dataset and the required outcome based on that dataset

 

[AJR]

I have to calculate the Adjusted Cost Base (ACB) of a stock, shares of which have been purchased and sold over a period of time. One must also account for a Return of Capital (ROC) which is when the company pays you money without issueing shares.

The ACB is the Average price payed and is used to calculate the profit on shares sold.

Each type of transaction effects the ACB as follows

1)Purchase

#Shares Purchased + # Previously Owned equals Total Number Owned.

Total Cost Of Shares Purchased (# Purchased * Price per Share) +PreviousTotal Cost equals
Total Cost of Shares Owned

ACB equals Total Cost / Total Owned

2) Sale -Price the shares sell for is irrelevant. ACB of the shares sold is used in the calculation

Total Cost of Shares Owned is reduced by (#Sold * ACB)

Total Number of Shares Owned is reduced by the number sold

ACB equals the new Total Cost / New Number Owned


3) Return of Capital

Total number of shares is not effected

Total cost is reduced by the amount of the ROC payment


It would look like the following in a spreadsheet. Sorry but the formatting is difficult

------------#---------------- $-------- Total Cost------ Total Owned --------- ACB

Buy |--- 100----@---- $2.00------ $200.00 ----------- 100----------------- $2.00 | 200/100
Buy | -- 200----@---- $2.50------ $700.00------------- 300---------------- $2.33 | 700/300
Sell |----- 50--- @---- $3.00------ $583.50------------- 250---------------- $2.33 | 583.50/250
ROC---------------- $100.00------ $483.50------------- 250---------------- $1.93 | 483.50/250


I will reiterate on the sell, as I found it confusing at first, The total cost is reduced by the
Number of Shares Sold * the ACB of the shares at the time of the sale i.e., $700.00-(50*$2.33)


Hope I made this clear and thanks a million

 

[essaytee]

It will help if you also provide details of your table design. Following on from this I'm sure many would have suggestions for related queries specific to your needs.

When you refer to Return on Capital, is this the same as a dividend (income received)?

When you refer to Adjusted Cost Base, is this the only way to calculate cost? I mention this as here in Australia we have the option when selling shares to associate those shares to a particular purchase of said shares (known as lots). Can not double dip though. I'm in the process of writing my own accounts program but is currently on the back-burner but I know I have to account for different methods of calculating profit/loss of shares. My point is, design for it, if it's available.

Steve.

 

[CJ_London]

The answer to your test data is relatively easy to determine with the sample data provided - one or more buys followed by a sale with stock remaining. However the sample data is too simple - so before I spend time suggesting a solution for you to then say "but now I have another purchase and another sale and 'it doesn't work'", please provide a more comprehensive 'real life' scenario and the tables set behind it and explain more clearly how often you want to determine the ROC - particularly as the ROC doesn't appear to be a record but simply a derived value - queries will only work on real records, not virtual ones so down to the sell row is straight forward based on a single table, but to show the ROC will need an additional table. One thing you will certainly need is a date field or some other basis of 'ordering' the data - or do the calc in excel where 'before' can be simply the physical row above.

 

[AJR]

Steve

Yes Return of Capital is like a dividend in that it is a payment only and you receive no shares. The difference is that a dividend is income, which is taxable in the year it was earned. A ROC is a partial repayment of what you originally paid for the shares. Thus it is applied to reduce your intial cost. By lowering the cost you increase the Capital Gain when you sell and pay tax on the ROC as Capital Gains Tax. -- Canadian Style Taxation.

Adjusted cost base is the only way to calculate the cost of shares for the purpose of determining Capital Gain in Canada. Sorry for the delay; I'm a little strapped for time.

Can you give me any insight into the best way to convey the table structure?

Thanks a lot for your help. I have done quite a bit of work on this so could possibly provide some info that you may find helpful. Feel free to ask anytime.

A/R

 

[AJR]

CJ

The date is required to determine the order of the transactions, not sure how I managed to omit that--sorry.

Return of Capital does not have to be calculated. It is a lump sum of money, that receives from the company, that is applied against the total cost to date. e.g., if you had paid a total of 1,000 £ for 100 Shares your ACB 1,000/100 = 10£ / Share. If you then received a ROC of 100 £ Your total cost would be 1,000 £ - 100 £ = 900 £ and your ACB would = 900/100 = 9.00£ /Share.

The ACB has to be determined after each transaction and must be capable of dealing with, essentially, an infinite number of transactions, up until the point where the total # of shares equals 0. The scenario I gave encompasses all the possible types of transactions which, after the initial purchase, could happen in any order.

I'm strapped for time here but will put something together to demonstrate the table structure in a day or three. Can you suggest a good way to convey that?

Thanks for the help

A/R

 

[essaytee]

Steve

...

Can you give me any insight into the best way to convey the table structure?

...

A/R

AJR

As I mentioned previously, I'm in the process of writing my own personal accounts program. It's on the back-burner at the moment but I got as far as the schema which I've attached. When I get back to query/form design no doubt the schema will be tweaked.

In relation to your specific needs the table, tbl_Lot_Sell, which hooks of the table, tbl_Post, will not be needed and can be completely dismissed without replacing with any other table.

Hope this helps and I'm open to constructive criticism/suggestions regarding the schema.

Steve.

 

[AJR]

Steve

Will have a browse over this thanks again.

A/R