Random sample data generator add-in for Excel now available
February 6, 2009 • JP • 39 Comments • Rate This Article
• Links to this article
As anyone in the security or financial business can tell you, data security is extremely important. Data in the wrong hands can cost a business millions of dollars, its reputation, and even it's very existence.
In The Dangers of Testing with Real Data, Mathew Schwartz writes:
Do you test applications using real, production data?
Anecdotally, many developers and QA testers say they prefer to build and test applications using the real thing: actual customer data.
Such practices, however, can violate a number of data privacy regulations. For example, the 1996 Health Insurance Portability and Accountability Act (HIPAA) mandates companies restrict access to people’s personal health data on a “need to know” basis. Likewise, the Sarbanes-Oxley Act of 2002 requires companies to control access and track changes to systems handling corporate financial information. In addition, over 30 states have passed data breach notification laws requiring companies to notify consumers if their personal information may have been compromised. This includes such things as a person’s name and address, date of birth, social security number, and credit card and bank account numbers.
So, the problem is, how can you get your hands on quality sample data that won't violate privacy regulations?
There is a limited amount of sample data available here:
Excel — Sample Data
Excel Sample Data
I'd like to announce another Excel add-in I've just completed — bringing the total of add-ins available on this site to five — one that lets you generate your own random sample data for testing purposes!
Suppose you need some fake SSNs, names, birthdates, etc, in order to test out a macro, UDF, PivotTable or PivotChart. This add-in is for you.
I was inspired by Fake Name Generator's "order in bulk" feature (but most certainly not by their huge wait time for a bulk order) to create an Excel add-in to duplicate their functionality. Now you can generate as much fake random sample data as you need, from the privacy of your own computer, in a fraction of the time it takes to either crawl the web piecing it together yourself or placing an order with a website and waiting for your turn in the queue.
Screenshot:
You can select from the following fields:
- Address – a randomly generated street address
- AutoNumber – similar to the Access feature that automatically numbers each row of data
- Birthday – a random date between 1933 and 2007
- City – a random city name based on a list of real city names
- Company Name – a random company name based on a list of fake company names
- Email address – a nonsense string of random made-up characters with '@' and '.com' delimiters
- First Name – randomly selected from actual first names
- Last Name – randomly selected from actual last names
- State – randomly selected actual state abbreviations
- Gender
- SSN
- Telephone
- Zip
Keep in mind that all of the data is dynamically generated each time Submit is pressed, so if you select City and State, for any given row they won't necessarily match, since they're selected independently (i.e. you might get "Anchorage, KS" and "New York, TX"). The data for Telephone, Zip, SSN and Phone Number are randomly generated so they won't even closely represent actual data. But that's a good thing. Also the gender won't match the name — random!
The Random Data Generator add-in adds a menu item to the Data Menu titled "Random Data Generator 1.0" (The hotkey combination Alt-D-R will get you to the userform quickly). In my tests, up to 10,000 records took about 2 seconds, while 50,000 took significantly longer (about 30 seconds). The records are placed in a new workbook which is saved onto the users' desktop. [Update 2/11/2009: Saving is now optional. --JP] This add-in should work in Excel 2000 through 2003 (there is a separate add-in for 2007, and while I don't have a copy of Excel 2007, I'd love to hear from anyone who installs the add-in in their copy and reports on its performance).
Credit is given to the following sources:
- First names were taken from 2000s Baby Names and other related pages on the same site.
- Last names were taken from Census Data
- Fake company names from Fake company names
- Street names were taken from a page listing the most popular street names
- The code to shift items up and down in the right listbox was taken from: Move Up/Move Down in a ListBox
And also to John Walkenbach's Excel 2000 Power Programming with VBA for the usual startup routines I use in all my add-ins.
I've decided to release the add-in with one minor bug — moving an item up or down in the right listbox changes the selected item focus. Unfortunately with a multi-select listbox, the item that has focus and the item that is selected can be two different items! So if you need to move something up or down more than one time, you'll need to reselect it repeatedly. The functionality was working, but for some reason now it isn't. The best way to avoid the problem is to add items one at a time in the order you want them to appear; that way you won't need to rearrange them. I plan to fix this sometime in the future in an updated version. [Update 2/11/2009: Dick Kusleika from Daily Dose of Excel posted a listbox fix that solves this problem. --JP]
Links removed due to technical issues. They will be back shortly.
Questions, requests or suggestions? Let me know in the comments.
(2 votes, average: 5.00 out of 5)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Shipment Tracking Add-in Update
Next Post: I made it into the EMO Newsletter
Very neat! Do you mind if I link to this page from the Free Tools page at the Fake Name Generator?
I tried it in Excel 2007 and got the following error: "Run-time error '1004': Add method of AddIns class failed". I'd be happy to help you troubleshoot it if you'd like, let me know..
Absolutely — and thanks for testing it, I just realized I made a real bonehead move with the 2007 version, which I'll be fixing right away.
Ok, I just realized without Excel 2007, I can't debug the error, so I'm just going to remove the 2007 version for now. If you want to look at it, let me know and I'll send you the unlocked version.
Email the 2007 version to me if you like. I'll take a look at it.
This seems like a great idea. Should know I could use it.
To fix the moveup/movedown problem
http://www.dailydoseofexcel.com/archives/2004/06/14/move-upmove-down-in-a-listbox/#comment-37847
and get rid of all the selecting and sendkeys stuff. It looks like ListIndex is all we need to make it work right.
I had no problem installing it in Excel 2007 (maybe because you sent me the xlam instead of the installer). Ten thousand records took ~2 seconds, fifty thousand took ~12 seconds.
Suggestions for v2:
I can't run it unless I have a workbook open, but then it doesn't use the open workbook. I would prefer it just populated the open workbook or created a new open workbook for me to save. I'm very protective of my desktop.
Disable the up and down arrows when they're not applicable.
Enable the Submit button based on the Change event of the textbox, rather than AfterUpdate.
Thanks, I'll check out that code and probably re-release before the end of the week.
Great utility … and thanks for leaving the unlocked version, it was extremely handy to pop in a couple extra simple things I needed (random name suffix and middle initial generation functions)!
You're welcome, and I'll add those to my to-do list for the next version.
Just needed to create some data so I could make a pivot table and test something out. I can't believe I used to do that manually.
Tell me about it. I did the same thing for a couple of my tutorials. Works very fast!
I am a senior Mathematics teacher and I am always trying to invent math scenarios using data tables that one would see in real life applications. I stumbled on this Add-In of yours and I am eternally grateful for your work. This saves me from having to write my own VBA scripts, which should be beyond the levels of a simple math teacher.
Again, thanks I really love this add-in.
Glad to hear it, although I would disagree with you about VBA being beyond anyone's skill level.
Hi JP:
Could you please send me updates version of Random Data Generator 2007.
I am working on similar project and your excel add on would be great help.
Version I have downloaded has some error, and while opening it gives me following message Runtime Error 1004.
Your help will be appreciated.
kalpeshmunot@gmail.com
Kal
Thank you for this add-in.
I'm not sure how to edit the unlocked version, so I didn't do anything with that.
I have a question though – how can you change the information that the zip codes use? I'm in the UK, and our postal codes are completely different.
And it just so happen that the software I'm busy testing now requires UK post codes.
Thanks
Aitch,
If you post a sample UK postal code, I can edit the add-in so it returns UK Postal codes instead.
I have tried the add on, but it's not working on Excel french version!
God luck
Dalven,
What specifically isn't working? Are you able to install the add-in? Did you download the correct version for your version of Excel?
Love this, I generate test data for developers and this addin has inspired me to take a stab at doing some coding (old old Cobol developer here).
Thanks much for the example.
FYI, Neither of the Excel 2007 zip files will unzip using Winrar, 7zip or Winzip. Not sure what you used but something is messed up.
Thanks for the 2003 code though. I was able to work with it.
I'm confused as well. I can open my local copies, but as soon as I upload them to the site, they can't be opened. I can email them to anyone who asks.
can you send both of the 2007 versions to me?
Hi JP,
I'd be grateful if you'd email me a copy of your Random Data Generator Add-In – Excel 2007 unlocked version. I tried to download it and it gives me an error when I try to unzip it.
Thanks.
Could you visit http://www.codeforexcelandoutlook.com/blog/add-ins/#rdg and try to download it one more time? I fixed the archive, hopefully it should work. If not, let me know and I'll email it to you.
Thx!
Thanks, but I'm still getting the following error:
The Compressed (zipped) Folder is invalid or corrupted.
An email of the file would be great.
Hi, i am also getting the error with the zip for 2007 version.
can you please fix uploaded file or email me the file.
thanks
JP,
You wouln't have a tool that would allow me create a gender code from first name would you.
This would be used in Excel 2003.
Rich
Sorry, no. One solution is to create a table with first name and gender and then use something like VLOOKUP or INDEX/MATCH to return the gender based on the name.
Dear buddy,
I am in deep trouble and need your help, don't sho me away please. Problem is I work in data entry, and have to enter the advanced data as well as the resultant data.
Can you give me or guide me to a tool that would open .dat cobol file in excel and after wards save the data in the same .dat format.
Thanks.
Please read the comment policy. If you can't find a related post, contact me privately instead of just commenting on any post.
I don't know any method for accomplishing what you want. You need to figure out what program was used to create the .dat file, and see if it can output the data in a format that is readable by Excel. For example, a .txt, .csv or .xls output format.
Or, find out what program outputs the data you want, and see if it can output the data in a suitable format.
Do you know how to make a program where you would be able to enter in 3-5 criteria. Then have excel choose items from a number of different categories and compile them into a list?
That description is pretty generic, but it sounds possible. Can you be more specific?
Im in Australia, I need postal codes and city names from Australia (Validation rules apply). Everything else I can work with.
Download the unlocked version of the add-in and edit it to suit your needs.
This rocks. You rock for making it. Sweet.
J.P.,
random name generator:
I do not write excel or basic language at all. I just downloaded the addin and clicked on the xla document and allowed it to open and get this error.
run time error "1004"
add method of addins class failed
then if you run debug this comment shows up highlighted
" AddIns.Add Filename:=ThisWorkbook.FullName"
So, then I opened excel 2003 and went to file open and navigated to the xla document and clicked on it and i get a message that the addin was successfully installed but it opens a series of error windows basically asking if you want to run debug and if you do agree you get a set of messages much akin to this
AddIns(AddInTitle).Installed = True and on line nine and so many messages that I do not have the time to write. There is very little user interaction on my part other then trying to see if it will work better then the other websites on version.
thanks
Mark