I posted a userdefined function a year ago that automatically imported stock prices from yahoo finance. The workbook demonstrated in this blog posts imports stock prices and refreshes a stock chart. The problem is how excel calculates maximum and minimum axis values.

So I created a small vba macro that is assigned to the chart. Click the chart and the max and min values are instantly changed. I also created dynamic named ranges. You don´t need to adjust chart cell ranges manually anymore when stock data changes.

VBA macro

Sub RefreshChart()
With Worksheets("Sheet3").ChartObjects("Chart 1").Chart.Axes(xlValue)
    .MinimumScale = Worksheets("Sheet3").Range("E2")
    .MinimumScale = Worksheets("Sheet3").Range("E3")
End With
End Sub

User defined function

I made some small modifications to the user defined function.

• Stock data are now sorted from smallest to largest by date
• Prices are numbers

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, c As Single, irows As Single
Dim temp1() As Variant
ReDim temp(6, 0)
ReDim temp1(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 = ""
For a = 1 To Len(csv)
  b = Mid(csv, a, 1)
  If b = "," Then
    If UBound(temp, 2) = 0 Then
        temp(r, UBound(temp, 2)) = txt
    ElseIf r = 0 Then
        temp(r, UBound(temp, 2)) = txt
    Else
        temp(r, UBound(temp, 2)) = Val(txt)
    End If
    r = r + 1
    txt = ""
  ElseIf b = Chr(10) Then
    If UBound(temp, 2) = 0 Then
        temp(r, UBound(temp, 2)) = txt
    ElseIf r = 0 Then
        temp(r, UBound(temp, 2)) = txt
    Else
        temp(r, UBound(temp, 2)) = Val(txt)
    End If
    ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1)
    txt = ""
    r = 0
  Else
    txt = txt & b
  End If
Next a
For c = LBound(temp, 1) To UBound(temp, 1)
    temp1(c, UBound(temp1, 2)) = temp(c, 0)
Next c
ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) - 1)
ReDim Preserve temp1(UBound(temp1, 1), UBound(temp1, 2) + 1)
For a = UBound(temp, 2) To LBound(temp, 2) Step -1
    For c = LBound(temp, 1) To UBound(temp, 1)
        temp1(c, UBound(temp1, 2)) = temp(c, a)
    Next c
    ReDim Preserve temp1(UBound(temp1, 1), UBound(temp1, 2) + 1)
Next a
irows = Range(Application.Caller.Address).Rows.Count
For a = UBound(temp1, 2) - 1 To irows
    For c = 0 To 6
        temp1(c, a) = ""
    Next c
    ReDim Preserve temp1(UBound(temp1, 1), UBound(temp1, 2) + 1)
Next a
YahooStockQuotes = Application.Transpose(temp1)
Set http = Nothing
End Function

Sheet 2 - Import stock prices

Array formula in cell range C1:I150

Download excel *.xlsm file

Adjust vertical stock chart axis automatically.xlsm

Related posts:

Learn how to create a stock chart in excel

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

Select numbers in each permutation

Excel udf: Import historical stock prices from yahoo

Excel recursive udf: List files in a folder and subfolders