## Excel udf: Import historical stock prices from yahoo – added features

*Article updated on August 06, 2017*

This post describes how to import historical stock quotes from yahoo. This custom function is more advanced than the previous one: Excel udf: Import historical stock prices from yahoo

You can choose:

- Start date
- End date
- Interval (daily, weekly and monthly)
- Stock symbol ticker

### VBA code

Function YahooStockQuotes(Fyear As String, Fmonth As String, Fday As String _ , Tyear As String, Tmonth As String, Tday As String, interval As String _ , ticker As String) Dim url As String, http As Object Dim csv As String, temp() As Variant, txt As String Dim r As Integer, a As Single, b As String Dim chk As Boolean ReDim temp(6, 0) url = "http://ichart.finance.yahoo.com/table.csv?s=" & ticker & _ "&d=" & Tmonth - 1 & "&e=" & Tday & "&f=" & Tyear _ & "&d=d&a=" & Fmonth - 1 & "&b=" & Fday & "&c=" & Fyear & "&g=" & interval & "&ignore=.csv" On Error Resume Next Set http = CreateObject("MSXML2.XMLHTTP") http.Open "GET", url, False http.Send csv = http.responseText r = 0 txt = "" chk = False For a = 1 To Len(csv) b = Mid(csv, a, 1) If b = "," Then temp(r, UBound(temp, 2)) = txt If chk = True Then temp(r, UBound(temp, 2)) = CDec(temp(r, UBound(temp, 2))) r = r + 1 txt = "" ElseIf b = Chr(10) Then temp(r, UBound(temp, 2)) = txt If chk = True Then temp(r, UBound(temp, 2)) = CDec(temp(r, UBound(temp, 2))) ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1) txt = "" r = 0 chk = True Else txt = txt & b End If 'If a = 200 Then Exit For Next a ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) - 1) YahooStockQuotes = Application.Transpose(temp) Set http = Nothing End Function

### Where to copy vba code?

- Press Alt-F11 to open visual basic editor
- Click Module on the Insert menu
- Copy and paste vba code.
- Exit visual basic editor

### How to create an array formula

- Select cell B6
- Copy (Ctrl + c) and paste (Ctrl + v) custom function into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.

### How to copy array formula

- Select cell B2
- Copy (Ctrl + c)
- Select cell range B2:H28
- Paste (Ctrl + v)

### Download example file.

YahooStockQuotes-added-featuresv2.xlsm

(Excel 97-2003 Workbook *.xls)

### Recommended blog posts

Want to know more about excel and stocks? You must read these blog posts:

- Tracking a stock portfolio #2 in excel
- Tracking a stock portfolio in excel (auto update)
- Calculate your stock portfolio performance with Net Asset Value based on units in excel
- Automate net asset value (NAV) calculation on your stock portfolio (vba) in excel
- Calculate your stock portfolio performance in excel
- Dynamic stock chart in excel – Add date ranges
- Create a dynamic stock chart using a web query and a drop down list in excel
- Scan stock markets in excel
- Stock alerts in excel
- Learn how to create a stock chart in excel
- Excel udf: Import historical stock prices from yahoo

Create a dynamic stock chart using a web query and a drop down list in excel

In this blog post I´ll show you how to update a stock chart in excel using a drop down menu […]### 18 Responses to “Excel udf: Import historical stock prices from yahoo – added features”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Hi - great spreadsheet thanks! HOw do I add additional attributes -I've tried changing the attributes on the arconfigsheet but it defaults to the original string?

Steve

Steve,

This is how you change attributes in an array formula:

1. Select cell B7

2. Click in formula bar

3. Change attributes in udf

4. Press and hold Ctrl + Shift

5. Press Enter

6. Release all keys

is there a way to have today's date in this (get yahoo finance} as a formula instead of a hard code number? or the beginning date?

How would I do this on a daily basis if I want to look at thelast 60 days' stock value?

becki,

Yes, you can. Try this:

=YahooStockQuotes(YEAR(TODAY()-60), MONTH(TODAY()-60), DAY(TODAY()-60), YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()), "d", "msft")

Thank you!

Hi Oscar,

How can we sort the order from start date to End Date??

Thanks,

Jimmy

[…] https://www.get-digital-help.com/2011/07/27/excel-udf-import-historical-stock-prices-from-yahoo-added… […]

Hi Oscar,

Is there a way for your udf to have the data in date descending order instead of ascending date order, thanks. In other words the newest data at the bottom?

Yeah, I would like to know too.

Please Oscar to reply his comment, Thanks.

[…] Enter this udf array formula in cell range C23:I355. It fetches data from yahoo and a stock ticker you specify. You can read more about this udf here: Excel udf: Import historical stock prices from yahoo […]

Well, using MarketXLS works for me. It's great.

I love this function that you provided and used it for over a year, suddenly it does not work anymore. Any ideas what I can do to fix this or troubleshoot?

Joe

Yahoo has changed the way you download historical stock data, I found this:

https://stackoverflow.com/questions/44030983/yahoo-finance-url-not-working

I will update the UDF as soon as there is a solution.

Hello Oscar,

This is a fantastic excel and vba informative site - Thank you.

I have a question on a form of syntax you use in an If statement

Stock Market trends

Formula in cell M24:

=IF((N24="")*(O24=""),IF(M25="","",M25),IF(N24"","B",IF(O24"","S","")))

How does the asterisk work in this formula please?

PS Please copy me in on your fix for the yahoo stock price history

when you have it. Google Finance also provides a download url but it has limited capability and is convoluted!

Thank you

Regards

Murray

Murray,

=IF((N24="")*(O24=""),IF(M25="","",M25),IF(N24"","B",IF(O24"","S","")))How does the asterisk work in this formula please?

The asterisk works like an AND operator. If both N24 and O24 are equal to "" then TRUE is returned.

Tip! Use the "Evaluate formula" button on tab "Formulas" on the ribbon to follow formula calculations.

I found a solution to the broken Yahoo Finance API here:

https://www.xlautomation.com.au/free-spreadsheets/yahoo-historical-price-extract

[…] To make things more interesting than copying historical prices from yahoo I am going to use a modified version of the user defined function in this post: Excel udf: Import historical stock prices from yahoo – added features […]

[…] quickly import stock prices to an excel sheet? I am using the user defined function from this post:Excel udf: Import historical stock prices from yahoo – added features It let´s you choose start, end date and interval (daily, weekly, monthly […]