Posted: February 18, 2016 by MaxFraudy in Others
We are looking for a freelancer who has prior experience in development. Can’t disclose much about the project at this stage. Timelines & enumeration can be discussed and agreed upon mutually.
If you can help or know of someone who can, please do write to firstname.lastname@example.org or post a comment here.
Posted: July 5, 2015 by Transformer in Excel, VBA
Tags: Advanced, JSON, UDF
Reverse geocoding is the process of taking a set of GPS coordinates and turning it into the nearest available address.
Browsing through the net, I found VBA code on many websites to get GPS coordinates(latitude & longitude) from a location address but found it really hard to get a reverse geocoding in VBA.
It can be achieved using following Google API:
It will return JSON, that can be parsed to get the nearest available location.
Function GEOAddress(dblLatitude As Double, dblLongitude) As String
Dim strJSON As String
Dim strAddress As String
Dim lngTemp As Long
Dim objXml As Object
Dim strUrl As String
strUrl = "http://maps.googleapis.com/maps/api/geocode/json?latlng=" & dblLatitude & "," & dblLongitude & "&sensor=false"
Set objXml = CreateObject("Microsoft.XMLHTTP")
.Open "GET", strUrl, False
strJSON = .responseText
Set objXml = Nothing
lngTemp = InStr(1, strJSON, "formatted_address")
strAddress = Mid(strJSON, lngTemp + 22, InStr(lngTemp, strJSON, """,") - (lngTemp + 22))
GEOAddress = strAddress
Posted: January 6, 2015 by MaxFraudy in Excel
Tags: Basic, chart, Custom Chart, Custom Format
It’s no secret that I’m a big fan of beautiful data visualizations. What may not be so obvious is that I’m huge on the KISS (keep it simple stupid) principle when it comes to getting work done. I believe that any moron can complicate things, but it takes a genius to simplify them. (On a separate note, I often recall a quote on life from one of my all-time favorite movies – “It is very simple to be happy, but it is very difficult to be simple”)
Today I’m sharing a simple tip on how you can highlight a data-point on your chart, based on a selection, without using VBA. And the best part is that it can be used for any chart type.
I have data for average precipitation in Delhi by month (for years 2000-2012). I have placed my data in cells A4:B15. Data validation has been put in cell B2 such that it can only take month names from January-December. Now that we are set up, follow these steps: Read the rest of this entry »
Posted: December 26, 2014 by MaxFraudy in Others
Posted: December 1, 2014 by MaxFraudy in R
Tags: Basic, table
You know how learning new things keeps one’s mind agile (or so I’ve heard). Well, following this adage, I’ve recently been spending some time in trying to learn R. I find R to be very different from SAS & SQL or any other language that I have used. It took me quite a while to get used to how data is handled in R.
I’m still a beginner so I don’t see myself posting a lot of ‘useful gyaan‘ on R in near future, however, I believe other beginners may also be facing similar problems as I am. So in line with our mission to ‘make life @work easier‘, I’ll be ‘sharing stuff‘ that could be real help (at least to a novice like me).
The first in line is a cheat sheet for data.table in R (Download data+table+cheat+sheet). This is not my creation but I found it at this place and felt like sharing it. For someone like me, who tends to forget syntax this is a ready reckoner for data.table manipulations. Some examples:
Snapshot of the data.table cheat sheet
We have earlier discussed how to hide errors in a worksheet in this post. Today I’m going address another similar problem that is very commonly faced. There are various ways to tackle it and there is no right or wrong about it. I’m sharing what I prefer to use (because I find it very simple and easy to implement).
Suppose I have a data table in my dashboard that is referencing to another range. My table has 10 rows (A2:D11) that reference range Z2:AC11. As long as there is data in Z2:AC11, everything is fine.
Now suppose that I have data only till row 4. In this case, my table shows 0’s which looks ugly and I’d rather prefer those cells to be blank. How do I solve this problem?
Read the rest of this entry »
Problem Statement: I have a database of employees where I store information about each person. I wish to have a picture of the employee in my database that I want to retrieve when I select an employee ID. Solution: I am sharing a VBA code that saves the file in SQL server table in binary format. When the file is retrieved, it can be done in original format. Let’s say I’ve a table Employee, it has two columns ID (Datatype: int) and Image (Datatype: varBinary(Max)). Code to store an image as binary type: Read the rest of this entry »
Today, I’m sharing a basic but nonetheless a neat trick to make your reports more presentable. I’ll take an example to illustrate my point.
I have Life Expectancy data for various countries for years 2011 and 2012. The fourth column is calculated using data in B & C columns. The number of rows of data can change, so I have put formula in extra rows. The problem then is that the rows that don’t have data return #DIV! error in column D as below. Read the rest of this entry »
Posted: April 18, 2014 by MaxFraudy in Excel
Tags: Basic, Custom Chart, Data, Format, Free Template
This is a common requirement while creating dashboards. One selects an option and the charts update to show data for that particular selection. It would be very useful if the same chart could show you how that particular selection fares against average or a set benchmark.
Let’s take an example. I have data for a class of students with their subject marks. I make a selection of the student name, his marks for each subject are plotted on a chart. I wish to see how this particular student has performed in each subject as against the class average. This custom chart does just the same, and it can be very very easily created in Excel as below:
Read the rest of this entry »