[chaosboy]

Hi all,

Long time since the last post - but have always found this site to be really helpful - here goes.

I'm looking to create a formula which will recognise the gains on a portfolio, from the beginning of the past week, the beginning of the past month, and from the closing balance of the previous year, ie 31st dec 2005, have been looking on knowledge base at microsoft to no avail, so hoping you may be able to help.

Plus be able to recognise the start of a new year automatically.

Thanks for looking,

CB

[OBP]

CB, are we talking Access or Excel?
I have some code for Access for recognising the new year automatically.
Can I be clear, are you looking for the formulae for the portfolio gains or the way to work out the week, month, year bit?

[chaosboy]

OBP, Thanks for coming back so quickly.

It's to set as a starting date the last working day of the previous year in either access or excel, I'm using both, so if I could find a formula for the start of the year, and then subtract a day or two, would that be possible?
I'm looking for essentially the way to work out the week the month or the year as I need this to populate a field to then go and interrogate an sql database.

Thanks in advance,

CB

[The Villan]

CD
In Excel, there are additional functions for many purposes that can be added to the normal functins available. I am not sure if you have installed these functions in Excel.
If not you should install them. So on the basis that you may not have done that, this would be what to do

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
If necessary, follow the instructions in the setup program.

I am not sure anymore if you need your original software CD's - I think you do these days, if you have a more recent version of Excel.

Once the additional functions have been installed, you can then do the following to see if any of the new functions are of any help.

Click on a blank cell
Click on the fx icon in the formula bar
This will bring up the Insert Function Dialogue Box
Click on the dropdown icon to the right of where it says - or select a category
Click on Date and Time
In the bottom left of the dialogue box click on - Help on this function
You should then get the Microsoft Excel Help screen, which should have all the Date & Time functions listed on the left hand side of your screen.
By clicking on each function, you will get all the help needed for that function.

I have only mentioned this incase you were not aware of the additional Toolpack functions.

If you are, then please ignore my post.

[OBP]

CB, can I be clear on your requirement. Do you want say Access to automatically notify you at the end of the Week, end of the Month and End of Year, put the date in a field and then run a Query to interrogate the SQL database based on the field's value?

[chaosboy]

OBP - Sorry about the lack of clarity, new to using this programme.....

I don't need to be notified, but yes I do need for that date to be put in a field and then for the query to interrogate the sql database.

Villain, thanks for the advice, I've added that pack, which no doubt at some point in the future will be useful!!

Once again - many thanks for looking.

CB