Setting up a spreadsheet?

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
User avatar
Topic Author
Bustoff
Posts: 2033
Joined: Sat Mar 03, 2012 5:45 pm

Setting up a spreadsheet?

Post by Bustoff »

I tried setting up a spreadsheet in Microsoft Excel. I have no experience with spreadsheets or Excel and it's kind of intimidating.

Anyway, I have a feeling I did not set it up correctly. In the vertical column are all my account totals. When I change an amount it automatically changes the total at the bottom of the column.

In my horizontal rows across the top I have the above mentioned account totals broken down into equities, bonds, cash, taxable and tax-advantaged.
For some reason the amounts in the horizontal rows wont change. :oops:

Are spreadsheets too advanced for someone like me who knows nothing about spreadsheets or Excel ?
User avatar
Dutch
Posts: 1277
Joined: Thu Jun 27, 2013 2:12 pm

Re: Setting up a spreadsheet?

Post by Dutch »

No, you'll get the hang of it. Spreadsheets are as difficult as you want them to be. You can do a lot with just some basic skills.

Can you post a screenshot? That would help to understand your problem.
User avatar
Topic Author
Bustoff
Posts: 2033
Joined: Sat Mar 03, 2012 5:45 pm

Re: Setting up a spreadsheet?

Post by Bustoff »

Dutch wrote:Can you post a screenshot? That would help to understand your problem.
No, I can't copy it.
Probably a bad idea asking about this on the forum when I can't articulate what I'm trying to do.
2retire
Posts: 400
Joined: Wed Jun 13, 2012 9:00 am

Re: Setting up a spreadsheet?

Post by 2retire »

If you have a Google account, you can go to http://docs.google.com and create one. You can also share it out with others so they can help you with whatever you are having problems with.
User avatar
Kosmo
Posts: 1303
Joined: Wed Sep 05, 2012 11:54 am
Location: Philadelphia

Re: Setting up a spreadsheet?

Post by Kosmo »

Maybe you could post the formulas you're using that are giving unexpected results. Excel has a decent help feature, but only if you know which functions you should be using. For simple arithmetic, fractions, percentages, and things like that you should be able to pick it up pretty quickly once you're pointed in the right direction.
User avatar
#Cruncher
Posts: 3977
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Setting up a spreadsheet?

Post by #Cruncher »

Bustoff, you need to get a beginners book on Excel or look up a tutorial on the internet. In the meantime here is a skeleton view of what it sounds like you're trying to do.

Code: Select all

row    col A     col B     col C     col D
  1   Account    Equity    Bonds     Total
  2   Schwab     30,000    10,000    40,000
  3   Fidelity   20,000    15,000    35,000
  4   Total      50,000    25,000    75,000
You need formulas in cells B4, C4, D2, D3, and D4:
in cell B4 =sum(b2:b3)
in cell C4 =sum(c2:c3) - can be copied from B4
in cell D2 =sum(b2:c2)
in cell D3 =sum(b3:c3) - can be copied from D2
in cell D4 =sum(d2:d3) or =sum(b4:c4) - can be copied from C4 or from D3
User avatar
Topic Author
Bustoff
Posts: 2033
Joined: Sat Mar 03, 2012 5:45 pm

Re: Setting up a spreadsheet?

Post by Bustoff »

Kosmo wrote:Maybe you could post the formulas you're using that are giving unexpected results. Excel has a decent help feature, but only if you know which functions you should be using. For simple arithmetic, fractions, percentages, and things like that you should be able to pick it up pretty quickly once you're pointed in the right direction.
Formulas? What are those?

I think I need to find out where to get basic instructions for learning how to do this. I was never able to make sense of the help menu.
I only got as far as I did because I simply started putting in numbers and noticed the column automatically added them up. That made me think I might be able to do this.

Sounds like I need to start google searching for Excel for Dummies.
m321matt
Posts: 2
Joined: Thu Aug 28, 2014 1:36 pm

Re: Setting up a spreadsheet?

Post by m321matt »

What exactly are you looking to do?

I'd be happy to whip up a template and email it over if it's just a simple return projection on various accounts/funds and totaling them up.
User avatar
Topic Author
Bustoff
Posts: 2033
Joined: Sat Mar 03, 2012 5:45 pm

Re: Setting up a spreadsheet?

Post by Bustoff »

#Cruncher wrote:Bustoff, you need to get a beginners book on Excel or look up a tutorial on the internet. In the meantime here is a skeleton view of what it sounds like you're trying to do.

Code: Select all

row    col A     col B     col C     col D
  1   Account    Equity    Bonds     Total
  2   Schwab     30,000    10,000    40,000
  3   Fidelity   20,000    15,000    35,000
  4   Total      50,000    25,000    75,000
You need formulas in cells B4, C4, D2, D3, and D4:
in cell B4 =sum(b2:b3)
in cell C4 =sum(c2:c3) - can be copied from B4
in cell D2 =sum(b2:c2)
in cell D3 =sum(b3:c3) - can be copied from D2
in cell D4 =sum(d2:d3) or =sum(b4:c4) - can be copied from C4 or from D3
Thanks #Cruncher ... that's exactly what I was trying to do. I thought something like that might be fairly basic and learn as I go.
But that is not the case. More learning is needed.
User avatar
wilpat
Posts: 534
Joined: Sun Jan 20, 2008 6:30 pm

Re: Setting up a spreadsheet?

Post by wilpat »

Contrary to the belief of many, profit is not a four letter word!
User avatar
Topic Author
Bustoff
Posts: 2033
Joined: Sat Mar 03, 2012 5:45 pm

Re: Setting up a spreadsheet?

Post by Bustoff »

m321matt wrote:What exactly are you looking to do?

I'd be happy to whip up a template and email it over if it's just a simple return projection on various accounts/funds and totaling them up.
Thank you Matt, very kind offer, but I need to learn this.
I was able to lable it like #Cruncher. I didn't realize you had to know formulas to make it work.
Boglegrappler
Posts: 1489
Joined: Wed Aug 01, 2012 9:24 am

Re: Setting up a spreadsheet?

Post by Boglegrappler »

Excel for most people isn't very hard to get used to. The "formulas" used by most people are simple addition and subtraction, multiplication and division.

It gets more complicated when you want to lay out a table comparing a formula with a couple of inputs, but once you get the basics you'll be able to look everything up and go step by step.
User avatar
LadyGeek
Site Admin
Posts: 95693
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Setting up a spreadsheet?

Post by LadyGeek »

wilpat wrote:Look here. Good info on excel.

http://osbxdc2.mzinga.com/discussions/B ... v=messages
That website requires cookies enabled to view.

======================================
Absolute beginner? Don't be intimidated, we'll get you going. Remember that this is a skill that will more than pay for itself and is very worthwhile to learn. Don't give up.

Let's go to the source. Microsoft would like to teach you how to use their products. Start here: Get to know Excel 2010: Create your first spreadsheet

This is a video tutorial, which I think is easier to understand than reading a text. Take your time and ask your questions here.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
LadyGeek
Site Admin
Posts: 95693
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Setting up a spreadsheet?

Post by LadyGeek »

This thread is now in the Investing - Theory, News & General forum (how to use a spreadsheet to track investments).
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
User avatar
CABob
Posts: 5091
Joined: Sun Feb 25, 2007 7:55 pm
Location: Southern California

Re: Setting up a spreadsheet?

Post by CABob »

Formulas are used whenever you want Excel to make a calculation.
Anyway, I have a feeling I did not set it up correctly. In the vertical column are all my account totals. When I change an amount it automatically changes the total at the bottom of the column.
This tells me that there is a formula in the cell at the bottom of the column where the total appears. The formula would tell Excel to total all of the values in the column. The formula probably looks something like =B2+B3+B4.
In my horizontal rows across the top I have the above mentioned account totals broken down into equities, bonds, cash, taxable and tax-advantaged.
For some reason the amounts in the horizontal rows wont change.
The most common reason would be that Excel cannot calculate these because you need to enter these figures taken from your statement or someplace. There could be a column on the right where the calculation could take place. The formula there would look something like =B2+C2+D2.
Bob
McCharley
Posts: 422
Joined: Tue Apr 12, 2011 5:45 pm

Re: Setting up a spreadsheet?

Post by McCharley »

Excel is awesome and terrible all at once. It has great, really advanced functionality (like XIRR) that really helps investors. Its graphs are terrible, though, really a joke. And in its current incarnation a lot of the useful stuff is lost in formatting fluff.

Still, though, it is a program worth becoming familiar with. For one thing, it is the common currency of data transfer. Companies like Scottrade enable you to download your entire transaction history as a .csv file (comma delimited, readable in Excel).
Post Reply