locked
Re-Design Application Question RRS feed

  • Question

  •  

    I have a question on how I should re-design an application.  To keep things simple, it is a Financial Analysis tool in which, current relevant financial data is entered to produce graphs and charts that will enable us to make decisions about buying/selling various financial products.

     

    Currently the system is comprised of about 30 excel spreadsheets, that our financial engineers enter data into.  A strict process must be followed to ensure the integrity of all spreadsheets is kept.  Our engineers start by entering up-to-date financial data into a couple of different spreadsheets, they then update(from the spreadsheets they just modified) about 10 different spreadsheets by pressing a button on each one, and modify the data on those sheets until they get their decision graphs looking exaclty how they want them.  This is a very simplified version.

     

    Obviously this current system is ineffecient and should be enhanced, but my question is should I keep the current application in excel or should re-write it using something like windows forms, a backend such as SQL Server, and reporting software or maybe a mixture of windows forms, and excel?  There is quite a bit of numeric data(including historical data from years back), is this something that would be appopriate to keep in a database?  Also, most of the spreadsheets are dependant on data entered into just a few, it seems like storing common data would be a good reason for a database?

     

    Obviously there are numerous solutions, but any advice would be much appreciated.  Thanks for the help!

    Tuesday, October 9, 2007 8:39 PM

All replies

  • Hi,

    It seems to me that the fact that you're moving from spreadsheet to spreadsheet updating with data from a previous spreadsheet could be very inefficient, and quite probably error prone?

    I guess the real question is, what effort would be required to move to a new system, versus what effort is currently required to use the existing system.  From the amount of time taken, you can calculate the cost of creating a new system, versus the cost of not doing so, and the amount of time that will elapse before you break even with a redevelopment overhead, then you can decide.

    From the point of view of calculations and the like, obviously SQL Server is excellent at that sort of thing running stored procedures and the like.  It really depends on what the system is meant to do, you said that the engineers adjust the spreadsheets until they get the graphs looking exactly like they want them.  Depending upon the measurements involved, and the tweaking, a data warehouse with OLAP cube may be useful.  On the other hand, the calculations may not be that involving, and it is just due to the fact that data is spread across spreadsheet, and the overhead of opening and closing, and tweaking that takes the time?  

    I wouldn't be using Excel, because as is often the case, a small simple system is evolved into something that isn't really relevant to Excel, and need upscaling.  From the basic scenario it sounds like there is a lot of time involved in opening and closing spreadsheets, which would probably be the biggest gains you would expect, as the calculations are likely to be easier in SQL Server.  One thing you can consider, to speed things up maybe, would be some sort of console application to write values into spreadsheets as required from a command line, then automate the updates in a spreadsheet, and finally open the final spreadsheet.  That would speed things up, but is clearly a short term workaround for a unsuitable solution, by the sounds of things.

    I'm not a big fan of fixing things for the sake of it, if you can do the numbers, and that tells you that it's worthwhile, then make the decision to redevelop.  At that point, I would recommend re-analysing the processes involved, as you'd obviously not want to replicate the exact process you have in excel, into sql server, as it seems that that is already inefficient.  What you need to do, is find out what the requirements are for the system, by all means using the existing system to gather information, and possible reuse, or copy.  At that point you will then have brought the system up to date, and it then be able to cope with actually making the solution better, making it useful rather than just about fitting the basic requirements?

    Without a full picture, I can only give you general advice.

    I hope that helps you get started,

    Martin Platt.
    Tuesday, October 9, 2007 11:20 PM
  • Perhaps Excel could still be maintained for some of the front end of the application. As well as keeping the existing charts and graphs, there are no doubt financial figures already calculated using formulas in the Excel spreadsheets. These calculations may require an Excel type financial "calculation engine". It seems that it is the data entry and all the funky "pressing buttons" stuff that is the real problem. This could be moved to a more solid data entry front end that is linked to a database. Then the Excel spreadsheets could mine the database. You could consider creating an Office Application using VSTO for the data mining and a WinForm app for the data entry. Here is a link for more information on VSTO.

     

    http://msdn2.microsoft.com/en-us/office/aa905533.aspx

    Wednesday, October 10, 2007 6:19 AM
  • Thanks for the replies so far, they are very helpful!

     

    I do have some further questions.  Would it be wise to use excel to store large volumes of financial data that may be accumulated for years?  Also, am I right in saying that a good reason to use an OLAP cube is because it pre-aggregates/calculates a number of values from a number of different tables, and in doing so makes retrieval of the calculated data faster?  Isn't this like creating a view in a database?  What happens to the OLAP cube when new data is entered into a table that it is retrieving from?  Is there some sort of trigger that refreshes it?  I am new to this concept that's why I ask.

     

    As far as the front end goes, a console application would probably not work since our engineers like to enter in values, and see how other values change as they enter them in, something excel does very well.  I could easily mimick this functionality on a windows form, but is there a good way to embed a spreadsheet into a windows form, or is there some sort of control that does similiar actions?

     

    Wednesday, October 10, 2007 3:55 PM
  • Excel is not a database so it is not where you want to store "large volumes of financial data" although I have seem huge Excel files with hundreds of large sheets. Databases are generally where you will want to aggregate large amounts of data as they are designed to do that efficiently. I feel that your dilemma is that the data needs to be entered into Excel where it updates the charts, pivot tables etc. However the "older" data is only required for aggregation and does not need to be modified. However the data remains in Excel turning it into a database effectively. Perhaps you can provide a mechanism in Excel to commit data to a "repository". The data is moved from Excel into a database and then the aggregate information is mined from the database and displayed in Excel. These aggregated numbers can then be used in the same way to create the charts and pivot tables.

     

    Wednesday, October 10, 2007 10:04 PM

  • No, you wouldn't use Excel for large amounts of data.  A database is more for storing multiple entries or records with the same of similar data, Excel is like a single record, so similar in concept to a database View.

    Normally you would use OLAP to change the measures around, the aggregated data that is pre-prepared will allow you to take a measure and view it against a different measure, so would suit situations such as "What if I did x, what the 
    outcome be with regard to totals, averages and so on when viewed against y".  You wouldn't use it typically simply for caching, or pre-preparing data, SQL Server is very efficient at that, especially when using stored procedures.  
    If you're asking if a SQL view is similar to an OLAP, the answer really is no.  It's similar in the case of bringing potentially different data through into a view, but that's pretty much where it stops.  
    To update data within an OLAP cube, you would typically have to update the cube with the data from the database, as the two work independently.  This shows you why 
    you use OLAP for data warehousing, since you can perform intensive data on the cube, without affecting a highly transactional
    state in the database.  
    Since you bring it up, "tweaking" values in a cube could be similar in a way to changing data in spreadsheets, then clicking the button to get the output.  You would have to be more specific on how things work, but I'd say that you'd need a compelling reason to move up to SQL and OLAP, such as data mining type activities.

    With respect to your currently existing systems, you could implement a solution in a number of different ways, to utilise the existing functionality, but the fact that you have to go through spreadsheets updating and clicking buttons seems to be the driver for asking these questions?  If it is, then you would probably be better to implement the solution in some controls that mimic the spreadsheet behaviour, but simplify entry and updates.
    You could also automate Office, as previously mentioned, but in that instance you're not necessarily improving the situation, since the new solution is based on the old problems, and large amounts of data is going to be the biggest factor in such a system.

    Perhaps the best approach is to identify the highest pain points in the system, and choose to replace those out, making sure you abstract things in such a way as to ease a possible gradual move to a fully migrated system?  I'm not a fan of deciding to replace everything, unless there is a good reason to do so, beyond because it's cool technology!  If you replace a little at a time, you limit down time, if that is a consideration, and you see how the system is evolving.  The downside to a gradual approach is the overhead of having to integrate with an existing system.  You could probably abstract that process through interfaces, so the new portion of software sees only an interface, and behind the interface is some wrapper class that implements the interface to allow reading from and writing to the spreadsheet?  When you want to replace that part of the system, you then can without a problem.  You also get the upside of being able to replace the solution, but still import from and export to Excel (I know people like to be able to do that!)

    I hope that this gives you some ideas to start really digging into research into the solution space, as well as allowing you to ask the right questions in terms of requirements,

    Good luck,

    Martin Platt.

    Wednesday, October 10, 2007 10:34 PM
  •  

    i think i would take the tact of using a SQL 2005 database (common storage) with server-side Excel components (reduce cost of re-formulation of the Excel functions) on an web/app server to create the reports, output them as PDF or Excel, and store the PDF as a record of the report generated. for input, either use Excel or a web application. for data updates, SSIS or BizTalk to integrate your sources into the database.
    Thursday, October 11, 2007 4:18 PM
  • Thanks alot, this should help me get started!

     

    Friday, October 12, 2007 8:00 PM