I have an Access 2007 database with separate tables each a separate stock with an array of prices by date such as:
Table: AAPL
Date Price
12/9/2013 $453
12/9/2013 $451
12/9/2013 $464
12/9/2013 $467
etc.
I have an Excel 2007 spreadsheet which I would like to link to these tables to compare/contrast these stocks and display the results (currently have this data automatically downloaded as .csv files and linked into my Excel spreadsheet, but want to change to Access for the reasons I detail below). I must use Excel as I have everything in Excel already automated, the calculations are intensive/complex/work, and the display charts are highly designed and linked to other documents.
My issue is that there are some dates in which one or more stocks don’t have prices (they didn’t trade while the others did trade). In my comparisons between these stocks I need the dates to match exactly – I can’t compare 12/8/2013 in one name with 12/9/2013 in another.
I have used the Bloomberg database query add-in for Excel which as a nice formula interface that accomplishes what I require: with a simple formula custom to Bloomberg’s database an Excel user can call the price on a specific date -- using ‘=bdq(AAPL,PRICE,12/9/2013)’ as a formula in a cell will populate that cell with that date’s price. Additionally, a user can substitute the date field for a cell reference – i.e. changing the ‘12/9/2013’ to ‘+A5’ in which cell A5 has the date 12/9/2013. This is very useful for my purposes as I can call the same date across multiple stock queries.
Question: is there a way to do this using Access 2007? – query a specific date from an Access table in Excel, ideally as a formula or cell-specific code, and have the date stipulated from another column/cell in the spreadsheet?
I am a new member and apologize if this was covered somewhere else (I couldn’t find it) or if I posted this in the incorrect section.
Thank you for your help and advice.
Table: AAPL
Date Price
12/9/2013 $453
12/9/2013 $451
12/9/2013 $464
12/9/2013 $467
etc.
I have an Excel 2007 spreadsheet which I would like to link to these tables to compare/contrast these stocks and display the results (currently have this data automatically downloaded as .csv files and linked into my Excel spreadsheet, but want to change to Access for the reasons I detail below). I must use Excel as I have everything in Excel already automated, the calculations are intensive/complex/work, and the display charts are highly designed and linked to other documents.
My issue is that there are some dates in which one or more stocks don’t have prices (they didn’t trade while the others did trade). In my comparisons between these stocks I need the dates to match exactly – I can’t compare 12/8/2013 in one name with 12/9/2013 in another.
I have used the Bloomberg database query add-in for Excel which as a nice formula interface that accomplishes what I require: with a simple formula custom to Bloomberg’s database an Excel user can call the price on a specific date -- using ‘=bdq(AAPL,PRICE,12/9/2013)’ as a formula in a cell will populate that cell with that date’s price. Additionally, a user can substitute the date field for a cell reference – i.e. changing the ‘12/9/2013’ to ‘+A5’ in which cell A5 has the date 12/9/2013. This is very useful for my purposes as I can call the same date across multiple stock queries.
Question: is there a way to do this using Access 2007? – query a specific date from an Access table in Excel, ideally as a formula or cell-specific code, and have the date stipulated from another column/cell in the spreadsheet?
I am a new member and apologize if this was covered somewhere else (I couldn’t find it) or if I posted this in the incorrect section.
Thank you for your help and advice.
