Connecting Excel to SQL Server: Adventures in an Almost Legendary Project

panumas nikhomkhai at Pexels

We were hired to convert a large UK accountancy firm’s many interconnected Excel spreadsheets into a robust SQL Server database project. Part of the project included exporting data from Excel to SQL Server.

The intricacies of doing this were immense.

The London accountancy firm

The accountancy firm is a large, respected company based in London. They manage the accounting of thousands of independent contractors across the UK, doing everything from tax returns to day-to-day payments for them.

They deal with massive amounts of data.

Using Excel as a database was no longer an option

Photo by StellrWeb on Unsplash

Understandably, the company had outgrown its in-house Excel spreadsheets and systems.

We had already created a fairly sophisticated Excel database solution for them which manipulated and processed the incoming and outgoing data efficiently. But the company had reached a level of expansion where there was simply too much data for Excel to deal with speedily and rapidly.

And there were other concerns as well, such as:

  • Portability of the data, should the company wish to move to a different platform in the future.
  • Advanced reporting which was becoming ever more difficult, given the sheer quantity of data they were dealing with.
  • Ease of backups.
  • Maintaining the integrity of the data as the company grew and more staff gained access to raw figures.
  • Security of data.

Excel had been doing a great job until the numbers hit several million records per spreadsheet.

We had already created a database in Excel. Now we needed to take that “database” and put it into an actual database — something like MySQL or SQL Server or Oracle.

The decision to switch from Excel to SQL Server

Data abstract
Photo by fabio on Unsplash

Excel tends to “play better” with Microsoft products, so we were not keen to use MySQL.

Eppert Consulting had previously completed a gargantuan forecasting project for a German company whose preferred database was Oracle. There are several intricacies involved in connecting Excel to Oracle, more so than with SQL Server.

The accountancy firm was comfortable with Microsoft products, and so it was decided that we would connect Excel to SQL Server.

That’s when the work began.

If I were to detail for you every step of the project’s life cycle, this article would end up being a book. The entire process was both fascinating and rewarding.

Excel is a powerful tool, but combined with SQL Server as its back-end data provider, it is without a doubt the most effective tool any company can have in its arsenal. It can do everything from creating quotations to running sophisticated forecasting software which increases sales.

The project, looking back at it, feels now almost legendary in its scope, even though it did not feel this way at each step of the process.

Here are some details of what we did.

The initial data import from Excel to SQL Server

Arrow
Photo by Nick Fewings on Unsplash

Although both Excel and SQL server have built-in tools for importing and exporting data, I would not recommend using these for a project with so much data.

The task is best done with VBA programming.

Once the data has been imported, it well behooves the programmer to spend as much time as is necessary verifying the correctness of that imported data.

This step cannot be undervalued. There is so much potential for minor importing errors which could have snowball effects later on. You will save yourself much debugging time if you verify the data fully and completely at this stage. A fresh set of eyes helps. Excel formulas and some direct VBA programming checks are also useful.

Daily importing and exporting from Excel to SQL Server and vice versa

Once the data has been imported into Excel, it needs to be pulled back into Excel in order to work with it.

Nothing beats Excel’s myriad functions for working with data. The accountancy firm was keen to move the data to SQL Server, but dropping Excel as an accountancy tool was never in question.

There are several ways to regularly export and import from SQL to Excel, and vice versa. The most common one (although we do not recommend it) is to open up a connection to the SQL Server using VBA code, pull the data, close the connection, and keep doing this for each transaction.

This is inefficient.

Increasing Excel’s speed was imperative

Long exposure of cars driving at night, suggesting speed
Photo by Marc-Olivier Jodoin on Unsplash

Speed was a key factor in deciding to move from Excel to SQL server, and constantly opening and closing connections would slow the software down due to the enormous amount of transactions this company was performing a minute.

We recommend pulling the data from SQL Server into a hidden workbook in Excel using VBA programming. Do this when the program first opens up, then periodically refresh the data to maintain consistency.

There are fine details on how this can be done without data conflicts, but that is beyond the scope of this article.

The key thing is to “flatten” the data. Excel is worked with most easily when all the data is on a single sheet. SQL Server is the exact opposite. By the very definition of a relational database, best practices in SQL Server dictate that one must separate the data into tables, and relate them one to the other. (The technical term for this is “normalising” the data.)

These two paradigms are in direct conflict with each other.

Interfacing between Excel and SQL Server

Markus Spiske at Pexels

If you wish to use Excel with SQL Server as a back-end, you will need to provide an interface between the two systems which “flattens” the data so that it is mostly in one sheet in Excel. To send data to SQL Server, you need a reverse system which “normalises” it into separate tables on the way back.

This might seem like quite some programming, and it can be. But once the initial elements are in place, the code can be re-used for all data transfers between Excel and SQL Server.

Excel is the boss in an accountancy firm. It is unlikely that you’ll end up using even a tenth of SQL Server’s many features. When connecting Excel to SQL Server, the programming task is made easier in the long run by doing the majority of work in Excel’s VBA programming language. This makes future debugging easier.

Further Excel integrations

But it didn’t stop there. By the time we had completed the bulk of the Excel to SQL Server integration, the tool had reached a stage of such value that the company requested further modifications to it.

Each of the modifications was small, and took very little time, comparatively, to implement. But looking back at the completed product, I am still amazed at how much we achieved. I am also amazed at how unbelievably flexible Microsoft Excel can be when it comes to connecting and integrating it with other tools and software such as Sage, Adobe Acrobat, Amazon Web Services, etc.

Here are just a handful of the many integrations Eppert Consulting programmed into this mammoth and invaluable piece of Excel accounting software.

Integrating Excel with Outlook

Email
Miguel Á. Padriñán at Pexels

One of the first requests was to integrate Excel with Outlook — preparing a client’s tax return, for example, and then sending the return to them in a PDF format using Outlook.

We did this in the Visual Basic for Applications programming language, of course. Advanced tasks such as these can only be done using Excel’s programming features.

We ended up programming many integrations between Excel and Outlook. Here are some of the features that were required for each or all of those integrations:

  • Correct formatting of the prepared Excel document so it looked professional when exported to PDF.
  • Automatically emailing it to an email address associated with the client (stored in the underlying SQL Server database).
  • Various email templates which could be edited by the client.
  • Email templates automatically selected when clicking the “Send” button, based off options filled in, in the Excel spreadsheet.
  • Bulk emailing of attached financial figures (either in Excel or in PDF format) utilising an underlying data store.
  • A sophisticated document approval process, resulting in a completed document finally being sent out by email once it had been verified and checked at various points.

Connecting Excel to bulk-emailing and SMS software

Hand using mobile phone
JÉSHOOTS at Pexels

The company uses a bulk emailing service, independent of Microsoft Outlook. It also uses a bulk SMS service which requires messages to be uploaded to a third-party company’s website in a specific format.

We programmed a feature to create these messages with one click, formatting the upload-file within Excel itself, using VBA programming code.

Importing banking data into Excel — integration with HSBC Connect, RBS and other financial institutions

Safe deposit boxes
Photo by Tim Evans on Unsplash

One of the most ambitious tasks we had was to import financial transactions from HSBC, Royal Bank of Scotland and other financial institutions into Excel.

Excel also had to export payment data directly to these financial institutions.

Each institution had its specific way of accepting data, as well its own security practices. We programmed several features so that Excel could both receive and send data for thousands of bank accounts, for which this accounting company had a mandate.

Programming Excel to work with Amazon Web Services

The company has produced several hundred-thousand documents over the years. Amazon Web Services was chosen as a secure location to store these documents.

The Excel tool needed a function where it could directly upload generated documents to Amazon Web Services’ Simple Storage servers.

To achieve this, we had to program an external tool in Microsoft’s .Net programming language, because Amazon did not provide an interface for Excel.

Well, we did it, and we also synchronised the data for the uploaded documents with our local SQL Server database so that we always had a local record with which to compare Amazon’s data to ours.

We also maintain separate backups of all documents. The Excel software takes care of placing each copy of the document — backup and original — where it belongs.

Connecting Excel to PHP, MySQL and Microsoft .Net websites

User on tablet
Pixabay at Pexels

Meanwhile, web technology continued to progress at a staggering speed. The accountancy firm’s clients requested more and more that their documents be available to download online.

To do this, it was necessary to create a second online database to store information regarding each user’s documents. We had to program functions to:

  • Notify users of their generated documents.
  • Ensure that only authorised users could download documents.
  • Upload documents into a secure location where they could be downloaded by authenticated users.

We also programmed a feature where clients could approve or disapprove financial transactions using an online platform.

We used a MySQL database to store data because the firm already had a PHP website in place.

This eventually led to a Microsoft .Net website, an additional SQL database, on and on and on…

The project kept growing, each time adding value to the firm, and improving its ROI.

Excel can connect to anything — anything at all!

There was never any dearth of ideas, and the software kept growing. Each new feature requested was a challenge, and I am happy to say that Eppert Consulting rose to meet those challenges.

No matter the request, we found a way to achieve it. This speaks more for Excel’s versatility than to our programming skills. Although an in-depth knowledge of Excel and all its potentials is absolutely necessary to come up with solutions to unusual problems — to think “outside the box.”

How to Create a Forecasting Tool in Excel That Actually Works

Laptops on desk and people writing on paper.
Photo courtesy of Unsplash

Theoretically, Excel contains built-in tools for creating forecasts. Unfortunately, the real world is a little different.

No, it’s a lot different.

I say this as a professional who has provided Excel services since 1996, and created dozens of forecasting tools–both complex and simple–for a multitude of clients, spanning a wide variety of industries.

I am the first to laud how vital Excel is for any business, but on the subject of business forecasting tools and financial planning tools, I am the last to recommend relying entirely on its built-in functions.

If you really want an Excel forecasting tool that puts your business on the speed highway to success, you have to piece one together yourself using a combination of Excel’s features.

1. Raw Data is required for forecasting in Excel

numbers in spreadsheet
Photo by Mika Baumeister on Unsplash

First and foremost, you need raw data–the numbers, the dates, how much income was made, how many sales were made, how many leads the company obtained in a set period of time, etc.

Forecasting is not only about money in, it’s also about how many deals are in the pipeline, how many quotes have gone out in the last week or month. Good Excel forecasting is about percentages of deals that were closed compared to the number of quotes that went out. It’s about how much promotion went out, and what kind. How much internet advertising was done? How much offline marketing was done?

Data, data, data!

“We are moving slowly into an era where big data is the starting point, not the end.” – Pearl Zhu, author of the “Digital Master” book series. (source)

2. Use predictions when forecasting in Excel

graphs on computer screen
Photo by Luke Chesser on Unsplash

Technically speaking, this also falls under Raw Data, but it is not data from the real world as such. It is predicted data.

The problem with Excel’s built-in forecasting capabilities is that they are far too automated. Microsoft doesn’t know your business. You know your business.

Just as you had a sheet which accepted all the raw data for your business in the last months or years, now you need a corollary sheet that will accept predictive data. This will most likely be in the form of percentages, for example, “We estimate that sales in Department X will increase by 10 percent” or “Promotional costs will go up by X amount in the next quarter, cutting profits by Y.”

The predictions are as varied as there are different types of businesses in the world. They’re up to you and your team to establish.

It can get pretty intricate. But sometimes it looks more complex than it really is. The trick is to establish what forecasting data is important and what isn’t.

It’s too easy to get carried away in Excel, buried underneath endless reams of raw data. Always try err in the direction of simplification, not more complexity. Pick the key data first, and build on from there as your tool becomes more involved.

3. Excel forecasting in action: Formulas

Pen pointing at charts on paper
Photo by Lukas from Pexels

It is impossible to list out every single formula that would be of use to you in connecting the raw data with the predictions, resulting in the actual forecasting data.

Excel has over 475 formulas in its functions library. (I think I’ve used every single one of them at least once, to be honest.) It’s impossible for anyone to know them all, unless they work with Excel full-time.

The VLOOKUP function is invaluable.

Try and avoid overly complex or “black box” functions. It is important that you, the business owner, know why and how a computed value exists on the sheet.

This part is really easier than it might initially look. It does take someone with a fair amount of experience in Excel to pull it off. Like art, you have to know how to do the complex things before simplifying them, or dropping them entirely. I could tell you that all you needed was a combination of the SUM, SUMIF, COUNT and MATCH formulas to get a good forecast.

But it’s a little more involved than that.

The key thing to know is: Formulas will take you from the Raw Data and Predicted Data to the Final Forecast in Excel.

Here are some tips when using formulas, based off what we see too often here at Eppert Consulting, when improving on existing Excel Workbooks:

  • Separate the formulas! Don’t try do it all in one long, endless formula. It gets too confusing. Rather split the formula across several columns to make debugging the formula easier in future.
  • Err in the direction of simplicity.
  • If you have too much data and the workbook becomes too slow, consider using the INDIRECT formula. This is an advanced subject and should be used with caution.

4. The charts

charts and keyboard
PhotoMIX Ltd. at Pexels

Lastly, you will want to add some charts to the sheet. Like everything in Excel, there’s the quick and easy way to do this, and then the intricate, exquisite way.

One really does need in-depth knowledge to make the final presentation something to write home about.

But Excel has also been designed for people who are not experts at it, and one can go a long way with its built-in wizards and tools to create great-looking charts and graphs to present at a meeting or to a potential client.

5. Other tips and tricks for forecasting in Excel

people sitting with computers at table
fauxels at Pexels

When forecasting in Excel, it’s easiest if you group your articles and apply predictions to each group, rather than applying those predictions to everything in bulk.

Another way to forecast is what we call the “top down” approach. This is more of a “Production Target” approach. You enter the raw data. Then, instead of typing in predictions, you type in a target for each group, such as “An increase of 10 percent in sales next year.”

Then you work backwards, working out what is needed in the various sub-departments and zones to achieve that goal.

There are many ways to skin this cat called Excel. It really is a fascinating piece of software with endless potential.

Making sense of data is our business

We have extensive experience in creating Excel forecasting tools. The most complex tool we created covers every phase of forecasting from acquisition to sale for a multi-million-Euro-per-annum retail corporation in the clothing industry.

The raw data can get complicated very quickly, even for small companies. In a sense, it’s even worse for small companies, because there are less people to take the load off and spend hours analyzing that data. If you’re a one-man show, likely you would just give up on forecasting altogether and simply go with your gut. Unfortunately, this is perilous in the long-term.

Making sense of data is our business–we work day in and day out with data and know how to spot what is important and what isn’t. We can usually figure out the best way to arrange the underlying raw data in an Excel workbook so that the resultant Excel forecasting becomes easier to do.

If you need help with any phase of your forecasting tool, give us a call, or send us a mail using the form below. We would be happy to help you.

Contact Us

 

Create Quotes in Excel Which Close the Deal

Man preparing a quote manually
Photo by Helloquence on Unsplash

There is no better software for creating quotes than Microsoft Excel. Yet the intricacies of Excel can be daunting for anyone not familiar with the program.

“It’s all about the presentation.”

It’s the simplest rule in Marketing. If you don’t “wow” your potential client, they’ll go somewhere else. And wowing the potential buyer has little to do with price.

In a study conducted in 2012, Business Weekly reported that “New research suggests that low prices can backfire for retailers since consumers sometimes see cheap prices as a sign of a cheap product.”

No, you wow your client with great value for money, unbeatable quality, and superior service. But how do you even get them to sit up and listen, to actually look at your proposal?

“New research suggests that low prices can backfire for retailers since consumers sometimes see cheap prices as a sign of a cheap product.”–Business Weekly

Too often we hear from clients that their in-house CRM or Accounting software–everything from Sage to massive SAP applications–simply doesn’t cut the grade when it comes to creating professional quotes which bring the deal home.

Either the proposal it creates looks “boring” or bland, or it simply isn’t customizable enough to make it look unique. 

The other rule of Marketing: You have to stand out

Professional clients want value for money, not necessarily cheapness. To close the deal, you need to stand out from the competition with your proposal.

Your only choice is to spend hours upon hours putting together a quotation for that “One Big Client,” neglecting all the other quotes that are being demanded of you–quotes which themselves also need to be professionally done.

It’s the endless conundrum of running a successful, expanding business.

Stressed out woman sitting at laptop
Photo by energepic.com from Pexels

Creating an Excel Quoting Tool is the way to do it

We have been creating customized Excel solutions for over twenty years–everything from Day Trader Excel Tools to an Excel Payroll Tool to, yes, a sophisticated and easy-to-use Excel Quoting Tool. Nothing comes close, competitively, to what Excel can do for a business. It is a phenomenal product which only keeps getting better.

Like all “good software” out there, Excel’s true potential is only really seen when one knows how to use every part of it. And that task can be daunting.

Here at Eppert Consulting, we are constantly amazed to discover that even some accountants don’t know all of Excel’s potential tricks and tools.

An Excel Quoting Tool is the easiest thing in the world to create–if you know Excel. It can be done in only a matter of hours for a simple tool which would generate professional quotes in seconds. The quotes would be designed to your specifications, then exported to PDF or Microsoft Word.

Steps to create an Excel Quoting Tool

1. Make it easy to input data

Excel has inbuilt tools to prevent user error. The trick to fast and professional quoting in Excel is to make the user-input almost automatic.

You can lock cells so that users don’t mistakenly overwrite key formulas and values.

You can colour-code input sections–locked cells one colour (such as grey), input cells another colour (such as light green).

One spectacular feature of Excel is conditional formatting. It’s really quite difficult to overpraise this feature, especially in quoting tools. You can have a hidden sheet which compares this quote to older quotes or, say, to a median value. If you’re quoting too high or too low, the cell could suddenly turn red to warn you.

The goal is to think as little as possible when crunching the numbers, and reduce human error completely. The ideal scene is to type in your values, click a button and export to a gorgeous PDF which impresses the heck out of your potential buyer, and knocks over all of the competition

If you really want to get sophisticated, you could design a user-form to capture your data and insert it into an underlying sheet. But this is really getting advanced. (We can help you here, if you wish.)

Laptop with Excel-style dashboard open on screen

2. Design a spectacular quote template

This is where you wow your potential client. The layout of this document needs to be visually appealing. It needs to catch the eye, direct it, impress.

It can all be done in Excel.

This is not the same sheet into which you input your values!

A lot of people make this mistake. Ideally, your input screen has no fancy bells and whistles. It contains boxes (cells) with easy-to-read labels, and clear instructions if necessary. It also contains conditional formatting and even built-in Data Validation to verify the data.

The input screen is the inner workings of the vehicle. The final quote, the PDF you receive after clicking the button called “Create Quote,” is the vehicle’s body. And you want that body to be a sports car.

Sports car

3. Lastly, use VBA code to make it sing and dance

“VBA” stands for “Visual Basic for Applications.”

Visual Basic is a programming language built right into Excel, the “Application.” You can view this programming language in any Excel file by pressing Alt + F11 together on your keyboard.

This is an advanced subject, and it’s really what makes Excel stand out above all other similar tools out there.

You can do almost anything with Excel VBA.

For example, using VBA you could:

  • Store all your past quotes in a database.
  • Search for old quotes by client, date, cost, subject, etc., and import them into a new quote.
  • Automate much of the number crunching.
  • Have the code manipulate the final PDF’s look in tiny ways which aren’t possible with Excel alone.
  • Import quoting details from external software such as Sage, Salesforce, or whatever software you are using in your company.

This is really where Eppert Consulting shines. I say that with no conceit or lack of humility. I say it merely as a fact. This is our forte: Creating Excel tools that go beyond the usual, beyond the norm, using Excel VBA coupled with Excel formulas and beautiful templates, which are a piece of cake to use.

Anyone can “Record a Macro,” but to transform Excel from a mere Volkswagen Beetle into an Audi RS7, you have to know how to program.

Summary

Still, even without VBA or a Macro, the simplicity of the steps is:

  • Input the data in one sheet, replete with locked cells, conditional formatting, data validation and all the usual built-in Excel tools.
  • Suck in the data using formulas into another presentation sheet which you can then save as a PDF.

If you’re looking for more advanced features, like VBA programming, you can give us a call or send us an email using the form below so we can help you.

Contact us