Refer to Excel workbook by path, based on cell value data

[cornfedtm]

Hi everyone. New to the forum. Thanks in advance for your assistance.

I have stock price data downloaded in individual .xls files titled by Symbol (just 2 columns - Date & Close). I would like to be able to INDEX MATCH or VLOOKUP closing prices on a certain date using a file path, with the path capable of being adapted when I type in a new symbol and/or date. I have attached the layout I had in mind. I was able to use an index formula to grab the data, however with the enormous amount of stocks/mutual funds out there, it seems improbable to type the file name into every formula. If someone feels that vba would be more productive, I'm open to it (although my experience is limited). Thanks again.

Stockdata.xlsx

[alansidman]

This may be an easier solution. The following UDF will give you stock price for a stock and a particular date. No need to store that data yourself when you can get the data as needed from the internet.

The syntax is =StockQuote(Range for Symbol, Range for date), ie. = StockQuote(A1,B1) where A1 contains the symbol and B1 contains the date.

I did not develop this code, but do not remember where I got it. I use it regularly.

Please Login or Register  to view this content.

[cornfedtm]

Thanks for the quick response. I will admit that VBA is not something I've spent much time with. I've copied the code to Sheet 1 tab (selected View Code). Then entered a stock symbol in A1 and a date in B1 and entered the formula in B2. It resulted in the #Name? error. Any suggestions for a rookie?

[Tony Valko]

Sounds like you put the code in the wrong location.

It should go into a general module. One way to get there:

Right click the sheet tab>View Code

Goto the Insert menu>Module

[cornfedtm]

Got it working. This is exactly what I was looking for. Thanks much.

[Tony Valko]

You're welcome. We appreciate the feedback!

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.