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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

draesideAuthor Commented:
file attached
DR-Market-Feed-new.xlsm
Robberbaron (robr)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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.