Stock Market Portfolio Tracker in MS Excel

This excel file will download stock prices from yahoo server.

This portfolio tracker download stock prices automatically from Yahoo server. We just need to open the file & update your stock codes & click the download button

It is very user friendly.Just read the instructions in "ReadMe.

In “Portfolio” sheet we can track all details like purchase price, current price
Previous Closing price, Change in %, Volume, Day H, Day L, 52 H, 52 L, Date, Time

Download

Two tips for giving your Excel worksheets a professional look

Excel provides several underline formats to give your spreadsheets a professional look and to make them easier to read at a glance. For example, labels and values are often underlined to distinguish them from the rest of the data. To access these formats, click on the cell you want formatted and press [Ctrl]1. Click on the Font tab and then click on the drop-down arrow of the Underline combo box. This box offers four different types of line formatting. While the Single and Double format are appropriate for text labels, they are not appropriate for indicating totals and subtotals. For Totals, you should choose the Double Accounting format; for Subtotals, choose the Single Accounting format.

Another way you can make your worksheets look more professional is to remove zero values. For example, you've just copied a formula down a column, and now you have blocks of cells containing 0.00 or 0.00%. You could go back and delete the formulas from those particular cells, but an easier method would be to change the worksheet to avoid displaying or printing the zero values. To do so, follow these steps:

  1. Go to Tools | Options.
  2. Click the View tab.
  3. Under the Window Options section, clear the Zero Values check box.
  4. Click OK.

10 simple tips for working more efficiently in Microsoft Excel

#1: Exploit defined names

Defined names aren't just for ranges. You can use a defined name to define a constant value, such as a discount amount. Use the feature as you normally would, entering the literal value or expression that evaluates to the desired value into the named cell. For instance, select a cell and choose Name from the Insert menu. Then, select Define. Enter the descriptive name Discount and click OK. Now, in the same cell, enter the actual discount amount, say 3 percent (just enter .03). Now, you can use the defined name, Discount, in your formulas instead of entering the literal value .03. For instance, Excel would use .03 for Discount when evaluating the following formula:

=TotalPrice - (TotalPrice * Discount)

This quick tip has two benefits: It makes updating much simpler, as you can quickly change the value in Discount and Excel will automatically update all dependent formulas. And it eliminates data entry errors.

#2: Quick copy to noncontiguous cells

Copying data or a formula is simple. You just drag the source cell's fill handle and Excel copies the data or formula from the source cell to the cells you select using the fill handle. But copying isn't always a nice, neat, contiguous package. Sometimes you need to copy data or a formula to a series of noncontiguous cells. You could paste the source data into each individual cell, but that's the hard way. Instead, you can copy data into a noncontiguous block.

First, copy the source data. Then, hold down the Ctrl key while you click each cell in the noncontiguous destination range. Once you've highlighted each target cell, press Ctrl+V, and Excel will copy the source data into each of the highlighted cells. Formulas copied this way obey referencing rules, in regard to absolute and relative addresses.

An alternate method is to right-click in the cell that contains data you want to copy and choose Copy from the resulting submenu. Then, right-click a destination cell and choose Paste. At this point, the source cell is still highlighted, which means you can copy the contents again. Right-click another destination cell and choose Paste. Continuing selecting destination cells until you've completed the copy task. Press Esc to clear the selection of the source cell.

#3: Customize defaults

Excel uses template files to control default settings in new workbooks and sheets. For most of us, the settings are adequate. However, if you find yourself resetting the same defaults for each new workbook or sheet, consider changing the defaults permanently.

To change default settings for a workbook, open Book.xlt, make changes, and then save the file. Don't change the file's name; you're just updating it. (It's a good idea to create a copy of the original Book.xlt so you can revert to Excel's original settings if necessary. Name the copy BookOriginalSettings.xlt or something similarly descriptive.) After changing Book.xlt, all new workbooks will use the custom settings you applied. To change a sheet's default settings, open Sheet.xlt, make the necessary changes, and save it.

If you don't have one or both files, simply create your own. Just be sure to save them in Excel's XLStart folder (\Program Files\Microsoft Office\XLStart).

#4: Enter repetitive data quickly

Tip #2 shows you how to copy existing data into noncontiguous cells. You can also use this technique to enter data into a series of noncontiguous cells. Hold down the Ctrl key and click all the cells into which you want to enter data. Then, type the text you want to enter and press Ctrl+Enter. Excel will enter the typed text into all of the cells in the noncontiguous selection.

#5: Create custom lists

Most of us work with sets of data that seem to repeat themselves throughout our projects. That means we can enter the same values in numerous spots. If you frequently enter the same dataset, consider creating a custom list. To do so, choose Options from the Tools menu and then click the Custom Lists tab. In the List Entries control, enter each item in the list, one entry per line, in the order in which you want it to appear. When you've completed the list, click Add. Excel will copy the list to the Custom Lists control. Click OK to close the Options dialog. To enter the list, select a cell and enter any name in the list. Then use the fill handle to complete the list.

If you want a partial list, enter the item you want to begin with and then pull down the fill handle. Excel will fill in the remaining names.

If the list already exists in the sheet, you don't have to retype it to create a custom list. Simply select the list before choosing Options from the Tools menu. Then, click Import on the Custom Lists tab.

#6: Customize movement

By default, the cell pointer moves down when you press Enter. Selecting the cell immediately below the current one won't always be what you need. For instance, some people enter data from column to column. You could press the Right Arrow key instead of Enter, but out of habit, most of us reach for Enter. Even if you can retrain yourself (or users) to use the arrow keys, they're far enough away from the main keys to slow down data entry.

Fortunately, you can change the cell pointer's default direction. Chose Options from the Tools menu and then click the Edit tab. Select the Move Selection After Enter check box (if necessary) and then choose a direction from the option's drop-down list. For instance, to move from column to column, you might choose Right instead of Down.

While entering data, you can temporarily force the cell pointer to move in the opposite direction by holding down the Shift key while you press Enter.

#7: Hide everything but the working area

You usually hide a column or row to conceal or protect data and formulas. You can also hide unused regions of a sheet to keep users from exploiting unused areas or to help keep them on task by not allowing them to wander. By hiding unused rows and columns, you present a sheet that focuses on just the work area.

To hide unused rows, select the row beneath the sheet's last row. (Select the row header to select the entire row.) Next, press Ctrl+Shift+Down Arrow to select every row between the selected row and the bottom of the sheet. Then, choose Row from the Format menu and select Hide. Repeat this process to hide unused columns, only select the column header in the first empty column. Press Ctrl+Shift+Right Arrow and then choose Column from the Format menu instead of Row.

Before you hide anything, make sure you don't inadvertently hide an obscure area by pressing Ctrl+End to find the last cell in the sheet's used range. Unhide the rows and columns by selecting the entire sheet. Then, select Row or Column from the Format menu, and choose Unhide.

#8: View formulas, or not, quickly

You probably know that you can view all the formulas in a sheet by choosing Options from the Tools menu and selecting Formulas on the View tab. Doing so displays formulas instead of their evaluated results. But there's a quicker way. Press Ctrl+~ (the tilde character to the left of the number 1 on your keyboard). The keyboard combination toggles between formulas and normal view. When you're finished viewing the formulas, simply press Ctrl+~ again to return to normal view.

#9: Identify printed sheets

Printing a sheet is a common task. Some users find it useful to print the name of the workbook in the header or footer. In Excel 2003, you can accomplish this by choosing Page Setup from the File menu and clicking the Header/Footer tab. Then, choose the appropriate item from the Header control's drop-down list. Versions prior to 2003 can use the following VBA procedure to print the full file's pathname:

Sub FormatHeader()
With ThisWorkbook
ThisWorkbook.Worksheets(sheetname)PageSetup.LeftHeader = .FullName
End With
End Sub

where sheetname is the sheet's name as a string value. To make the procedure more dynamic, use ActiveSheet.Name instead. That way you can run it against any sheet in the workbook.

#10: Speed up calculation time

How, when, and what Excel calculates is a huge subject. In general, cell references and calculation operations are the main performance vampires. Reasonable formulas and even lots of data don't usually slow things down. Complex formulas and repetitive references are the real culprits. Here are a few basic guidelines that should help you avoid calculation bottlenecks:

  • Avoid complex and array formulas. Use more rows and columns to store intermediate values and use fewer complex calculations.
  • Reduce the number of references in each formula to the bare minimum. Copied formulas are notorious for repeating references and calculations. Move repeated calculations to a cell and reference that cell in the original formula. (See Tip #1 for an alternate suggestion.)
  • Always use the most efficient function possible: Sort data before performing lookups; minimize the number of cells in SUM and SUMIF; replace a slow array with a user-defined function, and so on.
  • Avoid volatile functions if possible. Excel recalculates these functions with each recalculation, even if nothing has changed. Too many volatile functions (RAND(), NOW(), TODAY(), and so on) can slow things down.

Various amazing Calculators made in Excel

1. The Ultimate Financial Calculator Download
Investment Valuation
Rates of Return
Financial Statements
Capital Budgeting
Time Value of Money
Loans and Leasing

2. EMI Calculator Download
Calculate EMI of any kind of Loan

3. Budget Planner Download
Create a quick and easy budget(Daily,Monthly,Yearly)

4.Amortisation Download
This calculator will give you a break - up of your Interest and Principal every month.


Selecting only blank cells

This is one of the questions colleagues at work are often asking me. The need arises usually when you are importing data from the mainframe to Excel.
Many times you are getting something like this:


If you want to produce a Pivot Table from this data, you will have to fill the blank cells in column A:
  • values in cells A3 and A4 must be "Peter"
  • values in cells A6 and A7 will must be "Paul"
  • values in cells A9 and A10 must be "Mary"

This can be done manually in our example. But suppose you have, let's say, 2,000 records in your table. In that case you can take advantage from the "Go To - Special" feature in Excel.

How?

First select the whole relevant range (the column range, in our case A1:A10). Then press "Ctrl + G" or F5. You will get this:



Hit the "Special" button and mark "Blanks"


and hit "OK"


Now enter "=" (the equal sign) in cell A3 (the first blank cell in the selected range), , click cell A2 (the cell above the first blank cell), and hit "Ctrl + Enter". You will get this:

This technique can be useful in many other cases. For example, if you want to delete a whole line when some cell is blank (like in our example), you can select all the blank cells using F5, then press the right button in the mouse, from the pop-up menu select "Delete --> Entire Row" and press "OK". All the rows with a blank cell in the selected range will be deleted.

How to find where the two curves cross

Sachin, a colleague at work, came to me with this question: How can I find the value of the point where to lines cross in a graph?
Excel don't seem to have a build-in procedure to find such a value. The workaround I suggest is to use the Solver Add In.

Let's look at this example:

The formulas to produce the graph are:
  • in cells B2 to B10: =3*A2+25
  • in cells C2 to C10: =A2^2+2*A2+1

Obviously Y1 crosses Y2 somewhere between X=4 and X=6. In order to find the exact value of X we'll use the Solver (in the menu bar: Tools-->Solver. If you can't see it, then you have to install the Add In).

Before opening the Solver copy the formulae in cell B10 and C10 to cells B12 and C12.


In the "set target cells" window write (or select) B12.

In the "by changing cells"window write or select A12.

In the "subject to constraints" window add two constraints:

  • B12 = C12 (the answer to our question)
  • A12>=0 (in order to get a positive value)

Now, your sheet will look like this:

Hit the "Solve" button, and you will get this:



CONVERTER FROM XL TO EXE

This utility converts Excel files from the XL format to an EXE format (convert XLS to EXE, convert XLA to EXE, convert Excel to EXE). Like XL files, the EXE files created are not independent of Excel, but they allow you to define Excel's properties before opening, and improve the portability and functionality of your workbooks.

The conversion, moreover to maintain the original format features, inclusive for editing thru the Excel, adds others functionalities where the autocompression and the control over the Excel highlight. So, by be specialized in Excel and VBA, can be more advantageous than ZIP files, ZIP autoextracts and program installation utilities.

Download Converter

Tested in the Microsoft Excel for Windows versions 97, 2000, 2002(XP), 2003, and 2007.

EXE format features:

- Can be read and write (Changes done in an EXE file opening in the Excel to write are saved in the proper EXE file);
- When saving changes, backups are created. Optionally, you can specify whether to overwrite or not overwrite previous backups (This makes possible more restoration levels than using XL format);
- Can be read-only, very read-only, doesn’t permit ‘Save’ and ‘Save as’ too;
- Changes can be saved to a new XL file, leaving the EXE unaltered (Good to be used like a template);
- Changes can be saved to multiple XL files with an incrementing number (Good to be used like an invoice template);
- Can be opened in the Excel without macro alert or with a custom alert (Good to work with known files containing macros using the Excel in medium or high security level);
- Can show, without text size limit, an alert before opening in the Excel (Good to installation alerts, copyright, guidance etc.);
- Can require password-to-open so safe that only can be cracked by attempts (brute force) same knowing the converter source code. Then, if you put a password and forget it, no-one, neither this utility author, can help you;
- There are four levels of autocompression: none, normal (faster), medium (slower), and high (slower). Normal is equivalent to a normal zip file. This makes large files more portable, mainly when they need protection, because protected files in XL format are of low compression. In a test with a simple workbook of 5 Mb, the ZIP compressed only 14% and this got 74% because the cryptography is done after the compression;
- Can be attached one or more text or binary files (Good to be used as installer file);
- All attached XL files can be opened too at start and controlled as one whole like a 'workbook' of workbooks;
- Can be opened in an exclusive Excel window (exclusive Excel instance) refusing, while open, any try to share with other strange workbook;
- Can be forced the closure of running Excel instances at start up. This is helpful to use the EXE as an installer for a COM add-in;
- When opened in a separate instance, it can start Excel in automation mode, very automation mode; Excel will run totally clean without XLSTARTs, add-Ins, and COM add-ins too. This is better than /automation Excel command line option that doesn’t avoid COM add-ins;
- When opened in a separate instance, it can control the Excel window style (maximize, minimize, normal, or hidden). These four last features are good to be used when your workbook is a dictator application;
- You can avoid the original converted EXE file from being renamed;
- All the above features can be configured when you convert;
- The conversion can be done thru the utility interface or by command line;
- The command line can contain all of the options, alert text, and file names and paths to attach. You can generate long and complex command lines from the user interface and send them to the clipboard to be pasted anywhere, such as in an argument of VB or VBA Shell function or in the Windows Run dialog box;
- You can convert back to XL format using Excel's Save As command if not read-only;
- When convert, it creates a backup of the original XL format file without overwriting previous one;
- Files in this format can function as EXE in CD Autorun;
- The conversion only adds about 129Kb which, depending on the file size, can be regained during autocompression;
- The converter and any converted files are standalone in the presence of the Office library, no extra library is necessary and neither changes in your Excel or Windows setting.

Fractions of Seconds

Fractions of Seconds

Over on Swimming Splits, Olly asks:

I am a rowing coach and am putting the data that my crew gives me from their training sessions into a spreadsheet in the mm:ss.00 format. However, if I make a mistake inputing the data e.g type 1:23.03 when it should be 1:23.30 something weird happens.

When I click in the cell, the value rounds up or down depending so 1:23.30 will round to 1:23.00. This will have a massive impact on the results that I am looking at. Is this a common occurrence in excel or is there something that I am doing wrong or should be pressing?

Good question. I'm not sure that I know this answer definitively, but I'll tell you what I think based on what I see.

Dates and times are strange beasts in Excel. I should say that they are a strange beast because they are the same thing. As you probably already know, dates are stored as the number of days from a particular date, usually 31-Dec-1899. That means that 01-Jan-1900 is stored as 1 and 04-March-2007 is stored as 39,145. Similarly, times are stored as fractions of a day. While dates are integers, times of day are the fractions between the integers. Today may be 39,145, but today at 8:00AM is 39145.3333333. It's only 8:00AM and already a third of the day is gone.

This method is great for adding, subtracting, and generally doing other math operations on dates. What it's not good for is understandability by most humans. Excel attempts to bridge that gap by displaying dates and times as dates and times rather than these cryptic numbers. Note that when you enter 0:01.03 in a cell, the formula bar displays 12:00:01 AM. Usually the formula bar will tell you the truth regardless of how you've formatted the cells, but not in the case of dates and times. Like an over-protective mother, it shields you from the harsh realities. The noteworthy aspect of this example is that there are no hundredths in the formula bar. The hundredths are still stored, but they aren't displayed in the formula bar.

If you were to edit the cell, say, by pressing F2 and Enter you lose the hundredths. Pressing Enter is the same as entering 12:00:01 AM into the cell, which contains no hundredths. To change the cell from 3 hundredths to 3 tenths, follow these steps: F2 to edit the cell, backspace three times to remove the AM and the preceding space, type .3, press Enter. I don't know of any way to get the hundredths to display in the formula bar.

It seems rather easy for Microsoft to have done this differently. If a user enters fractions of seconds, display fractions of seconds. Otherwise display it as it is now. There doesn't seem to be a trade-off here that I can see (other than coding time by developers).

Calendar for 178 Years

Calendar_for_178_years
Calendar_for_178_years

Click above to download

World cup Cricket Tracker in Excel

Track the world cup from this Excel Sheet.
It is Unique and Rarely available.


Schedular_cum_Result_Tracker
Schedular_cum_Result tracker

Magic Spread Sheet

magicspreadsheet
Magic SpreadSheet

This Magic Spread Sheet contains following MAGICS:
1)Disappearing the Card
2)Secret Number
3)Magic Eight Ball

Its Amazing.

To convert numbers into words(Rs.)

Numbers+in+Words(Rs)
Numbers+in+Words

This worksheet helps to convert numbers into words.

MS Excel Keyboard Shortcuts

Navigating in Excel Shortcut

Switch between Worksheets CTRL-PageUp/CTRL-PageDown

Switch between Workbooks CTRL-Tab

Move one character up, down, left, or right. Arrow keys

Go to end of a contiguous range CTRL-Arrow Keys

Select a cell range SHIFT+Arrow keys

Highlight a contiguous range SHIFT-CTRL-Arrow Keys

Select entire worksheet CTRL+A

Move to the beginning of the line. HOME

Go To F5

Move a Sheet/Copy a Sheet Alt-E-M

Change Zoom Sizing Alt-V-Z

Entering and editing data Shortcut

Complete a cell entry and select…

...the cell below. ENTER

...the previous cell above. SHIFT+ENTER

...the next cell to the right. TAB

...the previous cell to the left. SHIFT+TAB

Delete cell and then get inside the cell BACKSPACE

Delete cell/selection. DELETE

Edit inside a cell (edit cell mode) F2

Once inside edit cell mode (F2)…

...Start a new line in the same cell. ALT+ENTER

...Highlight individual characters within cells SHIFT+Arrow keys

...Highlight contiguous string within cells SHIFT+CTRL+Arrow keys

...Delete the preceding character. BACKSPACE

...Delete the character to the right of the insertion point. DELETE

...Cancel a cell entry. ESC

Spell Check. F7

Insert a comment. SHIFT+F2

Fill down. CTRL+D

Fill to the right. CTRL+R

Undo the last action. CTRL+Z

Redo the last action. F4 or CTRL+Y

Hiding / Unhiding Rows and Columns Shortcut

Hide the selected rows. CTRL+9

Unhide any hidden rows within the selection. CTRL+SHIFT+( (opening parenthesis)

Hide the selected columns. CTRL+0 (zero)

Unhide any hidden columns within the selection. CTRL+SHIFT+) (closing parenthesis)

Selecting, grouping, inserting, and deleting cells Shortcut

Highlight Entire Row SHIFT+SPACEBAR

Highlight Entire Column CTRL+SPACEBAR

Group Rows or Columns SHIFT+ALT+RIGHT ARROW KEY

Ungroup Rows or Columns SHIFT+ALT+LEFT ARROW KEY

Clear the contents of the selected cells. DELETE

Delete the selected cells. CTRL+MINUS SIGN

Insert blank cells. CTRL+SHIFT+PLUS SIGN

Pivot Table Introduction

XLPivotTableIntro06-07
Pivot Table Introduction

Pivot tables are used to summarize huge data in Excel.
You can rotate rows & column to view different reports.

Download above file to learn Pivot Tables.

Notes for Advance Filter in Excel

AdvancedFilterNotes
Advanced Filter Notes

Complete info on how to use Advance Filter in MS Excel

Excel Keyboard Shortcuts

Excel Keyboard Shortcuts
Excel Keyboard Shortcuts

Excel keyboard Shortcuts to help you work quickly.

Can you solve this puzzle??

puzzle
Puzzle

Click above to download the puzzle.

Its a challenge

VBA Macros ebook

It is interactive ebook which will help you in learning & developing Macros.

Click below to download VBA Macros ebook


VBA Macro ebook
VBA Macro ebook

EMI Calculator

Click the link below to download EMI Calculator

EMI_Calculator
EMI_Calculator

For Sudoku Lovers

Following files uploaded for sudoku lovers

1.Sudoku solver & sudoku Generator

2.Giant sudoku

3.Multiple solution sudoku

Click this link to download all above files :)

Sudoku_all
Sudoku_all.zip

Excel in MS EXCEL & VBA Macros

==============================
Excel in MS EXCEL & VBA Macros
==============================

Click this button BELOW to Download Useful Excel Material

Orkut Excel Community