Automatically and Periodically Loading Data into Excel

[agfor3]

Hi All,

As a side project, I am working on creating a stock index comprised of 25 or so stocks and tracking their change.

To set this up, I loaded the closing prices of the stocks for the past 6 months, and weighted each of the stocks a certain way (ex. .1, .015, .2 of the entire index) based off of market share. Then, I multiplied the weight by the stock price and added them up to find the index price. I applied the formula for all the trading days and graphed the index.

What I want to do now is automate the process so that I can load the closing prices for each stock for trading days (not every day, because no trading on weekends or holidays) automatically into the spreadsheet so that each day, it calculates the index price by itself based off the previous close.

Does anyone know how I would go about this?

In terms of getting the data, there is a way to export as csv from Yahoo Finance using =NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s= [Ticker Symbol] &f= [Data Type]").
(but I don't know how to automate).

If anyone can help guide me, I would really appreciate it.

 

[sedgefield02]

This is a procedure that will download a price from yahoo finance
In this version I load the ticker symbol into BP5 (you can change that to any range you want) and the price and dividend info is returned in BQ5


code
GetThePrice: '==================================


If Range("BP5") = "VIX" Then Range("BP5") = "^VIX"
If Range("BP5") = "YAFFX" Then Range("BP5") = "YAFIX"


qurl = "http://download.finance.yahoo.com/d/quotes.csv?s=" + Range("BP5") ' bp5 is the id of the fund to price

qurl = qurl + "&f=" + "l1r1d" ' "nd1l1" ' get price only

Range("BQ5") = ""
Set DataSheet = ActiveSheet
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=Range("BQ5"))
.BackgroundQuery = True
' .TablesOnlyFromHTML = False
.FillAdjacentFormulas = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With

'Range("D7").CurrentRegion.TextToColumns Destination:=Range("D10"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, Space:=False, other:=False
Range("BM9") = ""
Range("BN9") = ""
Range("BO9") = ""

Range("BQ5").TextToColumns Destination:=Range("BM9"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, Space:=False, other:=False


Return
/code

 

[sedgefield02]

Sorry about the sloppy indentation on the code - I'm new to posting on this forum so I'm not quite up on the tools.