Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
Excel Wizards
Old 03-19-2011, 10:00 PM   #1
Full time employment: Posting here.
RetiredGypsy's Avatar
 
Join Date: Mar 2008
Posts: 895
Excel Wizards

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.
__________________

__________________
I'm free and I like it!
RetiredGypsy is offline   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

Old 03-19-2011, 11:24 PM   #2
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
pb4uski's Avatar
 
Join Date: Nov 2010
Location: Vermont & Sarasota, FL
Posts: 16,470
Quote:
Originally Posted by RetiredGypsy View Post
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.
__________________

__________________
pb4uski is online now   Reply With Quote
Old 03-19-2011, 11:40 PM   #3
Moderator
Ronstar's Avatar
 
Join Date: Aug 2007
Location: A little ways southwest of Chicago
Posts: 9,363
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
__________________
Ronstar is online now   Reply With Quote
Old 03-19-2011, 11:49 PM   #4
Thinks s/he gets paid by the post
 
Join Date: Nov 2005
Location: North of Montana
Posts: 2,753
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.
__________________
There are two kinds of people in the world: those who can extrapolate conclusions from insufficient data and ..
kumquat is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Looking for some Excel help Bimmerbill Other topics 3 07-23-2010 07:08 PM
excel question simple girl Other topics 3 12-20-2008 12:16 PM
Hobnobbing with other Wizards BUM Life after FIRE 8 11-01-2007 12:47 PM
Financial Math Wizards Required nwsteve FIRE and Money 7 02-20-2005 03:53 PM

 

 
All times are GMT -6. The time now is 12:06 PM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.