I need ideas for a macro that will download data from the web and update pre-existing MS excel dataset?

Question

So the application would need to find Yahoo Finance (maybe), find the ticker, find the historical data, download the relevant data (for ex: omitting the “volume” data), and either import just the data which is new since the last update, or download all info and overwrite the existing. And then make sure that it is doing the summary calculations on the now-larger data set.

Answer 1

I'm not sure how to get data from a webpage into Excel using an Excel macro, but I usually just select and copy then paste it by hand. I would paste and only calculateulate what was relevant and ignore the 'volume' to save steps.

I can tell you about the summary calculations. You can insert a row, insert the data and the formula adjusts to the larger data set itself.

I recorded a macro that I did the above operation: here's the code: (There were six numbers in the col, I inserted a row and added one value so there were 7 numbers. The summary calc was in row 8 and it moved to row 9.

Rows("7:7").Select
Selection.Insert Shift:=xlDown
Range("C9").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-8]C:R[-2]C)"

If you want to modify this to be more dynamic you can assign a variable to the existing row by doing this: (variable name is 'abc')

abc = ActiveCell.Row

Good Luck with the project!

Answer 2

I'm afraid the input data is not consistent enough to make a macro out of it. But you might get it down to where there's a place you could put a pause to check whether it's working correctly, and just hit "continue" or "edit," as necessary. I must admit, it sounds like more trouble than it is worth. Doesn't someone (like maybe Dow Jones) provide spreadsheet downloadable data already organized?

Or you could try downloading the whole thing -- whatever format it's in -- to your own computer, then tweaking the format if necessary, then feeding it to your macro.

Good luck! If you succeed, you should market the program. (That's more than just a macro, in my book.)