Excel Function to Calculate Travel Time and Distance using Google Maps API with VBA

In this article, we will create an Excel function to calculate the distance between two addresses using the Google Maps directions API. This will allow you to get the travel time between the two locations. The format of the function will be as follows: =TRAVELTIME(origin, destination, api_key), =TRAVELDISTANCE(origin, destination, apikey). The origin and destination will be strings, and can be either an exact address or the name of a place. In order to use the function, an API key is required. The “Getting Started” page can help you with this: http://bit.ly/googlemapsgettingstarted. Create a new project and make sure the Directions API is added.

Continue reading “Excel Function to Calculate Travel Time and Distance using Google Maps API with VBA”

Excel Email Validation with Data Validation Tools

If you have a list of email addresses in Excel that you need to validate follow proper format, or would like to prevent other Excel users from entering invalid addresses in a column, use Data Validation. By using a custom formula as the validation criteria, email address validation can be done. To get started, with the cells you need validated selected go to Data > Data Validation.

Continue reading “Excel Email Validation with Data Validation Tools”

How To Use Excel Data Types and When You Might Not Want To

Microsoft recently introduced a feature called “Data Types” into Excel 365. Unlike the regular Excel data types like Dates, Numbers, and Text, this feature relies on the internet to bring external data points into your Excel sheets. They call them linked data types. The implementation is a lot easier to use than creating a query or using VBA to bring data to Excel, but it’s pretty limited in its current form and probably wouldn’t be very valuable to most users. Right now, it’s limited to Stock and Geography data.

Converting a set of stock tickers to the Stocks data type.
Continue reading “How To Use Excel Data Types and When You Might Not Want To”

BeautifulSoup 4 Python Web Scraping to CSV Excel File

In this tutorial we do some web scraping with Python and Beautiful Soup 4. The results are then saved to a CSV file which can be opened and analyzed in Microsoft Excel or another spreadsheet program. I show you how to select elements from the page, deal with 403 Forbidden errors by faking your user agent, and overcome cases where the website is poorly laid out for web scraping. The example used here is SocialBlade.

Continue reading “BeautifulSoup 4 Python Web Scraping to CSV Excel File”

Import JSON Data in Excel 2016 or 2019 or Office 365 using a Get & Transform Query

If you have data stored in JSON format that you would like to import into Excel, it’s now very easy and doesn’t require any VBA to import data locally from the disk or from a web API. Watch the video or read the steps below!

Continue reading “Import JSON Data in Excel 2016 or 2019 or Office 365 using a Get & Transform Query”