Getting started with Google BigQuery and GDELT Project

Getting started with Google BigQuery and GDELT

          Once upon the time, the new kid on the block left more established search engines in the dust, then, after reinventing web-based email service, Google introduced its Apps. Today, let’s talk about one of the myriad services Google offers to us: BigQuery. Basically, this cloud-based service allows us to utilize Google’s hardware to store our own datasets or access public data on the go. Google provides API for Java, PHP, and Python access. In addition, various third-party tools now connect directly to BigQuery: Tableau, R, JasperSoft, and Simba to name a few. We get a 1 TB monthly usage quota to query BigQuery’s data for free. Some of the downsides of this service include: premiums for storing our own data and querying in excess of the free quota. We are also limited with data manipulation tasks we can perform in BigQuery; in fact, we can only append records to our table, we cannot update or delete them. Finally, this service uses a SQL language dialect, which lacks some of the SQL commands we are accustomed to: DISTINCT comes to mind, or resort us to some convoluted workarounds (try using the TOP command.) Meet, the GDELT Project – “the largest, most comprehensive, and highest resolution open database of human society ever created.” In this tutorial, we will learn some interesting facts about different countries, using GDELT data in BigQuery.

Continue reading

Where are the jobs? 2014 INC 5000 List might provide some answers…

2014 INC 5000 data viz

          What do Domino’s Pizza, Microsoft, Timberland, Tough Mudder, Intuit, Vermont Teddy Bear, E*Trade, Lending Club, Morningstar, Oracle, Fuhu, Cold Stone Creamery, Under Armour, and GoDaddy have in common? They all have appeared on Inc Magazine’s INC 500 list of the fastest-growing American private companies. Looking at the last year’s INC 5000 list might help us draw employment insights and identify best industries and places to look for a job. By definition, these company are experiencing tremendous growth, and as such, could be representative of future job opportunities in the US. In addition, according to Forbes , small businesses added over 65% of the net new jobs in the past two decades. So, where are the jobs?! Based on INC 5000 data, Chicago, IL employs over 5% [56,813] of all INC 5000 workers. Another 14% [144,847] of INC 5000 employees work in California. Top 3 industries by employment include: Human Resources, Business Products & Services, and IT Services . Together, they are responsible for almost 400,000 jobs, or 38% of the grand total.

Continue reading

Happy New Year! Highlights of 2014.


Happy New Year - 2015!

          2015 is quickly approaching, and I wanted to share the brightest highlights of 2014 with my readers. All of the events are listed in chronological order:

         #1. First blog post , on Income Tax Rate inequality in US published on August 27th.

         #2. First influencer follower on Twitter – @Modeloff . World Financial Modeling Championship followed me on Twitter on October 27th. They subsequently retweeted my solution to the 2013 Modeloff Data Analysis problem.

         #3. Official Twitter account of my favorite software application – @MsExcel (if you really have to ask) , followed us on December 10th.

          The opportunities are now endless. Who knows, perhaps they will grant my New Year’s wish:

         #4. One SlideShare presentation viewed 1,000 times , My presentation on the best AT&T plan for your iPhone6, reached 1,000 views on December 16th. All four of my presentation have accumulated 2,000 views on the last day of 2014…

         #5. Like on LinkedIn update from a real-life Excel MVP , Jordan Goldmeier – Excel MVP of @OptionExplicit and Cambia Factor fame liked my LinkedIn update on working with nested if functions in Excel on December 26th.

          Have a very happy, healthy, and prosperous 2015!






Excel Formulas vs. Functions – Auto Loan Calculator (PMT)


Car Loan Calculator using PMT Function in Excel

         If you are like me, you are using terms Excel Formulas and Functions interchangeably. However, these expressions represent distinctly different concepts. We can think of a formula as a statement that returns results of a certain user-defined calculation. Formulas can contain values (numeric, date, text,) cell references, ranges of cells, named ranges; they can perform any mathematical operation (addition, subtraction, multiplication, division, exponentiation,) as well as evaluate results of Excel’s built-in functions…A function is a predetermined formula that helps us simplify complex procedures and operations. For example, great folks at Microsoft programmed different functions that allow us to sum a group of numbers, search for a specific word within a text string, determine current date and time, or even figure out amount of our loan payment. Ability to construct complex Excel formulas , using multiple Excel functions is one of the prerequisites for becoming an Excel expert. Formulas always start with the equal sign ( = ), and we always enclose functional arguments (if any) inside parentheses.

          Let’s suppose that you need to calculate auto loan payment for your dream car – 2015 Ford Mustang GT. According to TrueCarPrice , for the base V8 model, you should expect to shell out $ 32,864 of your hard-earned dollars for this car; if you reside in the state that Ford Motor Company calls home. This price is comprised of $ 29,612 true value estimate, $ 567 market premium, $ 825 delivery charge, and state sales tax. According, to Bank Rate, you can realistically finance this purchase with a 3% interest rate loan. Since you just received your Holiday bonus, you want to put it towards a $5,000 down payment on your new car. Let’s calculate your monthly car payment using both: formula method without utilizing Excel functions: =P*i*(1+i)^n/((1+i)^n-1) , as well as through a formula incorporating Excel’s PMT function. Both of these methods rely on formulas to perform the necessary calculations, but only the second method makes use of an Excel function.
Continue reading