Wednesday, January 3, 2018

Expenses and SQL

I've been using the free version of Expense Manager to keep track of my expenses last 2017 and luckily I was able to make complete entries for the whole year. This year I plan to set a monthly budget and try to stick to it and I will be using last year's data.

Expense Manager uses sqlite as storage and it has an option to backup the data either as raw sqlite data or csv. Instead of using the raw sqlite data backup, I created a new database (using sqlite3)  and created the expenses schema is below. 

CREATE TABLE expenses(
   date_incurred DATE, 
   amount NUMERIC(8,2),
   category VARCHAR(27),
   description VARCHAR(24)
);

After preprocessing the csv file by removing unneeded columns, sample insert statements generated are shown below.

INSERT INTO expenses(date_incurred,amount,category,description) VALUES ('2017-01-01',-55,'Snacks',NULL);
INSERT INTO expenses(date_incurred,amount,category,description) VALUES ('2017-01-01',-21,'Transportation',NULL);
INSERT INTO expenses(date_incurred,amount,category,description) VALUES ('2017-01-02',-1150,'Others','radio');
INSERT INTO expenses(date_incurred,amount,category,description) VALUES ('2017-01-02',-66,'Others','battery');
INSERT INTO expenses(date_incurred,amount,category,description) VALUES ('2017-01-02',-1700,'Others','bag');
INSERT INTO expenses(date_incurred,amount,category,description) VALUES ('2017-01-02',-500,'Dinner',NULL);


Some of my expense categories:

Snacks
Transportation
Others
Dinner
Medicines and Supplements
Breakfast
Lunch
Groceries
Apartment Rent
Cable/Internet
Laundry
Water
Cellphone Load
Haircut
Clothing
Mom Allowance
Books and Reading Materials
 
Rest and Recreation
 
Now to answer some of the interesting questions.

What was my total expenses for February 2017?
>select sum(amount) from expenses where date_incurred>="2017-02-01" and date_incurred<="2017-02-28";

What was my total expense for 2017?
>select sum(amount) from expenses;

What are the categories of my expenses?
>select distinct category from expenses;

How many times did I pay my apartment rent in 2017?
>select count(amount) from expenses where category="Apartment Rent";

How much did I pay for breakfast in 2017?
>select sum(amount) from expenses where category="Breakfast";

How much is my expense per month in 2017?
>select strftime('%m',date_incurred) as month, sum(amount) as amt from expenses where strftime('%Y',date_incurred)='2017' group by month;

How much did I spend for lunch each month, total and average, in 2017?
>select strftime('%m',date_incurred) as month, sum(amount) as amt,avg(amount) as mean from expenses where strftime('%Y',date_incurred)='2017' and category="Lunch" group by month order by amt;

What is the average amount I spent for lunch in 2017?
>select avg(amount) from expenses where category='Lunch';

My budget for snacks for one month should be.
>select avg(amount)*30 from expenses where category='Snacks';

Tuesday, January 2, 2018

Highlights of my academic life in 2017

  • Presented a paper in PCSC 2017
  • Presented a paper in NCITE 2017
  • Started teaching again after four years of being on study leave
  • Taught a new course: Computer and Network Security
  • Attended technical conferences sponsored by private companies
  • Helped SRG members in their SP/thesis proposals, undergraduate and graduate
  • Participated in the 2017 ACM-ICPC Asia-Manila Regionals
  • Presented in SESAM's graduate seminar on Space Science
  • Maintained the operation of the Peak-Two Cloud
  • Learned new Web Dev Technologies

Monday, December 25, 2017

2017 ACM-ICPC Regionals Asia-Manila Experience

We have two teams from ICS for this event held at Ateneo de Manila University last December 13-15, 2015. It is good to have been part of this event again after a long break. Thanks to Sir Clinton Poserio, head coach of the Eliens team, for inviting me. The ICS alumni have been a great help in providing financial support. A team from National University of Singapore was the winner in the competition. The top team from the Philippines was from the Ateneo de Manila University. The contest website is here.



Thursday, November 30, 2017

DEC{}DE 2017: Transforming Security

We attended this one-day event sponsored by Trend Micro last Nov. 29. The morning session was devoted to keynote sessions and the afternoon on technical sessions which included a hands-on lab.
Majority of the talks focused on the modern threats and attack approaches. The speakers also presented interesting statistics on the security landscape. With the growing security threats, the speaker from FBI urged the cooperation among the industry, government, and the academe. A common attack nowadays is the Business Email Compromise (BEC) wherein employees are subjected to phishing attacks resulting to the spread of malware, particularly ransom ware. With the growing popularity of the crypto-currencies, attackers are also taking advantage of user resources for mining without their consent. There were also talks on cloud security(Amazon) and application of machine learning techniques to security(Microsoft). A final takeaway from the event is that "security is a habit.".



Wednesday, November 1, 2017

A list of good-sounding adjectives for titles

  • sustainable
  • inclusive
  • comprehensive
  • novel
  • modern
  • exclusive
  • multidisciplinary
  • interdisciplinary
  • collaborative
  • pioneering
  • innovative
  • modern
  • monumental
  • scientific
  • professional
  • superior
  • *-proof (ex. hack-proof)
  • *-resistant (ex. tamper-resistant)
  • *-preserving (ex. privacy-preserving)
  • free
  • tested
  • timely
  • unparalleled
  • profitable
  • encompassing
  • generic
  • secure
  • safe
  • efficient
  • effective
  • revolutionary
  • futuristic
  • forward-looking
  • latest
  • advanced
  • interactive
  • user-friendly
  • infinite
  • certified
  • complete
  • emerging
  • excellent
  • sensational
  • practical
  • powerful
  • proven
  • sizable
  • reliable
  • non-deterministic
  • non-intrusive
  • (more to follow)