Find the price of a security depending the date

[SebH]

Hi,

I am working on this since few days and many hours without to find how could I do that by myself, even if I am quite sure that it is not so hard to do :-/

The context :

I receive on a daily basis my portfolio from an outside party : A csv file with let's say 5 columns : Date/ Stock name / Price / Quantity / Type

I have created a macro to import these files in ACCESS, all these files in the same table (maybe here is the source of my problem). So my table looks like this :

Date / Stock Name / Price / Quantity / Type
01 Jan / ABC / 1.05 / 200 /Share
01 Jan / XYZ / 2 / 100 / Bond
02 Jan / ABC / 1.06 / 180 / Share
02 Jan / XYZ / 2.1 / 100 / Bond
03 Jan / ABC / 1.04 / 180 / Share
03 Jan / XYZ / 2.15 / 110 / Bond

The query :

I have created a query to analyse these data with :
- 1st column : Stock Name
- 2nd column : Entry Date
- 3rd column : Exit Date
- 4th column : Currency
- 5th column : Entry Price
- 6th column : Exit Price
- 7th column : Performance (= Exit Price / Entry Price - 1)

So, my SQL query is :

SELECT Last(Portfolio.Stock_Name) AS Sec_Name,
Min(Portfolio.[Date]) AS Entry_Date,
Max(Portfolio.[Date]) AS Exit_Date,
Portfolio.Ccy,
First(Portfolio.Price) AS Entry_Price,
Last(Portfolio.Price) AS Exit_Price,
IIf([Entry_Price]=0,0,IIf([Exit_Price]=0,0,[Exit_Price]/[Entry_Price]-1)) AS Perf,
Max([Quantity]) as Quantity_May
FROM Portfolio
WHERE (Portfolio.Type)="Shares")
GROUP BY Portfolio.Ccy, Portfolio.Stock_Name
ORDER BY Last(Portfolio.Stock_Name);

So everything is fine except for the Entry_Price and the Exit_Price. Indeed, the "First" and "Last" function of ACCESS is quite a mess and returns value depending of their creation date in the database.

Thus, I do not know how to do to find a price depending a "Stock Name" and a "Date".

I have tried to work with WHERE, to work with a sub-query, to replicate the table and work with INNER JOIN and Table_2 as portfolio but without figures out it.

As I said, I am quite sure that this kind of work is a basic stuff and a "Must to know how" but everyone had been a beginer with ACCESS :-/

I would appreciate your assistance

 

[SebH]

Hmmm i become a little bit nervous to see how many views have my post without any reply. Does this problem is more complex that I though?

 

[plog]

First and Last should be taken out of MS Access and bludgeoned to death in an alley. They do not do what you (or anyone else) thinks they do.

Also, you have some poorly named fields. You shouldn't have spaces in field names (it makes it harder to write code) and you shouldn't use 'Date' as a name because its a reserved word and makes things confusing.

What you need to do is create a sub-query to get the first and last dates of your prices for each stock. Let's call that subPriceDates the SQL will be this:

SELECT [Stock Name], MIN([Date]) AS FirstDate, MAX([Date]) AS LastDate FROM Portfolio GROUP BY [Stock Name]

With that you can now build your query. You will create a new one using Portfolio and subPriceDates linking them by [Stock Name]. Then to get the earliest price you bring in another instance of Portfolio (it should show up in the design view named like 'Portfolio_1'), link it to subPriceDates via [Stock Name] fields and subPriceDate.FirstDate to Portfolio_1.Date. You then bring down the [Price] date from Portfolio_1 and that's your beginning price.

To get the last price you bring in a third instance of Portfolio (named 'Portfolio_2') and do the same thing you did to get the first price.

 

[SebH]

Hi plog,

Many thanks for your detailes explanation: I succeed to replicate it - you make my day ! Now I need to take time to fully understand how it works to be able to use it in different situations.

You said :

Also, you have some poorly named fields. You shouldn't have spaces in field names (it makes it harder to write code)

And I agree. I write in my explanation : "Stock Name" but I am using "Stock_Name". Does the "_" is enough or do you adice to use "StockName" ?

Also, you said :

Also, you have some poorly named fields. You shouldn't have spaces in field names (it makes it harder to write code)

Agree too. But one question, if I am using the "[Portfolio].[Date]", does the use of the "[" and "]" make it works or do I must use "Pricing_Date" ?

Many thanks for your assistance,
much appreciated.

 

[plog]

You should only use alphanumeric characters and underscores. So 'Stock_Name' is a good name.

Yes, the brackets make the Date field work. In fact, without them, the query wouldn't work. The problem is, you have to remember brackets everytime with that field. I would rename the Date field like you said--Pricing_Date.

Even though they aren't reserved words, I would prefix every generic field name you have with 'Pricing_', e.g. Pricing_Type, Pricing_Quantity, etc.

 

[SebH]

Thanks for your advice, I will try to keep this good practice.

 

[jdraw]

As plog suggested, you should not use Reserved words as field or control names.
By using the [ and ] you have told Access that this is not a reserved word. You could put a [ and ] to delimit every reserved word you use in every context, and every term you use that has embedded spaces or special characters "-!@#$%^&* - etc, or you could do as suggested.

Don't use terms with embedded spaces or special characters or reserved words.

Use only alphanumeric characters and underscore ("_") for field and object names.

Reserved words list

PricingDate , Pricing_Date or whatever makes sense to you, given the above caution, is fine.