Struggling with a query

[PhilOfWalton]

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.

BoughtSold
    BoughtSoldID    Primary Key Autonumber
    InvestmentID
    BoughtSoldDate
    Balance
 

This lists dividends received

Dividends
    DividendID    'Primary Key Autonumber
    InvestmentID
    DivdDate
    UKInterest
 

This is the stock name

Investments
    InvestmentID   'Primary Key Autonumber
    SecurityName   'Name of Investment
 

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.

SELECT Dividends.DividendID, Investments.InvestmentID, 
Investments.SecurityName, Dividends.DivDate, Dividends.UKInterest, 
BoughtSold.DateBoughtSold, BoughtSold.Balance, 
IIf([Balance]>0,([UKInterest])/[Balance]) AS DivRate
FROM (Investments INNER JOIN Dividends 
ON Investments.InvestmentID = Dividends.InvestmentID) INNER JOIN BoughtSold 
ON Investments.InvestmentID = BoughtSold.InvestmentID
WHERE (((BoughtSold.DateBoughtSold)=DMax("DateBoughtSold","BoughtSold","InvestmentID = "
 & [Investments]![InvestmentID] & " AND DateBoughtSold <= #" & [DivDate] & "#")))
ORDER BY Investments.InvestmentID, Dividends.DivDate;
 

Any help would be appreciated

Phil

[zmbd]

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?

 BoughtSold
     BoughtSoldID    Primary Key Autonumber
     InvestmentID    Foreign Key Investments
     fk_Dividends    Foreign Key Dividends
     BoughtSoldDate
     Balance

would this allow you to at least solve your second problem?

[PhilOfWalton]

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:

Dividends
    DividendID    'Primary Key Autonumber
    InvestmentID
    DivdDate
    UKInterest
    LastBoughtSoldID     
 

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

[zmbd]

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!

[PhilOfWalton]

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

[zmbd]

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.

[PhilOfWalton]

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

[PhilOfWalton]

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:-

Function BalAtDate(InvestmentID As Long, DivDate As Date) As Currency
'? BalAtDate(172, #11/04/06#)
    Dim MyDb As Database
    Dim RstBoughtSold As Recordset
    Dim StrSQL As String
    Dim LastDate As Date
    Dim BalanceAtDate As Currency
    Dim PreviousBalanceAtDate As Currency
    Dim StrDivDate As String
 
    StrSQL = "SELECT BoughtSold.* FROM BoughtSold "
    StrSQL = StrSQL & "WHERE InvestmentID = " & InvestmentID
    StrSQL = StrSQL & " Order By DateBoughtSold;"
 
    Set MyDb = CurrentDb
    Set RstBoughtSold = MyDb.OpenRecordset(StrSQL)
 
    StrDivDate = CStr(DivDate)
    With RstBoughtSold
        Do Until .EOF
            If DDiff("d", CStr(!DateBoughtSold), StrDivDate) >= 0 Then  ' DateDiff only works for US Dates
                Exit Do
            End If
            LastDate = !DateBoughtSold
            BalanceAtDate = !Balance
            If Nz(!Balance) <> 0 Then
                BalanceAtDate = !Balance
                PreviousBalanceAtDate = !Balance
            Else
                BalanceAtDate = PreviousBalanceAtDate
            End If
            .MoveNext
        Loop
        .Close
        Set RstBoughtSold = Nothing
    End With
 
    BalAtDate = BalanceAtDate
 
End Function
 

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

[NeoPa]

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.

[PhilOfWalton]

I have often run into problems with trying to use DateDiff an UK dates as stored in a table (dd/mm/yyyy)

Certainly using the data in the Excel file and

If DateDiff("d", !DateBoughtSold, DivDate >= 0 Then
...
 

interprets the dates as if the were in US format mm/dd/yyyy, and gives the wrong result. So my UDF DDiff seems to give the correct results.

Yes, a simple division is correctly giving me the dividend rate per share.

Bit more fun now as I have a table of share prices recorded at different dates. so my next job is to work out the value of the shares held from the price immediately prior to the dividend date, and see what percentage he is getting.

But that's tomorrow's entertainment

Phil

[NeoPa]

Again, that's simply a misinterpretation of what's going on. Excel can store dates, and it can also store strings that look like dates. Take any date value in a cell and format it differently - it keeps the same date but shows a different string. The underlying content hasn't changed.

Tables don't store dates as UK dates, or as US dates. They store dates or strings. They may have a format property such that when the value's viewed it appears as a string, but that is not the value itself.

If your data is actually a string then you may see such issues. If it isn't then you won't. I say again, dates are stored as dates. They are never stored as the formatted value except when stored as strings, in which case they aren't really dates.

If you think you have an exception then feel free to call me up to discuss it. I don't want you thinking it's so simply because I say it is. I'm confident it is of course. I just want you to understand the issue fully so you know why it is so.

[PhilOfWalton]

I am aware that dates are stored as a floating point decimal number (Double) with the integer part the number of days from 30 Dec 1899 and the decimal part as the fraction of a day (hours, minutes & seconds), so the display of a date in a table depends purely on the region & the format.

However, if I want the days difference between January 7th and January 13th, I type in the mug's way in the immediate window

? datediff("d", #07/01/18#, #13/01/18#)
 

and get -1990

I have to type in

? datediff("d", #01/07/18#, #01/13/18#)
 

to get the correct result of 6.

so given those two dates - January 7th and January 13th, in the form of 07/01/18 and 13/01/18 (apparently) in my table, what is your method of getting DateDiff to work?

Phil

[NeoPa]

That isn't what those two dates represent. I must admit that I wasn't aware that dates in VBA are region independent. I knew that in SQL they are, but look at the same literal date string in the QBE that you enter in SQL, and you'll see it's been regionalised.

The two dates you represent as #07/01/18# and #13/01/18# are actually 1st July and 13th January. If you want them to be recognised correctly you have to provide a region independent string that is unambiguous. #7 Jan 2018# will work, as will #1/7/2018#, or even #2018\-1\-7#.

Although I guess we none of us use actual date literals in VBA very much, I'm very surprised that is also region independent. I didn't know that before today.

[PhilOfWalton]

Actually looking again, it is weirder than I thought.
In the immediate window, the code

? Datediff("d", #07/01/18#, #13/01/18#)
 

giving -1990 (about 5 years difference) implies it is interpreting it as

?Datediff("d",#01 Jul 2018#, #18 Jan 2013#)
 

However

?Datediff("d", #07/01/18#, #11/01/18#)
 

gives 123 which is the same as

?Datediff("d",#01 Jul 2018#, #01 Nov 2018#)
 

ie here it is interpreting it as standard US Dates.

The following SQL gives the correct results:-

SELECT TblDatesTest.*, 
[Date2]-[Date1] AS Subtract,
DateDiff("d",[Date2],[Date1]) AS StdDateDiff, 
DDiff("d",[Date2],[Date1]) AS UDFDDiff, 
#1/7/2018#-#1/13/2018# AS FixedSubtract, 
DateDiff("d",#1/7/2018#,#1/13/2018#) AS FixedStdDateDiff, 
DDiff("d",#1/7/2018#,#1/13/2018#) AS FixedUDFDDiff
FROM TblDatesTest;
 

All highly confusing, especially when you think you have entered the same dates in the immediate window as in your table, and get totally unpredictable results.

Phil

[Rabbit]

Back to the query, the only reasons I can think of for the missing records are either there isn't at least one record in all three tables for each investment ID and/or some of the dividends have no earlier bought sold record.

I would do a distinct count on the dividend id without any joins and then add in a table and filter at a time until I found which caused the records to disappear.

[NeoPa]

PhilOfWalton:
Actually looking again, it is weirder than I thought.

Yeah. Me too.

I never even entered the dates with two-digit years. I've avoided that for a lot longer than two decades wherever possible. It just doesn't make sense to me to offer myself up to unnecessary problems.

So, it does tell us that date literals in the Immediate Pane are treated unexpectedly. IE. VBA date literals are similar in that respect to those used in SQL. The obvious answer is to determine exactly how they're interpreted. That, or use a format that you know is always unambiguous. Dates are tricky in that if a date can be interpreted in UK format as well as US format then it will choose the latter. However, if either is impossible (EG. US #13/1/2018#.) then it will be treated by using the local format. This is great - except it is singularly responsible for most of the date problems people fall over, because it's so hard to identify.

NB. Please don't overlook Rabbit's post on the main issue that came in before mine..

[PhilOfWalton]

@ Rabbit

Thanks for suggestion, but in the end, as the query had to be updateable, I had to resort to the VBA solution.

It's a bit slow, but, by limiting the records to a limited date range, just about acceptable.

Phil