Getting sparklines to refresh along with data import of stock prices from web

[Nerpilis]

I'm relatively new to VBA, but have been increasing my exposure over time. Along time ago while first learning about web queries I stumbled upon this code I believe I got from Chip Pearson (per the note left behind). He has a site with a lot of downloads and help available if you care to Google him. Anyway found this code that wasn't useful to me at the time, but saved it and now suddenly I have use for it. Essentially you can have a list of stock symbols/tickers, input a date range, hit the control button and it will retrieve historical stock prices from yahoo finance and place each set of historical price data into a new tab/worksheet and re-title the worksheet with the ticker symbol. I modified the sheet a bit such that I pull data from the tabs and perform other processes with the data download.

What I am stuck with: I'd like to add sparkline cells corresponding to the row/ticker that will refresh each time I download new data. See my attachment and hit the retrieve button, you will notice that the sparklines disappear. I believe this is happening because the VBA code deletes the sheets then creates a new one and labels it with the ticker symbol, however the sheet data that the sparklines were referring to is no longer there (despite being named the same as the previous location). I noticed this when I have the editor project pane open and it displays the 'name' in parentheses , but the sheet numbers change with each successive button click. I'm not clever enough yet to pick apart the wonderful code I lifted; Is there a way that when you refresh the data download it just overwrites the same sheet, so that the spark line references reflect the 'new' data? Is there another way to write the code to get to my end goal?

Additional challenge! (read on)

The list of tickers I want to retrieve may change dramatically not only in quantity but also the names, the date range I would be interested may also change significantly. That said I'd like to some how figure out a way/formula to input such that the sparkline array reference will change according to the ticker/tab name and the column array dimension. Ideally I'd like to copy the first row/set of formulas (under the brown column headings) drag and paste all the way down to the last ticker. I've played a bit with the range selection portal that pops up when creating the sparkline and some formulas (i.e INDIRECT) just don't work there.

[Nerpilis]

Forgot to add: Thank you for looking and responding!

[Nerpilis]

Perhaps what I'm looking to do is not possible? I found another thread on another forum that employed a named range solution to someone's sparkline dynamic reference issue:

http://www.mrexcel.com/forum/excel-q...mic-range.html

Potentially could this this be the path I need to go down? I have some experience with named ranges, however I've never seen anything like this and I can't figure out how they were generated: when I look at the name range manager on my sample workbook there are a series of ranges all with the same name "ExternalData_1", and each of the series refers to one of the generated worksheets, column A and only the cells that are occupied. Seemingly this could be exactly what I want if I could get the reference to refer to the closing price column (starting at row 2) and perhaps the named range generated would be unique? Perhaps I don't understand the vba code enough, but to me I can't see how the named ranges are generated.


A minor aside: when the control button is pressed and the data retrieval is performed, it usually stops at the last ticker 's tab, is there a way to end the program such that you are viewing the input tab? (Solved: Sheets("Input").Select)

[Nerpilis]

OK I think I may be on to something, a slightly new direction - writing a sparkline macro with VBA, however I'm a bit embarrassed since I'm not sure how to loop through the list and place the sparkline in the desired cell location. so far what I did was record a macro to get the particulars on how to get the type of sparkline I want and some basic parameters I want. Then I borrowed a bit, at least the part that I understand from the yahoo data retrieval macro.... and here's what I have so far, note the comments on where I'm stuck with the syntax:

Please Login or Register  to view this content.