Posted on 29 December 2009
are an interesting way to look at data. Here is the first preview of the treemap add-in for excel. Being a demo, the number of data points are limited to 50.
Here the a demo file containing the treemap add-in for Excel
. (The demo treemap version is limited to a maximum of 50 rows and is meant for evaluation purpose only. The full version does not contain this restriction.)
Posted on 22 October 2009
Nearly everybody I know uses Excel for tracking the returns in the stock market. Not many however know that Excel can be used for online stock tracking and getting live price information from stock exchanges like NYSE and Nasdaq as well. Here's how.
Establishing a connection to the MSN server for Stock Quotes
Click on the data tab in the Excel ribbon (in Excel 2007 and later). In Excel 2003 and earlier, ...
Posted on 23 September 2009
This two part article consists of:
Part B - Exporting Excel to HTML using VBA
Let’s first take a look at tables in html. Data tables are typically stored in HTML using the <table> tag. Such a table will typically consist of one or more rows (<tr> tag) with each row having one or more cells (<td> tag). To read a table from an HTML document ...
Posted on 18 September 2009
The labels used with the chart category axis are probably the most ignored part of the entire chart making process. You see, the labels on the value axis can be modified, the bars, the lines and the plot area - all of them can be embellished - colors added, widths adjusted, descriptions applied
- the works you know ! Even the poor little gridlines can afford a makeover. But what about the chart's category axis label - what do ...
Posted on 15 September 2009
Chart table - as the name suggests - is a beautiful fusion of a chart and a table giving rise to a visually appealing yet information rich presentation of data. Not that the concept itself is anything new - experts have openly and vociferously championed the cause of the chart table, its various facets have been discussed at length in numerous forums and the latest versions of excel come equipped with tools that further aid their creation (namely sparklines). However ...
Posted on 04 September 2009
In our previous article we covered adding an average line to a bar chart
. At times, however, it may be useful to show an average line for each period or group when the chart consists of more than one such period or group. The advantage here is apparent - not only can the reader view the data points individually, he/she is also able to build a map of how various groups are placed with in relation to each other....
Posted on 02 September 2009
VBA can be used to make interactive charts that respond to mouse movements. In this article we first start of by making a simple bar chart embedded in a chart sheet and then use the mouse move event of the chart sheet to make it interactive.
Create a simple Bar Chart
For the purpose of this exercise, we take the list of top 15 billionaires and sort it based on their networth.
Posted on 29 August 2009
Here's the VBA code to read, list and combine all excel, csv, text any other format file in to a single Excel sheet. By editing the code, you can make the program read and then combine files of only a specified extension say, Excel (.xls) or comma separated (.csv) or text (.txt). If you have a lot of small files getting FTP'd over or getting accumulated in a location, this can save a lot of time.
Dim File_Path As ...
Posted on 14 July 2009
This ranks right at the top of annoyances when it comes to working with the VBA IDE - the mouse scroll wheel simply does not work. So if you have more lines of code than can fit into a single screen in the VBA IDE, you end up having to drag the scroll bar to up/down to navigate. Jimmy Peña recently posted this very useful VBA tip
on his informative blog
that provides the solution to this issue. ...
Posted on 22 June 2009
Posted on 17 June 2009
Stock charts come in various shapes. There are candlestick stock charts, line and bar stock charts, “Kagi” stock charts, moving average stock charts and everything in between. For a financial service provider how to present financial information pertaining to a specific stock can a critical question. On one hand, if you give too many options, you loose out on user comfort and increase API complexity. On the other, give too few options and you end up losing more demanding customers. ...
Posted on 11 May 2009
This Sudoku solver in Excel will help you solve a sudoku puzzle with brute force. This Sudoku solver, has been written in VBA and incorporates a brute force algorithm which helps its solve some of the toughest sudoku puzzles using nothing more than an excel spreadsheet.
You can download the Sudoku Solver in Excel with Code, Logic and Algorithm
In this article we will talk about how to develop a Sudoku solver using code in Excel, discover the tips ...
Posted on 08 May 2009
The results of the "Stress Test - Housing Crisis of Shame" are out. I just saw NY Times publish the stress test figures this morning
. The first thing that came to my mind when I saw the results was, well.......not that they require another $75 billion, that's pretty well known to most of us by now, was ....gosh.....what an opportunity wasted. Here was a important and relevant piece of data that could have really been presented better using ...
Posted on 30 March 2009
A mortgage calculator made using excel can come in handy when you need to do quick analysis of how a mortgage fits in with your overall finances. Over the weekend, I designed a mortgage calculator in excel
that calculates how much you need to pay for your mortgage.
The nice thing about this spreadsheet based calculator is that all you have to do is to plug in the loan amount, the ...
Posted on 11 November 2008
One exercise that is done with almost religious fervor in all companies at the beginning of each financial year is the preparation of the budgets and targets for the next year. Every year a fresh faced newly recruited manager is identified, handled this dreaded responsibility and asked to present the first draft to the management team. And so the rookie spends a couple of nights pouring over multiple spreadsheets and a plethora of for complicated formulae and comes out with, ...
Posted on 08 November 2008
This article describes 3 ways of simulating multithreading in VBA using Excel.
One feature that I sorely miss in the VBA's integrated development environment (IDE) is the ability to do multithreading. I think part of that comes from having worked in my previous avatar with Java and C++.
In the past few days I’ve been thinking about ways in which one can ‘simulate’ multithreading in excel. (If you noticed, I said ‘simulate’ – you can’t actually multithread with vba – not atleast ...
Posted on 22 October 2008
came up with this nice graphic on flight cutbacks by airlines in 2008 compared to the year before. It essentially is a radial chart with a lot of interactivity thrown in.
I took a shot at doing this chart in Excel. I cut a lot of corners in the process (the defendant offers lack of data in his defence mylords :-)) but I think I came quite close to achieving some ...
Posted on 21 October 2008
This article shows three methods using which you can send email using VBA in Excel. You can download a working example of sending email using vba in Excel
Excel allows you to create the best-in-class charts, work with large amount of data and data sources work with images, flowcharts and what not. And if that was not enough, you can use the inbuilt vba editor to automate sending emails from ...