422,904 Members | 1,025 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,904 IT Pros & Developers. It's quick & easy.

Struggling with a query

PhilOfWalton
Expert 100+
P: 1,183
To cut things to the bone I have 3 tables.
TblBoughtSold which lists the date that stock was purchased or sold and, culpa mia, the balance.

Expand|Select|Wrap|Line Numbers
  1. BoughtSold
  2.     BoughtSoldID    Primary Key Autonumber
  3.     InvestmentID
  4.     BoughtSoldDate
  5.     Balance
  6.  
This lists dividends received
Expand|Select|Wrap|Line Numbers
  1. Dividends
  2.     DividendID    'Primary Key Autonumber
  3.     InvestmentID
  4.     DivdDate
  5.     UKInterest
  6.  
This is the stock name
Expand|Select|Wrap|Line Numbers
  1. Investments
  2.     InvestmentID   'Primary Key Autonumber
  3.     SecurityName   'Name of Investment
  4.  
All three tables have the relationships enforced between the InvestmentIDs

As I said it this is the simplified version.

I am trying to establish the Balance of each item of stock on the date the dividend was paid. That is problem no 1.

The second problem is that stock may have been sold and, due to a delay in the dividend payment, it appears that there was no stock at that Dividend Date.

Given the correct balance I should be able to calculate the dividend rate.

This is as close as I have got, but I should be getting 1 line for every dividend received, and I am only getting about 90% of the lines.

Expand|Select|Wrap|Line Numbers
  1. SELECT Dividends.DividendID, Investments.InvestmentID, 
  2. Investments.SecurityName, Dividends.DivDate, Dividends.UKInterest, 
  3. BoughtSold.DateBoughtSold, BoughtSold.Balance, 
  4. IIf([Balance]>0,([UKInterest])/[Balance]) AS DivRate
  5. FROM (Investments INNER JOIN Dividends 
  6. ON Investments.InvestmentID = Dividends.InvestmentID) INNER JOIN BoughtSold 
  7. ON Investments.InvestmentID = BoughtSold.InvestmentID
  8. WHERE (((BoughtSold.DateBoughtSold)=DMax("DateBoughtSold","BoughtSold","InvestmentID = "
  9.  & [Investments]![InvestmentID] & " AND DateBoughtSold <= #" & [DivDate] & "#")))
  10. ORDER BY Investments.InvestmentID, Dividends.DivDate;
  11.  
Any help would be appreciated

Phil
2 Days Ago #1
Share this Question
Share on Google+
8 Replies


zmbd
Expert Mod 5K+
P: 5,087
just on a lark, would it be advisable to add a field to the [BoughtSold] that would link the record to the dividend paid for that inventory?
Expand|Select|Wrap|Line Numbers
  1.  BoughtSold
  2.      BoughtSoldID    Primary Key Autonumber
  3.      InvestmentID    Foreign Key Investments
  4.      fk_Dividends    Foreign Key Dividends
  5.      BoughtSoldDate
  6.      Balance
would this allow you to at least solve your second problem?
1 Days ago #2

PhilOfWalton
Expert 100+
P: 1,183
Thanks zmbd.

I think you are on the right track, but it needs to be the other way round. The way you have suggested would create a BoughtSold record for each Dividend.

My suggestion is:

Expand|Select|Wrap|Line Numbers
  1. Dividends
  2.     DividendID    'Primary Key Autonumber
  3.     InvestmentID
  4.     DivdDate
  5.     UKInterest
  6.     LastBoughtSoldID     
  7.  
That way the number of records in both tables remains the same, and, if the Balance is zero when the dividend is paid, it should be quite simple to find the previous BoughtSoldID for that Investment.

I will wait to hear whether you agree with my reasoning, before making quite a big change to the Db.

Again, thanks,

Phil
1 Days ago #3

zmbd
Expert Mod 5K+
P: 5,087
Honestly I was guessing here based on a DB that I have tracking some inventory (non-serial numbered consumables).

Maybe if I explain my thought process here it may help you decide how, or even if, my suggestion could work for your DB
[t_supplier]
[t_products]
[t_POnum]
[t_invoice]
[t_inventory]
[t_discount]

- So I was guessing that your [BoughtSold] === [t_invoice].

[t_discount] has the information about the discounts offered by the supplier for quantity discount, special promotions, or other discounts (that gets to be a mess with some of the promotions (@@) )

+ so when I purchase a product [t_invoice].[fk_discount] has the discount for the product purchased, amount, etc... ([t_POnum]->[t_invoice] each line item in the invoice can have a different discount and there are multiple items per purchase order).

So when the items arrive, they get labeled with Date received, who received them and the PO#, [t_inventory] gets a record with the product, amount received (we may order 6 and receive 3 in one go and the other 3 a week later :-( so we can check against [t_invoice].[amtordered] ) , and the po number.

When items are removed [t_inventory] receives a similar entry just the amount received is a negative number

Where this is going is that I can calculate the value of inventory on hand because we may have items from six different PO# that may have different discounts. We can see if the all of items from the oldest PO have been used (some expire!), I can also pull against a supplier over a given time frame an see if we're getting better pricing from them, or compare against another supplier (we multi-source some mission critical items), often over time as some of the product pricing is variable depending on market conditions.

It's one of my earlier databases so it's not very well structured and the SQL is quite the mess (steep learning curve for me then!) but it works and has worked since... 1995ish - originally it was a Lotus-Approach database - V3.0 IIRC-ran on OS Win3.1 in 1994 or 1995 (been too long) - that was fun converting from Approach to Access!
1 Days ago #4

PhilOfWalton
Expert 100+
P: 1,183
Thanks again, zmbd, but I don' think your second post is that relevant.

I obviously didn't make mkyself as clear as I should have done.
When I mentioned stock, I really meant stocks & shares (Investments). So when a dividend is paid for a particular share, I am trying out how many shares my client owned on that payment date. Hopefully if he buys more shares, and assuming the dividend rate is constant, he would expect to get a proportionally bigger dividend.

We need to work backwards. We know the dividend at a specific date, and when we solve the problem, we know how how many shares he owned, so we can calculate the rate.

Don't forget that he buys and sells shares when he thinks he can make a profit, so there is no pattern in the Balance of shared owned.
Dividends are paid on a more or less regular basis, but I emphasise, more or less.

Hope this clarifies the question

Phil
1 Days ago #5

zmbd
Expert Mod 5K+
P: 5,087
Ok, PhilOfWalton
you really are not making much sense here as it sounds as if you are talking about bounds not stocks. If your talking about bonds then my last post makes much more sense and was what I gathered you were talking about from your post and query.

Usually stock dividends are paid out according to the number of shares owned at the time of pay-out:
So if you own 1000 shares on 1900-02-01 and the dividend announced for the quarter is $0.37(us) you would receive $370(US)
if later you sold 500 shares because the market value increased, sold shorts because of bad new, covered with purchases and increased your holdings to 6000 shares and the quarterly dividend was announced and paid on 1900-05-01 was $0.40(US) would receive $2400(US)

so to keep a nice database of this
[t_Company]
[Company_pk][other company fields]

[t_Dividend]
[Dividend_PK][Company_fk][Dividend_date][Dividend_rate]

[t_Transactions]
[Transactions_PK][Company_FK][Transactions_date][Transaction_amt][Transaction_Price]

I don't think you need a relationship between the two tables [t_Dividend] and [t_Transactions] - you only need to know the amount of shares held at the time the dividend was paid...

+ I'll have to think about this a bit and it's quite late, I was at wedding reception... and I have a few Rum-Cola and a few more glasses of wine on-board so I'm feeling a bit warm and fuzzy :-) - and I got to see one of my friends little girls (known her for almost 20 years - hard to believe - time leaves us so quickly) become a wife - one got past her brothers! Hard to think of her being that old, I still see the little teenager in my mind's eye and in my heart.

But I digress
It would be helpful for you to use standard fiduciary nomenclature

+ Stocks - general term for all shares owned, this is like saying I own shoes.

+ Shares - specific term for ownership certificates of a particular company, this is like saying I have a pair of Nike Flex 2017 Mens Running Shoes and six pairs of Adidas Ultraboost Parley Shoes
>> There can be different classes of shares that pay at different rates; however, these have to be carefully setup to avoid running afoul of Birch v Cropper (1889) 14 App Cas 525

+Bonds - Where as shares are an ownership of company, bonds are a means of financing debt of the company for which the company is willing to pay an interest in consideration of being able to use your money (AKA an loan). At maturity the investor usually gets the principal back along with any interest due (usually the interest is paid quarterly, biannually, or yearly depending on the terms of the bond).
>Bonds purchased at different times could have different yields and that creates a much more interesting tracking for dividend/interest earned.

I have more that basic understanding of stocks, bonds, and commodities. I helped my Mom study for her Series-7, and other FINRA required exams for licensing. She ran her own office for many years and worked for some of the major commodities and securities firms for many decades before retiring.
15 Hours Ago #6

PhilOfWalton
Expert 100+
P: 1,183
Ah, the Rum-Cola and wine has made you quite relaxed. Excellent.

You obviously know a lot more about the market than I do, but we are talking English law, not American Law.

OK, the database has entries that go back over 20 years, so it is well established, and works well. So although I have simplified the question, the table names & field names are long established.

The nature if the stocks varies, but that is not relevant. Your second paragraph sums up the situation perfectly, the difference being that on 1st February we received $370, and looking back, we found that we had 1000 shares on that date so we can calculate the rate at 3.7%.
On 4th June we received $2,400 and found that we had held 6000 shares from May 1st, so the rate would be 4%.

I regret holding the Balance of shares (Bought - Sold) in the table. I am always preaching against holding calculated values in a table, but 24 years ago, when this Db was born, I knew even less about Access than I do now. Yes I know that must be hard to believe...

Attached is an Excel example from the database. The black figures are straight from the Db, and the red figures have been put in manually by looking at the balance of shares held prior to the dividend being paid.

We need to find those red values.

Thanks again,

Phil
Attached Files
File Type: xlsx Sample.xlsx (11.7 KB, 3 views)
12 Hours Ago #7

PhilOfWalton
Expert 100+
P: 1,183
Ah,in the end I gave up trying to use a query and had to resort to VBA. Fortunately it runs pretty quickly. Using this, we do not even need to change the table designs.

I suspect my SQL is even worse than my VBA

Here's the code if anyone is interested:-

Expand|Select|Wrap|Line Numbers
  1. Function BalAtDate(InvestmentID As Long, DivDate As Date) As Currency
  2. '? BalAtDate(172, #11/04/06#)
  3.     Dim MyDb As Database
  4.     Dim RstBoughtSold As Recordset
  5.     Dim StrSQL As String
  6.     Dim LastDate As Date
  7.     Dim BalanceAtDate As Currency
  8.     Dim PreviousBalanceAtDate As Currency
  9.     Dim StrDivDate As String
  10.  
  11.     StrSQL = "SELECT BoughtSold.* FROM BoughtSold "
  12.     StrSQL = StrSQL & "WHERE InvestmentID = " & InvestmentID
  13.     StrSQL = StrSQL & " Order By DateBoughtSold;"
  14.  
  15.     Set MyDb = CurrentDb
  16.     Set RstBoughtSold = MyDb.OpenRecordset(StrSQL)
  17.  
  18.     StrDivDate = CStr(DivDate)
  19.     With RstBoughtSold
  20.         Do Until .EOF
  21.             If DDiff("d", CStr(!DateBoughtSold), StrDivDate) >= 0 Then  ' DateDiff only works for US Dates
  22.                 Exit Do
  23.             End If
  24.             LastDate = !DateBoughtSold
  25.             BalanceAtDate = !Balance
  26.             If Nz(!Balance) <> 0 Then
  27.                 BalanceAtDate = !Balance
  28.                 PreviousBalanceAtDate = !Balance
  29.             Else
  30.                 BalanceAtDate = PreviousBalanceAtDate
  31.             End If
  32.             .MoveNext
  33.         Loop
  34.         .Close
  35.         Set RstBoughtSold = Nothing
  36.     End With
  37.  
  38.     BalAtDate = BalanceAtDate
  39.  
  40. End Function
  41.  
I might add that I have to use a UDF DDiff() as DateDiff() works with US dates, but not English dates DD/MM/YYYY.

The DDiff function uses information from the windows profile to get information on the local date format, and hopefully is independent so "knows" how to interpret the date that you use.

Thanks for your input

Kind Regards

Phil
5 Hours Ago #8

NeoPa
Expert Mod 15k+
P: 30,842
PhilOfWalton:
I might add that I have to use a UDF DDiff() as DateDiff() works with US dates, but not English dates DD/MM/YYYY.
DateDiff() works with dates. There are no US or UK dates. There are only US and UK formatted date strings. If you're using date literals in SQL then you should always use a SQL standard date format for them. US dates formats happen to be supported whereas UK date formats certainly are not. Please see Literal DateTimes and Their Delimiters (#). This is not an area where experienced developers from anywhere in Europe should be struggling. Certainly not on Bytes.com where I repeat this point almost weekly.

Moving on. I thought you had a query which produced for you the total stocks/shares held, at a particular date, as well as the dividends paid for those stocks/shares. From there, why is it not simply a case of dividing one by the other for the rate you're after.
3 Hours Ago #9

Post your reply

Sign in to post your reply or Sign up for a free account.