Automatically create graph in excel when importing data

I have created the attached spreasheet (DR Marker Feed New.xlsm) which imports 3 sets of stock market data from Yahoo finance. For each set of data I would like to automatically generate a line graph in a separate worksheet. The data range will expand on a daily basis so the graph's range needs to dynamically expand to accomodate this. Each graph needs to also include a horizontal line depicting -40% below the most recent market closing price.
draesideAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Robberbaron (robr)Connect With a Mentor Commented:
can you post an update with example of the chart you want?
i guessed it is the AdjClose that you want graphed ?

Is it ok to delete the charts prior to loading new ones ?

try this code and advise changes necessary. It only creates the first chart but others are easier then.

Sub Trial1()

    Sheets("All 3 Indexes").Activate
    
    'insert new column 60%
    Columns("J:J").Select
    Selection.Delete
    Range("J6").Activate
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("J7").Select
    ActiveCell.FormulaR1C1 = "40%close"
    
    'calc 60% of last close
    Range("J8").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*60%"
    
    'copy it down
    Range("J8").Select
    Selection.Copy
    Range("I8").Select

    Selection.End(xlDown).Select
    Selection.Offset(0, 1).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.End(xlUp).Select
    Range("H7").Select
    
    Dim cht As Chart
    
    Sheets("Charts").Shapes.AddChart(Left:=50, Top:=50, Width:=800, Height:=1200).Select
    Sheets("Charts").Activate
    ActiveChart.SetSourceData Source:=Range("'All 3 Indexes'!$C$7:$J$6325")
    ActiveChart.ChartType = xlLine
    ActiveChart.SeriesCollection(1).Delete
    ActiveChart.SeriesCollection(1).Delete
    ActiveChart.SeriesCollection(1).Delete
    ActiveChart.SeriesCollection(1).Delete
    ActiveChart.SeriesCollection(1).Delete
    
    Set cht = ActiveChart
    cht.HasTitle = True
    cht.ChartTitle.Text = "Chart 1"
    
    Range("L7").Select
End Sub

Open in new window

0
 
draesideAuthor Commented:
file attached
DR-Market-Feed-new.xlsm
0
All Courses

From novice to tech pro — start learning today.