I have an Access Table with the stock price information for last 1 years worth of data...
DATE, STOCK_SYMBOL, CLOSING_PRICE, CALCULATEDFIELD1. CALCULATEDFIELD2, ETC
I am looking to create a Function within Excel that allows me to pull Access data into Excel. So i would like to enter a UserDefinedFunction formula in Excel and it queries Access for a specific value based on the function I use and symbol I provide.
=HighestHigh10(a5). Assume a Symbol in Range("a5"). It would find the "HighestHigh10" Column, look for "AAPL" (text in [A5] and return the value into Excel. (HighestHigh10 is a calculated field that calculates the Highest high for the last 10-days)
OR a more ideal scenario... It would calculate the HighestHigh based on the days you provide, =HighestHigh("AAPL", 10). It would calculate the HighesHigh in past 10 days and return that price back to Excel, or any calculation I create...
Just wondering if this is easy to do or difficult, and if you could point me in the right direction. I know a query from Excel would pull in sets of data based on filters or criterias. But could I be able to write a function like example above, would it be able to do some calculations and return a single data point? Any links or descriptions would be appreciated...
this is what I am trying to build but with my own calculated fields, I already have the data in Access. XLQ
-Jonathon
DATE, STOCK_SYMBOL, CLOSING_PRICE, CALCULATEDFIELD1. CALCULATEDFIELD2, ETC
I am looking to create a Function within Excel that allows me to pull Access data into Excel. So i would like to enter a UserDefinedFunction formula in Excel and it queries Access for a specific value based on the function I use and symbol I provide.
=HighestHigh10(a5). Assume a Symbol in Range("a5"). It would find the "HighestHigh10" Column, look for "AAPL" (text in [A5] and return the value into Excel. (HighestHigh10 is a calculated field that calculates the Highest high for the last 10-days)
OR a more ideal scenario... It would calculate the HighestHigh based on the days you provide, =HighestHigh("AAPL", 10). It would calculate the HighesHigh in past 10 days and return that price back to Excel, or any calculation I create...
Just wondering if this is easy to do or difficult, and if you could point me in the right direction. I know a query from Excel would pull in sets of data based on filters or criterias. But could I be able to write a function like example above, would it be able to do some calculations and return a single data point? Any links or descriptions would be appreciated...
this is what I am trying to build but with my own calculated fields, I already have the data in Access. XLQ
-Jonathon
