Stock Charts

It's been awhile since I have written anything here. If you have been checking, my apologies - you know how the summer goes- between working, chauffering Kelsey to summer school and attempting to improve and expand my garden my time just evaporates. If you click on the RSS feed button, the blog will be delivered to your email and that will save you a trip here.

Today, I wanted to talk about some very basics in creating a stock chart. Yes, given the current stock market situation, I thought people might be interested in knowing how to do this even though it will be a depressing exercise if you track over time. However, some of you will find it useful.

Excel has four different types of stock charts.

• High-Low-Close
• Open-High-Low-Close
• Volume-High-Low-Close
• Volume-Open-High-Low-Close

The High-Low-Close chart is often used to illustrate stock prices. When you chart the data, the High and Low values are displayed with vertical lines while the open and the black bars represent close prices.

It is important to remember that you must organize your data in the correct order to create this and other stock charts. In other words, if you opt to create a High-Low-Close chart then your data needs to be ordered High Price, Low Price, Closing Price.
Creating Stock Charts


It is important to remember that you must have the correct number of series that the chart requires. Excel also requires that the data be in the proper order.

1. Select your data

2. Click Insert and Select Other Charts icon

3. Select the type of stock chart you wish to create




 

When the stock chart is created, a whole series of layouts and chart styles are displayed on the ribbon

Here is another example:

I made up data. When I used real data, it was too sad.

SUMIF

SumIf is a great function that a lot of people don’t know up about. Basically it combines two very popular functions -the SUM function and the IF Function. SumIf tests specified cells and if those cells meet certain conditions or “criteria” the selected cells are then summed up.


Think of the possibilities: You may wish to sum up receipts by different lockboxes or sum up the value of past-due invoices. Or you may just want to add up negative numbers in a column quickly or add up the returns or overtime for a particular day.

I am using a very simple example to illustrate SumIf. In the example below, I tested to see if any of the house prices in cells A3: A6 exceeded 150,000 so that I could sum the related realtor commissions. In looking at the example, you can see that there are two houses that meet this criterion (at row 4 and 5) and that the corresponding realtor commissions are in Column B.

The house prices at row 4 and 5 met the specified criteria so the corresponding values in Column B, (12,500 and 10,000) were added up to calculate total commissions on houses with prices over $150,000.


So, basically, the IF component checked to see if any houses were over $150,000 and then the SUM component added up the corresponding realtor commissions associated with those houses in Column A.


Two notes of caution of a SUMIF.

*Notice that I started my range at A3 instead of A2.It is important to remember not to include your column headings as part of the data range when you select your data as this may result in an incorrect answer. If you have a terrible memory and don’t like to fuss around then select the entire column by clicking on the column letter so that you don’t have to worry about it.

*If you use IFs frequently, you may have already noticed the quotation marks around the criteria. SumIF is not considered a logic function. It is considered a Math and Trig function so the syntax is a bit different and quotation marks are needed if you type the criteria in as I did.

Excel is usually forgiving and will automatically insert them if you forget. If the criterion used is a cell reference, such as B12, then you would not need to use the quotation marks; however, the contents of the cell would need to include the operator if there was one.

An alternative way to use SUMIF  if shown below.  This is an example of a rollup. I have personnel data and I want to add up salaries IF they meet the shift criteria that I have in cell G2.

 Here I have told Excel to add up all the salaries in Column F if the Shift information in Column E meets the criteria specified in cell G2. In this case, I want to add up all the salaries for everyone on Shift 1.

(To make it even more useful, use a data validation list in cell G2 containing the different shift numbers).

Generating Random Numbers

Excel's Random Number function can be very useful if you are conducting an audit and want to select a sample.

RandBetween returns a random number between the numbers you specify. A new random number is generated every time the worksheet is calculated.

The syntax is: =Randbetween (bottom, top)

For example =RANDBETWEEN (1,10) will randomly generate a value between 1 and 10. If you copy it down a column, a number will be randomly generated for each cell.

The good news is that the function is readily available in Excel 2007 however if you are using Excel 2003 than you will need to run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu.


 
If you need to make the numbers static after they have been generated, use Copy>Paste Special Values.

Outlining - A Feature Designed for the Accountant

Last week, I attended a great seminar put on by Jon Peltier and Alex Kerin on Charts and Dashboards. I will be sharing some of that information with your shortly.  But in the meantime, I thought you might be interested in the Outline Feature. A useful feature that is often overlooked.

The Outline feature was designed with the CPA in mind. The Outline feature allows you to gain a quick overall view of a complex worksheet such as a P&L or a balance sheet. This feature allows specified row and /or columns to be expanded or collapsed which enables the user to work with details or summary information on an as needed basis. You can get as detailed or as high level as you want.


Excel allows you to create an automatic outline where the program determines what should be grouped through the Auto Outline feature or the user can manually group rows and columns. If you are one of those people who create empty rows within your data so that it looks nice then the Auto Outline feature will not work for you. And yes, the Auto Outline feature looks at the formulas in your worksheet and that is how it determines how to group so if you don't have any formulas in the worksheet then the Grouping feature will not work for you.

How to use the Outline feature
1. Make sure that your mouse is inside the data range
2. Select the Data tab
3. Click Group
4. Click Auto Outline

Notice the horizontal line above the column headings and the vertical lines with minus symbols to the left of the row numbers.





5. Click on minus above Column G and notice that the week detail is hidden. What you are left with is just the Total Month.

Click on the Level 1 button to hide all the detail rows or use the minus at rows 7 and 12 to manually hide the detail rows.

If you click on the + beside Row 7 or row 12 and see that the hidden information is now displayed



To expand or collapse specific columns or rows, click on the outline buttons. Bars connected to box containing a minus and a plus identify groupings of data. The minus outline button collapses rows or columns and the plus sign expands the outline. Individual rows or columns at the lowest level of a grouping are identified by black dots. The building block buttons allow the user to select the levels of outlined desired. Levels can range between 1 and 8 depending on the complexity of the worksheet.


Manual Outline

Excel’s Auto Outline does a great job however at times it may not work the way you wish particularly if you have a lot of blank rows. In that case, Excel allows you to manually select columns or rows and outline them yourself.


To manually outline data:

1. Select a group of rows or columns that you want to group
2. Select the Data tab
3. Click Group
4. Select  if you want to group by columns or rows

5. Click OK