Solved

Importing DOW Jones total to Excel

Posted on 2013-06-19
1
954 Views
Last Modified: 2013-06-19
I'd like to simply import the closing total of the DOW jones industrial average into a single cell in my spreadsheet.

It can be from any financial website, like Marketwatch,  Yahoo Finance, etc.

Thanks.
0
Comment
Question by:James Powell
1 Comment
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 150 total points
ID: 39259571
Hi,

pls refer to  http://www.financial-modelling.net/tutorials/excel/open-excel-retrieve-stock-quotes-in-a-formula

Code DJIA

Function StockQuote(strTicker As String, Optional dtDate As Variant)
' Date is optional - if omitted, use today. If value is not a date, throw error.
If IsMissing(dtDate) Then
dtDate = Date
Else
If Not (IsDate(dtDate)) Then
StockQuote = CVErr(xlErrNum)
End If
End If

Dim dtPrevDate As Date
Dim strURL As String, strCSV As String, strRows() As String, strColumns() As String
Dim dbClose As Double

dtPrevDate = dtDate - 7

' Compile the request URL with start date and end date
strURL = "http://ichart.finance.yahoo.com/table.csv?s=" & strTicker & _
"&a=" & Month(dtPrevDate) - 1 & _
"&b=" & Day(dtPrevDate) & _
"&c=" & Year(dtPrevDate) & _
"&d=" & Month(dtDate) - 1 & _
"&e=" & Day(dtDate) & _
"&f=" & Year(dtDate) & _
"&g=d&ignore=.csv"

' Debug.Print strURL

Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", strURL, False
http.Send
strCSV = http.responseText

' Debug.Print strCSV

' The most recent information is in row 2, just below the table headings.
' The price close is the 5th entry
strRows() = Split(strCSV, Chr(10)) ' split the CSV into rows
strColumns = Split(strRows(1), ",") ' split the relevant row into columns. 1 means 2nd row, starting at index 0
dbClose = strColumns(4) ' 4 means: 5th position, starting at index 0

' Debug.Print vbLf
' Debug.Print strRows(1)
' Debug.Print "dbClose: " & dbClose

StockQuote = dbClose

Set http = Nothing

End Function

Open in new window

Regards
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now