Excel Wizards

[RetiredGypsy]

I've been using Excel spreadsheets ever since I got a job to track expenses. It's been absolutely wonderful. In creating series of linked equations through different spreadsheets, I thought I'd learned a lot. So now that I'm trying to make real-time charts, I realize how little I've actually learned.

My goal is to track how much I've personally invested each day against how much my investments are worth each day, and the percentage of that difference. For example, if I've invested $50k in an S&P 500 index in total up to today, but the actual value of the stock and reinvested dividends has given me a total of $80k in that index, a 60% increase.

I really don't know enough to know what questions I should be asking, so I figured I'd turn to the wizened financial trackers here who know the ins and outs of Excel and see if what I'd like to do is even possible.

Can you have real-time charts in Excel? Where if you enter a number into a cell, it automatically updates a graph for you? Can it also calculate the percentage of difference? If you can, where can you find a reference or tutorial on how? My Google Fu is weak, and I haven't had any luck in finding the answer so far.

I don't know if it makes a difference, but I have the 2007 version.

 

[pb4uski]

I've been using Excel spreadsheets ever since I got a job to track expenses. It's been absolutely wonderful. In creating series of linked equations through different spreadsheets, I thought I'd learned a lot. So now that I'm trying to make real-time charts, I realize how little I've actually learned.

My goal is to track how much I've personally invested each day against how much my investments are worth each day, and the percentage of that difference. For example, if I've invested $50k in an S&P 500 index in total up to today, but the actual value of the stock and reinvested dividends has given me a total of $80k in that index, a 60% increase.

I really don't know enough to know what questions I should be asking, so I figured I'd turn to the wizened financial trackers here who know the ins and outs of Excel and see if what I'd like to do is even possible.

Can you have real-time charts in Excel? Where if you enter a number into a cell, it automatically updates a graph for you? Can it also calculate the percentage of difference? If you can, where can you find a reference or tutorial on how? My Google Fu is weak, and I haven't had any luck in finding the answer so far.

I don't know if it makes a difference, but I have the 2007 version.

Yes, most Excel graphs are "real time" so it you change an input cell that the graph draws on then the graph will be automatically updates.

Not sure on the other part of your question but the percentage increase is simply the fair value of the holding/cost of holding -1, or (80/50)-1 in your example.

 

[Ronstar]

Gypsy - I took an excel course at the local junior college. There are a lot of online training tutorials as well.

As pb4uski stated, the graphs upgrade themselves.

I believe its possible with a little programming to download stock prices to Excel and have your portfolio updated automatically (except no of shares). I'm looking into this for myself, but havent figured it out yet

To get the pct, I would put =(a-b)/a in the cell, where a is the beginning value and b is the ending value. Then format the cell to pct

 

[kumquat]

You can import prices from via web query: Overview of connecting to (importing) data - Excel - Microsoft Office

Edit to add:
I've done this a lot with Open-Office but don't use Excel.