Macro to Calculate Efficiency of Spend

This macro was used in a spreadsheet to calculate the efficiency of marketing spend across many different channels. You can add easily adapt this macro to work for your own particular calculations.
' EfficiencyOfSpend Macro
' Pending the type of metric (e.g., CPI, visits), this function will calculate whether or not you are on or off target.
'
' Keyboard Shortcut: Ctrl+Shift+I
'

 
If KpiType = "CPI" Then
 
    'If Target = 0 Then EfficiencyOfSpend = 0

    If Target > ActualValue Then
    EfficiencyOfSpend = (Target - ActualValue) / Target
 
 
    ElseIf Target < ActualValue Then
    EfficiencyOfSpend = (ActualValue - Target) / Target
    EfficiencyOfSpend = -1 * EfficiencyOfSpend
 
    ElseIf Target = ActualValue Then
    EfficiencyOfSpend = 1
 
    End If
End If
 
If KpiType = "Visits" Then
    MsgBox "clickvisitetc"
    If Target < ActualValue Then
    EfficiencyOfSpend = (ActualValue - Target) / Target
 
    ElseIf Target > ActualValue Then
    EfficiencyOfSpend = (ActualValue - Target) / Target
 
    ElseIf Target = ActualValue Then
    EfficiencyOfSpend = 1
    End If
End If
 
If KpiType = "Impressions" Then
    MsgBox "clickvisitetc"
    If Target < ActualValue Then
    EfficiencyOfSpend = (ActualValue - Target) / Target
 
    ElseIf Target > ActualValue Then
    EfficiencyOfSpend = (ActualValue - Target) / Target
 
    ElseIf Target = ActualValue Then
    EfficiencyOfSpend = 1
    End If
End If
 
If KpiType = "Clicks" Then
    MsgBox "clickvisitetc"
    If Target < ActualValue Then
    EfficiencyOfSpend = (ActualValue - Target) / Target
 
    ElseIf Target > ActualValue Then
    EfficiencyOfSpend = (ActualValue - Target) / Target
 
    ElseIf Target = ActualValue Then
    EfficiencyOfSpend = 1
    End If
End If
 
 
 
End Function
Posted in VB

Highlight Rows in a Range that Meet Some Criteria

Option Explicit
Sub highlight()
 
' SelectValues Macro
' Selects values that will become criteria for shading rows
'
' Keyboard Shortcut: Ctrl+Shift+U
'
    'First, read in the values for shading
    Dim l As Double
    Dim m As Double
 
    l = Sheets("Criteria").Range("B2").Value
    m = Sheets("Criteria").Range("B3").Value
 
   'Set up variables that you'll need for below code
   Dim c As Range
   Dim r As Range
   Dim n As Double
 
   'Input Range to be evaluated
   Set r = Application.InputBox(prompt:="Input your range", Type:=8)
 
   'On error in below loop, just keep going
   On Error Resume Next
 
   'loop through all the values in the range
   For Each c In r
 
      'convert to number if value happens to be text
      c = Val(c)
 
      'evaluates and colors rows based on your criteria spreadsheet
      If c > l And c < 0 Then c.EntireRow.Interior.ColorIndex = 6
      If c < l And c > m Then c.EntireRow.Interior.ColorIndex = 44
      If c < m Then c.EntireRow.Interior.ColorIndex = 3
 
   Next c
 
End Sub
Posted in VB

Count Unique Objects in a Range

Sub UniqueReport()
 
Application.ScreenUpdating = True
Dim dict As Object
Set dict = CreateObject("scripting.dictionary")
Dim varray As Variant, element As Variant
 
varray = Application.InputBox(prompt:="Input your range", Type:=8)
 
'Generate unique list and count
For Each element In varray
    If dict.exists(element) Then
        dict.Item(element) = dict.Item(element) + 1
    Else
        dict.Add element, 1
    End If
Next
 
'Paste report somewhere
Sheet2.Range("A1").Resize(dict.Count, 1).Value = _
    WorksheetFunction.Transpose(dict.keys)
Sheet2.Range("B1").Resize(dict.Count, 1).Value = _
    WorksheetFunction.Transpose(dict.items)
 
Application.ScreenUpdating = True
End Sub
Posted in VB

A Simple Employee Database in C++

This is just part of a group of classes used to create an employee database. The employeegroup class uses pointers which are used for memory management (as opposed to Java which does the memory management for you.) If for some reason you'd like the entire program, let me know and I'll send you a copy containing all the classes used. Just post a comment to leave me a message.
#include "employeegroup.h"
 
EmployeeGroup::EmployeeGroup() : EGname("BLANK"), head(NULL), tail(NULL), lastreturn(NULL), exists_head(false), exists_tail(false), size(0) 
{}
 
EmployeeGroup::EmployeeGroup(String s)
{
	EGname=s;
	head=NULL;
	tail=NULL;
	lastreturn=NULL;
	exists_head=false;
	exists_tail=false;
	size=0;
}
 
EmployeeGroup::~EmployeeGroup()
{
	enode* current = head;
	while(current!=NULL)
	{
		enode* next=current->next;
		delete current;     
		current = next;
	}
}
 
void EmployeeGroup::name(String n)
{
	EGname=n;
}
 
String EmployeeGroup::name()
{
	return EGname;
}
bool EmployeeGroup::insert(Employee e)
{
     enode *q,*t;
 
		 // Condition where neither head nor tail exists
		 if(head==NULL)
		 {
			cout << "Inserting head (first node)..." << endl;
			enode* temp = new enode;
			temp->e = e;
			temp->next = NULL;
			head=temp;
			exists_head=true;
			size++;
			return (true);
		 }
		 if (employee_already_exists(e))
			 return (false);
		 // Condition where head exists but no tail exists
		 t=head;
		 if (exists_head==true)
			 if(exists_tail==false)
			 {
 
				  if (t->e.idno < e.idno)   //insertion after head
				  {
					  enode* temp = new enode;
					  temp->e = e;
					  temp->next = NULL;
					  t->next = temp;
					  tail=temp;
					  size++;
					  exists_tail=true;
					  cout << "Inserting after head successful!" << endl;
					  return (true);
				  } else   //insertion before head
				  {
					  enode* temp = new enode;
					  temp->e = e;
					  temp->next = head;
					  tail = head;
					  head = temp;
					  size++;
					  exists_tail=true;
					  cout << "Inserting before head successful!" << endl;
					  return (true);
				  }
			 }
 
			 // Condition where head exists and tail exists
			 q=head;
			 if (exists_head)
				if(exists_tail)
				{
					if (e.idno < q->e.idno)
					{
						  enode* temp = new enode;
						  temp->e = e;
						  temp->next = head;
						  head = temp;
						  size++;
						  cout << "Inserting before head successful!" << endl;
						  return (true);
 
					}
					if (e.idno > tail->e.idno)
					{
						  enode* p;
						  p=tail;
						  enode* temp = new enode;
						  temp->e = e;
						  temp->next = NULL;
						  p->next = temp;
						  tail=temp;
						  size++;
						  cout << "Inserting after tail successful!" << endl;
						  return (true);				
					}
					//If above two conditions are not met, we insert in the middle
					q=head;
				    while(q->next != NULL)
					{
					if ( q->e.idno < e.idno ) 
						if ( q->next->e.idno > e.idno )
						{
							enode* temp = new enode;
							temp->e = e;
							temp->next = q->next;
						    q->next = temp;
			                cout << "Inserting in the middle..." << endl;
			                size++;
			                return (true);
						}
			          q = q->next;
					}
				}
				return(false);
}
 
bool EmployeeGroup::employee_already_exists (Employee e)
{
	  enode* current;
	  current = head;
	  if (current->next==NULL)
	  {
	   	 if (current->e == e)
			{
				//cout << "An employee with that ID already exists.";
				return (true);
			}
	  }
	  while(current->next != NULL)		//Loop to see if an employee with an equal ID exists
	  {		
 
			if (current->e == e)
				{
					//cout << "An employee with that ID already exists.";
					return (true);
				}
			current = current->next;
	  }	
	return (false);
}
 
 
void EmployeeGroup::display()
{
cout << "\nEmployee group name is " << EGname << endl;
enode* current=head;
cout << "The pointer for head is " << head << endl;
while (current != NULL)
	{
		current->e.write();
		cout << "The next pointer for this person is " << current->next << endl;
		current = current->next;
	}
 
}
 
Employee EmployeeGroup::get_next()
{
	if (lastreturn==NULL)
	{
		lastreturn=head;
		lastreturn->e.write();
		return (lastreturn->e);
	}
	else
	{
		if (lastreturn->next==NULL)
		{
			cout << "End of List" << endl;
			return (NULL_EMP);
		} else
		{
		enode* temp;
		temp=lastreturn;
		lastreturn=lastreturn->next;
		temp->next->e.write();
		return (temp->next->e);
		}
	}
}
 
bool EmployeeGroup::remove( int id )
{
     enode *q,*r;
     q = head;
     if( q->e.idno == id )
		{
			head = q->next;
			delete q;
			size--;
			return (true);
		}
 
      r = q;
      while( q!=NULL )
      {
        if( q->e.idno == id )
             {
				r->next = q->next;
				delete q;
				size--;
				return (true);
			 }
 
      r = q;
      q = q->next;
   }
   cout<<"\nElement "<<id<<" not Found.";
   return (false);
}
 
 
int EmployeeGroup::empcount()
{
	return size;
}
 
Employee EmployeeGroup::find(int id)
{
	if (exists_head==false)				//Check to see if there's a head. If not, break.
	{
		cout << "There are no employees in this group.\n" << endl;
		return NULL_EMP;
	}
	enode* current=head;
	while (current->e.idno!=id)
	{
 
		if (current->next==NULL)
		{
			cout << "No employee with id "<< id << " was found.\n" << endl;
			return NULL_EMP;
		}
		else
		{
			current=current->next;
		}
	}
	cout << "We found the employee!!!" << endl;
	return (current->e);
 
}
Posted in C++

MySQL to XML Database Dump

Thumb PHP
This code was used to dump a list of legal blogs from a MySQL database to an OPML file.  After the dump, I would typically upload the OPML file to the company’s legal search engine which would then pull the RSS feeds into its own index. I also constructed a spider that would use Google to spider legal blogs for info.
<?php
header("Content-type: text/xml");
function check_not_empty($s, $include_whitespace = false)
{
    if ($include_whitespace) {
        // make it so strings containing white space are treated as empty too
        $s = trim($s);
    }
    return (isset($s) && strlen($s)); // var is set and not an empty string ''
}
 
 
 
$host = "localhost"; 
$user = ""; //I've erased the username and password for security
$pass = "";
$database = "law";
 
$linkID = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($database, $linkID) or die("Could not find database.");
 
$query = "SELECT * FROM blogs2 ORDER BY Serialid ASC";
$resultID = mysql_query($query, $linkID) or die("Data not found.");
 
$header = "<?xml version=\"1.0\"?>\n
				<opml version=\"1.0\">\n
<head>\n
<title>Matthew Currier created</title>\n
<dateCreated>".date("F j, Y, g:i a")."</dateCreated>\n
</head>\n
<body>\n";
echo $header;
for($x = 0 ; $x < mysql_num_rows($resultID) ; $x++){
    $row = mysql_fetch_assoc($resultID);
	$Title = $row['Title'];
	$Htmlurl = $row['Htmlurl'];
	$Xmlurl = $row['Xmlurl'];
	$Serialid = $row['Serialid'];
	$Category1 = $row['Cat1'];
	$Category2 = $row['Cat2'];
	$Category3 = $row['Cat3'];
	$Category4 = $row['Cat4'];
	$Category5 = $row['Cat5'];
	$Category6 = $row['Cat6'];
	$Location1 = $row['Loc1'];
	$Location2 = $row['Loc2'];
 
	echo "\t<outline type=\"Legal Blogs\" serialID=\"$Serialid\" priority=\"1\" xmlUrl=\"$Xmlurl\" title=\"$Title\" htmlUrl=\"$Htmlurl\" ";
	if (isset($Category1)){ 
	echo "category1=\"".$Category1."\" ";
	}
	if (check_not_empty($Category2)){ 
	echo "category2=\"".$Category2."\" ";
	}
	if (check_not_empty($Category3)){ 
	echo "category3=\"".$Category3."\" ";
	}
	if (check_not_empty($Category4)){ 
	echo "category4=\"".$Category4."\" ";
	}
	if (check_not_empty($Category5)){ 
	echo "category5=\"".$Category5."\" ";
	}
	if (check_not_empty($Category6)){ 
	echo "category6=\"".$Category6."\" ";
	}
	if (check_not_empty($Location1)){ 
	echo "location1=\"".$Location1."\" ";
	}
	if (check_not_empty($Location2)){ 
	echo "location2=\"".$Location2."\" ";
	}
	echo "/>\n";
}
 
 
echo "</body>";
echo "</opml>";
 
 
?>
Posted in PHP

Multivariate Regression of High School Dropout Rate on Other Variables

SAS Thumb
This is the code I wrote for a multivariate regression project.  The independent variable is high school dropout rate and the dependent variables include parameters like unemployment rate, teen birth rate, and student-teacher ratio. If you don’t have SAS installed, you can also view a snippet of the output that the code produces.
/* Variables imported from excel
1 state
2 DropoutRate
3 UnempRate
4 PercentPassportAppsApproved
5 PercentRepresentedByUnions
6 StudentTeacherRatio2008
7 TeenBirthRate
 
x0=state
y=DropoutRate
x1=UnempRate
x2=PercentPassportAppsApproved
x3=PercentRepresentedByUnions
x4=StudentTeacherRatio2008
x5=TeenBirthRate
x6=Death Penalty
*/
 
dm "out; clear; log; clear"; /* These instructions clear the output and log windows */
 
/*Rename variables to simplify processing */
 
data work.fin2(rename=state=x0 rename=DropOutRate=y rename=UnempRate=x1
              rename=PercentRepresentedByUnions=x3 rename=PercentPassportAppsApproved=x2
              rename=StudentTeacherRatio2008=x4 rename=TeenBirthRate=x5 rename=DeathPenalty=x6); set final2;
 
    *Change Passport Apps approved into a readable percentage;
     PercentPassportAppsApproved=PercentPassportAppsApproved*100;
 
run;
 
DATA DropOne; set work.fin2;
 
    IF _N_ NE 9 THEN OUTPUT DropOne; *drops District of Columbia from data set;
    label y='Dropout Rate';
    label x1='Unemployment Rate';
    label x2='Passports Approved';
    label x3='Union Membership';
    label x4='Students to Teachers';
    label x5='Teen Birth Rate';
    label x6='Death Penalty(1)/No Death Pentalty(0)';
 
RUN;
 
proc reg data=DropOne;
title 'Regression of Dropout Rate on 6 independent variables (excluding DC)';
model y = x1 x2 x3 x4 x5 x6 / clb;
run;
This program produces the following output:
Posted in SAS

C++ String Class

Screen print of code
This is a simple string class that I constructed as an assignment for one of my classes. Most programmers would just use C++'s built in string preprocessor, but it's a good exercise because in some cases you may need to write your own to serve your specific needs.
#include 
using namespace std;
 
class String {
private:
    char* strval;     //holds string value
    int strln;           //holds length of string value
public:
    //constructors
    String();           //initializes default value of null string
    String(char* s);   //initiializes string to value of cstring s
 
 //destructor
 ~String();   
 
    //access functions
    bool operator==(const String s);   //true if value equal to s, else false
    bool operator(const String s);   //true if value greater than s, else false
    int length();                      //returns length of string value
/*    String operator+(String s); */    //returns concatenation of s to string value
 
    //modifier functions
    void tolowercase();
 
    //input/output functions
    //reads in string value from istream
    friend istream&amp; operator&gt;&gt;(istream &amp;strm, String &amp;strng);
    //outputs string value to ostream
    friend ostream&amp; operator&lt;(String s)    //true if value greater than s, else false
{
    int shorter_len = ((strlns.strval[i]))
        return_value = true;
    else
        if ((i==s.strln) &amp;&amp; (strln&gt;s.strln))
            return_value = true;
    return return_value;
}
 
int String::length()
 {return strln;}
 
istream&amp; operator&gt;&gt;(istream&amp; strm, String&amp; strng)
{
 char* temp = new(char[101]);
    strm &gt;&gt; temp;
    for (strng.strln=0; strng.strln &lt; strlen(temp); strng.strln++)
 {
        *(strng.strval+strng.strln) = *(temp+strng.strln);
 }
    return strm;
 delete [] temp;
}
 
ostream&amp; operator&lt;&gt; s1;
 
}
Posted in C++