more-arw search

Spreadsheet vs Database - Excel vs Access

This is the third part in my series where I am making the case for when a database is superior to a spreadsheet.  I will reiterate: spreadsheets are a great tool for presentation of data but databases offer advantages when you need to transform your data or analyze large amounts of data from different sources.  Most people are not as familiar with databases as they are spreadsheets.  Once you fully understand what you can do with a database, you may want to invest time in learning how to create databases and queries.

There’s nothing like going out to your favorite restaurant and getting a great table with a view.  Well, with a database, you can get a great view to a table.  One of the most powerful features of databases is that it allows you to create views of data.  If you need to combine information from several spreadsheets, it might be easier to convert those spreadsheets into tables and then create views that combine columns from different tables into a single view.
Creating views or queries allows you to order your information exactly the way you want to see it.  Do you sometimes need to see your data from multiple perspectives? By creating multiple views, it allows you to see the data in different ways without actually changing the underlying data in the tables.  When the data in the tables is refreshed, your views automatically update without having to re-write it.  Once the view is created, all you need to do is run it.
Are you downloading information from your billing system or other subsidiary system, transforming data and then uploading it to your general ledger?  You can transform your data within a view.  For example, let’s say you download customer transactions from your billing system and the transaction numbers contain dashes but you need to remove those dashes to upload the information to your general ledger.  In Excel, you could use the Substitute function to remove every instance of a dash in your transaction number.  However, you would have to copy this formula to each instance of a transaction number that you wanted to transform.

With a database, you would simply upload the raw data from the billing system (assuming it comes out in database format) into a database.  Then you create a query that lists all the columns in the exact order that you want to see them.  For the column containing the transaction number, you write a similar Substitute function that will remove the dashes from the transaction number.  You save the query (view) and execute. This accomplishes two things: 1). It automatically removes the dash from the transaction number. 2). It preserved the original data so you can still see it in that format if needed.  Each time you need to refresh the data, you would merely overwrite the data in the table and re-run the query. To see it in the original format, you would simply copy the same query and delete the “Substitute” function from the transaction column.  You now have two views of the same data.

The setup of a database is unquestionably more labor intensive but the flexibility you can achieve once it is setup is well worth it.  In my next article, I will further explore the power of queries and give you a few suggestions on how you could start to use them to transform your finance and accounting operations.

Comments

Jilynne Tapia
Title: Assistant Controller
Company: United TranzActions
(Assistant Controller, United TranzActions) |

Do you have any recommendations on databases?

Richard Schultz
Title: CFO
Company: SoMedia Networks
LinkedIn Profile
(CFO , SoMedia Networks ) |

Hi Jilynne,

If you are just getting started with a database, I'd recommend Microsoft SQL Express edition (it's free) or MySQL (owned by Oracle, also free). Here's why.

1) They're free. I hate paying for something if I don't know I'm going to like it.
2) They are pretty much industry standard - most web-based databases use MySQL, and most accounting packages use (or support the use of) MS SQL. If you are focused mainly on data from websites, try MySQL. If you're working mostly with accounting information, go for MS SQL.
3) They are very similar languages, and are at least one generation ahead of Access. MS Access is based on the "Jet" database engine, and the language, syntax and capabilities are all weaker than either MS SQL or MySQL. While it is certainly the easiest database to start with, you will need to "unlearn" a bunch of stuff once you start working with either of the other databases. Given that you are in an accounting environment, you will almost certainly need something more powerful than Access, so it's a bit of a waste to learn Access then learn either MS SQL or MySQL. However, if you learn MS SQL or MySQL first, the other language is almost identical, and so your learning is transferable.

Either way, there are tons of information on the web for using either product, and your IT department probably has at least one person who can hold your hand in the early days.

Good luck! Learning SQL was probably the most productive use of my time in the last 20 years.

Topic Expert
Wayne Spivak
Title: President & CFO
Company: SBAConsulting.com
LinkedIn Profile
(President & CFO, SBAConsulting.com) |

I would add to the list Richard gave, one other free database, Postgres. Depending on your needs, it may (note may) be a better choice (MySQL is great for reads vs transactional, which would be Postgres).

I have found MySQL much easier to use, and as such it is the only database (in a Linux environment) that I use.

Gregory Q. Jenkins
Title: President
Company: Quentasia Industries Incorporated
(President, Quentasia Industries Incorporated) |

That's a difficult question to answer without knowing the specifics of your needs. There are different types of databases: relational and multi-dimensional. For small departments with a limited number of users MS Access works well. For larger departments and more data intensive queries, a SQL database might do the trick. For more powerful data crunching then you should look into the multidimensional tools such as COGNOS or Essbase. If you're just starting out with databases these tools are harder to master so you'll have to weigh the benefit against the time investment that will be necessary in order to be able to use the tools. Hope this helps.

Harold D. Tamayo
Title: Vice President of Finance
Company: MHA Inc., a Roper Technologies Company
LinkedIn Profile
(Vice President of Finance, MHA Inc., a Roper Technologies Company) |

Great response Gregory. I would start with the business requirements and reporting needs. Through the discovery phase the person can define what success would look like. Maybe the need is for building good Excel capabilities or using Access. However, I do recommend that if your needs are more at the enterprise level, not ad-hoc querying and the person is thinking about SQL or other type of more powerful database tools, then it might be also time to bring in business intelligence technology. However, in my view, always keep in mind that there is a cost associated with expanding organizational analytic capabilities and tools. As Richard said, "I hate paying for something if I don't know I'm going to like it"

Gregory Q. Jenkins
Title: President
Company: Quentasia Industries Incorporated
(President, Quentasia Industries Incorporated) |

I think Richard makes some excellent points. I would slightly disagree in that Access is a powerful tool when compared to a spreadsheet but there is no question that MySQL or MS SQL are the better technologies. If you're a small department Access is OK for now. However, overtime, you will outgrow it. I have spent the majority of my career in finance and accounting and I can tell you it should be mandatory for everyone to know SQL. It's that powerful of a tool in my opinion. I am building an operational data store for my company using SQL Express. It's absolutely a robust version despite the fact it's free. Definitely worth looking into MySQL or SQL Express. Harold also makes an excellent point. You need to figure out what the business requirements for the database will be. I know. I know. It's already starting to sound like too much work but trust me it is worth the effort.

Linda StClaire
Title: VP Finance
Company: Extron Inc.
(VP Finance, Extron Inc.) |

I use a kind of blended method, I use ODBC (Open Database Connectivity) in Excel to download the tables and fields I want from my ERP (and it has refresh capabilities as well), so that I can manipulate the data and use Excel functions for analysis of the data at the same time. Ironically, the queries created this way can be represented as a SQL statement (and I sometimes make changes to the query in the actual SQL statement, but would not say I am SQL proficient), however ODBCs drag and drop is so fast, queries can be created and modified quickly. The downsides are: you (apparently) can actually write data back to the ERP (I have no clue how, but I've been warned) and you cannot share the queries across the company, because you cannot restrict access to specific tables-customer support doesn't get to see the balance sheet. If a specific query turns out to have ongoing demand in other departments, I have our IT department use the SQL statement to create an SSR report, that can be run by the other departments, without any modifications possible or access to the actual database.

Topic Expert
Wayne Spivak
Title: President & CFO
Company: SBAConsulting.com
LinkedIn Profile
(President & CFO, SBAConsulting.com) |

Using two-way ODBC can be extremely dangerous. All accounting systems have built in logic for audit purposes, balancing issues and overall data integrity.

Read-only ODBC is GREAT, and I believe it should be used effectively by all, as you have detailed, but you must, I repeat must make sure your login-credentials are read-only!

Gregory Q. Jenkins
Title: President
Company: Quentasia Industries Incorporated
(President, Quentasia Industries Incorporated) |

I have to agree with Wayne. I know from personal experience of the dangers of circumventing the ERP's data checks. Read-only capability is great but you do want to prevent the possibility of writing data back to the tables in your database.

Topics:
Products and Companies: