Table of Contents

1. Create a stock chart in excel 2003
2. Create a stock chart in excel 2007/2010

Create a stock chart in excel 2003

This tutorial will show you how to create stock charts in excel 2003.

How to copy historical stock prices to excel

Search for a company in Yahoo Finance and go to historical prices. Select and copy historical prices.

Go back to excel. Paste the values to a sheet.

Insert the stock chart

Select the data below "High", "Low" and "Close" in your excel sheet.

Go to "Insert" and click "Chart..." in the top menu in Excel.

Press "Finish".

Chart settings
To change the gray background to white,  right click with mouse button on the grey area and select "Format plot area".  Select a background color.

Create a stock chart in excel 2007/2010

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

The user defined function makes it easy to import historical prices from yahoo finance.

This chart is a mess.

Change the vertical axis minimum and maximum values

1. Select vertical axis
2. Right click on vertical axis
3. Click "Format Axis..."
   
4. Click Minimum: Fixed and type 24
5. Click Maximum: Fixed and type 32
   
6. Click Close

Add months to stock chart

In cell A1 type : Months

In cell A2:

=TEXT(B2, "MMM")

In cell A3:

=IF(TEXT(B3, "MMM")=TEXT(B2, "MMM"), "", TEXT(B3, "MMM"))

Copy cell A3 and paste down as far as needed.

1. Select stock chart
2. Right click on stock chart
3. Click "Select Data"
   
4. Click "Edit" button
5. Select cell range A2:A113
6. Click OK
7. Click OK
8. Select horizontal axis
9. Right click on horizontal axis
10. Click "Format Axis..."
11. Go to "Alignment"
12. Change custom angle to 1
13. Click Close

User defined function:

Option Explicit
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 String, txt As String
Dim r As Integer, a As Single, b As String, c As Single, irows As Single
Dim temp1() As String
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
    temp(r, UBound(temp, 2)) = txt
    r = r + 1
    txt = ""
  ElseIf b = Chr(10) Then
    temp(r, UBound(temp, 2)) = txt
    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

Related posts:

Adjust stock chart axis automatically

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

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

Excel udf: Import historical stock prices from yahoo

Interactive chart in excel (vba)