I’ve been very busy since the last time I posted, sorry about that. Mostly I’ve been busy with work, or meeting up with friends, or with a girlfriend (we broke up when she went home to Korea in June, so that’s no longer an issue.

I’ve not only been working at my normal job for CSNstores.com, but have also begun one-on-one tutoring sessions by answering an ad I found in a restaurant.  This has netted me about $550 in 2 months, and that was with only 2 students. I’m currently down to one since the other left, but I’m going to focus on Korean students in Boston who need help understanding their English class readings, as their parents seem to be willing to pay anything for their children to succeed.  I’ve witnessed this first hand with the girl I dated and am also seeing it with the current people I’m tutoring, and it seems there’s lots of demand for this kind of service so that’s one thing I’ll be focusing on in the coming months.

I also just recently accepted a new position at work: processing.  This has been keeping me very busy. Basically we have spreadsheets full of product data concatenated into SQL insert scripts, and it is my job to run them to enter everything into the system (one of the main tasks).  I also gather project materials, put them together, and send the work out to be done.  I have just now started to get comfortable with the position after a week, but I know there’s a lot more I can do.  Many nights I’ve had to stay late to get the work done on time, but yesterday I was shown some organizational steps that will help out a great deal.

I was asked to take this position because my new boss would like to improve the process, make it more efficient, reduce steps, etc, and that is exactly what I was doing in my previous position.  So far I’ve really impressed my new boss, which is awesome since it’s been only 1 week since I started the position.

In my next post I’m going to write more about how exactly I do this, but I’d like to wrap this one by letting everyone know that I moved into my new apartment on August 1st. I left the previous place a month early to get out of the September 1st lease cycle, which is just awful with all the students moving in on the same day.  I now live in Somerville, MA, just a few miles north of Boston and it so much nicer.  I’m very happy with it so far.

Finally, I’m all prepared for hurricane Irene to hit tomorrow so we’ll see how that goes. Good luck to all my other friends on the east coast!

So now I’d like to take some time and illustrate how you can use excel and firefox extensions to fill in data for you, create scripts, and generally reduce the amount of work you have to do.

First, if you’ve never used CONCATENATE, make it your friend. It’s super easy.  Type in =CONCATENATE to any cell and then follow it with (“your input goes here”).  Remember to use quotations around the text just like that there.  This is useful for SQL scripting since you have defined columns which can be pasted directly from the database output into your spreadsheet.  Ex: Product Name (Cell C1), Product ID (Cell B1), NewProductName (Cell C1).

You can write a script to update this information by typing: =CONCATENATE(“Update <ExampleTbl> set ProductName ‘”,C1,”‘ where ProductID = ‘”,B1,”‘”)

So the ‘ on either side of the cell info is to tell SQL that you are using variable characters, and the quotes and commas are how Excel knows what cell you’re referencing.  The very last quotation mark is simply to close the quotes from the beginning of the concatenate formula. You can then drag that down and automatically fill in new names for all the products that need updating.

Another useful formula is the IF statement, which can be combined with many others and is very versatile.  One of my favorite uses is when you have to changed a command at the very end of a list of scripts.  For example, every single query needs to have “union all” at the end save the last one.  Let’s say there’s a list of product IDs from A1 to A20. The IF(ISBLANK) formula needs to be used to do this, and you would tell it to see if the cell below the first one in the list is empty or not, which defines if you’ve reached the end of your list.

So now you’ve updated those product names and you want to make sure they’re succeeded.

Formula: =IF(ISBLANK(A2), CONCATENATE(“select from ExampleTbl with (nolock) where ProductId = ‘”,A1,”‘”), CONCATENATE(“select from ExampleTbl with (nolock) where ProductId = ‘”,A1,”‘ union all”).

This formula essentially looks to see if A2 is blank. If it is, the if commands tells Excel to use the first statement if the given criteria are met. But if not, the second command will be used. This will automatically fill in union all to all queries but the last one.  If statements are also useful to make sure data matches:

=IF(A1=C3, “Names Match”,”Names Do Not Match”) which can make comparing data entered into the system and the original product info as easy as dragging and seeing if anything doesn’t match. Useful when you have lots of data entry to check.

My new position at work involves a lot of file manipulation and folder creation/moving.  To make this as easy as possible, the best way is to start with VLOOKUPs.  VLookup stands for vertical lookup, and will look up information from a table arranged vertically. A typical formula looks like this: =VLOOKUP(C2,A2:B20, 2, False).  This is telling the vlookup to look for the information from cell C2 in columsn A2:B20 (your table, the first cell on top left and last on bottom right), and to return the corresponding information for whatever is in cell C2 from the 2nd column of the table.  False tells the command to return only exact matches, while true will look for any matching words. For every category we may have, there is a standard directory (ex: Pet Items\Cats) and so you build out a list of all category names and their corresponding directories. In one cell of your template you will always have to paste the category name(C2), and then the next cell would have a vlookup like this: =VLOOKUP(C2,A2:B20, 2, False). A, the 1st column of the table, would be the names of your categories, and B (2nd column) would be the directory of each category.

I also make brand new folders, and you can concatenate the directory name to add the new names like this. D2 is directory lookup cell, E2 is new folder name: CONCATENATE(“”,D2,”",E2,”") which would get you Pet Items\Cats\NewFolderName in cell F2.

Say you want to create that new folder. Again you can use concatenate, this time with windows command line commands: =CONCATENATE(“CD “”Pet Items\Cats & MKDIR “”,E2,”"”) which would essentially create a new folder  in that category for every new name you need.  This drastically reduces the time spent creating new folders and is much simpler than right click, new folder, copy name, paste name, hit enter, do it again. It typically takes me about 30 seconds to create 80 folders in this way, if that long.

You can also use the windows command line and concatenate to copy new files to these folders. Again, this requires standardized file locations for the files in question.  I recommend making one if you don’t have it already, and then adding a new column for the filename itself. The easiest way to get the file names is click start, click run, then type cmd and hit enter. In the command prompt box type this: CD “DirectoryName” and hit enter to change to the needed directory. then type dir > FileList.txt. Dir lists all the files in a directory, and > tells it to make a file out the output (in this case, a text file). Next just open the text file, and paste it into excel. Using text to columns, choose standard width, and move the line between the filesize and the file name, then next and finish. It will create a new column with all names which you can then add into cell F2

Then simply concatenate together the directories with the file names and you’re all set: =CONCATENATE(“XCOPY “”Pets\”,FilenameCell,” “”Pets\Cats\NewFolder”" /s /i”) this command will add in all needed info to copy any files you may need, and you can then select and paste all commands at once to run multiple copy actions at one time. I actually recommend doing 3-5 at a time as you cannot view all error messages after a certain amount.

Moving on to firefox, some of the extensions I use most at work are iMacros, AutoCopy, and Clippings. iMacros basically records all mouse clicks and input into text boxes, which you can save and run again over and over. Think of it like this: If you are constantly pasting in data, then clicking a button, and then typing in something else, that can be recorded once and then when you double click a macro the whole thing is run before your eyes, kind of like watching a ghost use your computer.

What makes this even more powerful is that you can edit the scripts, simply by right clicking on them. The most useful edit I’ve found is telling it to paste from your clipboard, instead of whatever value was used when you first made the macro.  To do this, make a macro and save it.  Then right click it, choose edit, and find the line with the copied information. Highlight the copied info and replace it with this: {{!CLIPBOARD}}  This will tell it to paste whatever’s on the clipboard.  This works really well unless you happen to have copied info your system won’t recognize, like your girlfriend’s phone number or an essay

Other times, you can find out how webpages and dropdown menus were built by editing the macro. For example, at work when we choose a name from a dropdown, we just type it in. But each of those names actually has an ID associated with it, and when viewing the macro after saving it I found that it was not a name that showed up, but actually the matching ID.  Knowing this, you can again replace that ID with {{!CLIPBOARD}} and then quickly get to the info you need just by copying an ID.

Autocopy will automatically copy highlighted text after a user-determined amount of time, and clippings allows you to save your most used responses, like “Hey , here’s the thing you wanted” where you can just copy and paste in the person’s name in the greeting.  It may not be personal but it sure is quick and means you don’t have to do any more typing of repetitive forms and letters.

The uses are endless but your time is not, so try out these commands and make excel and firefox do all the hard work so you don’t have to!

So I had a review back in February which was basically everything I expected it to be.  It boiled down to that I needed to be more consistent in my work, and that the improvements that I had made to speed up the process didn’t really matter.  I had made a spreadsheet with some SQL scripts to pull values from the system and paste the ones that the offshore team had to enter next to each other to compare if they are correct.  Not only is this much faster than looking at the spreadsheet, the website, and back and forth hundreds of times, but it also reduces the number of pages you have to load to get to the info and the time you have to wait for those pages. To make things worse, our IT department has stuck us with IE as the default browser, and we are unable to change the homepage due to restricted settings.  I ended up dragging all the links I use to the bookmark bar, but pretty much everyone else I work with clicks the new tab button, waits for that load, and then clicks the homepage button, and from there click the “production tool” button which brings you to a page with a hundred links for every department within the company where they get to choose the ones they need at the time.  So basically 3 pages load just to get to the page you actually need.  I despise this and it makes me sick every time I see someone do it this way.  I suggested a simple change only to find out that everyone has a different style and some people apparently prefer to do unnecessary tasks.

Well, I do not. I got firefox installed, changed my home, and installed the following extensions: lastpass (for autologin when the site logs me out), autocopy (no more ctrl+c), Clippings (for canned responses) and iMacros (no more entering of repetitive information).  I’ve also made keyword searchers for ID numbers of suppliers and manufacturers, allowing me to simply type in “s ###” and be brought to the correct page. With iMacros this is even easier: I simply copy the ID and doubleclick the macro I recorded which pulls the ID I copied from the clipboard and brings me right there.  Other things I’ve gotten rid of include entering required names and statuses, instead option to copy a list of items and paste it directly into the box to have them all pulled up automatically.  Pricing? Don’t we type the same things into those boxes all the time?  OK, that’s now a double click.

But wait, I also have to answer questions and solve problems for the customer service reps, mostly when they request products be changed or new ones added.  This is where Clippings comes in.  I created a template message, highlight and choose make new clipping from selection, and then add a keyboard shortcut to paste the standard response to the most common issues.  I’d say this works about 95% of the time.  I’ve even improved upon this by recording it as an iMacro and copying the product name they had requested and adding in a line of code so not only is the message added with a double click but the product ID is also pulled from my clipboard.

So now pretty much all the repetitive tasks of my job are done with a simple copy, highlight, or double click.  I’m amazed when I look back at my first month, I was somewhat annoyed with the amount of work that needed to be done by hand, so I went and learned about SQL and Excel and then automated away those parts.  Now I’ve gone even further which, at least in my mind, allows me to do more work throughout the day but with less effort than my coworkers.

I have a progress check-in coming up on Tuesday where I plan on bringing up these improvements as well as letting my boss know I’d like to take part in training the new hires we will be bringing on shortly.  And as for my consistency?  My February review was just the kick in the pants I needed to get myself into gear.  I went from getting pages of edits back (and some times having to do them twice) to getting just a few lines or none at all, allowing me to move onto the things I’m doing now.  Very excited to see what the future holds (especially if it holds a raise)!

So I’ve pretty much decided that I hate Boston.  The main reason is that my standard of living has decreased considerably.  What I mean to say by this is that Boston is so expensive it prevents me from living like an adult.  I’m forced to rent a shitty apartment that I have to share with 4 other people just to make it affordable.  In Minneapolis I paid $635 per month for my very own studio apartment. In Boston I’m paying $600 per month for a basement bedroom (upstairs rooms are $750) and I have to share the place with some pigs.  As I’m sure you’ve all seen on my twitter and facebook, dishes sit for weeks at a time.  It’s all one person, but none of us are going to clean up after him.  Apparently he does major cleaning once a month because, as he told one of my housemates, “it needs to be done.”  The thing about that is, if he cleaned up regularly it wouldn’t be even half as bad.

This is also the guy who has our internet account.  It is in his name and we pay him for it.  Last month it was shut off for almost 3 weeks because he had not paid the bill in more than 2 months.  Which is funny when you consider that he only has to come up with $12 of his own money IF HE ACTUALLY HAD OUR MONEY.  Turns out though he’s stealing our money and buying booze, take out food, cigarettes, and whatever else dirty-hipster-skater-biker kids waste their money on.  We just recently had a new internet bill posted on the fridge and the price had gone up by about $11, with the previous price scribbled out.  However on the back comcast posts a breakdown of all charges, and the $11 charge was for reactivation and late fees. I simply wrote on the bill that these charges were his fault and that I was not paying them, as did the rest of us in the house. This guy is just absolutely ridiculous to expect us to not only finance his vices, but then to subsidize his mistakes.

So anyway, I’m in the process of finding a new apartment, which is no easy task.  I’d like to live somewhere relatively close to where I am now, yet away from all the rowdy college students but also close to public transportation. Turns out the area I’m considering right now is a tad pricey for a 2 bedroom.  Enter REDDIT.  I’ve met with some folks from Reddit who would like to get an apartment together and I think this is much more to my liking as we would be forming an intentional community rather than being thrown together randomly.  I need harmony in the place I live in, not constant discord where it feels like we’re all at each other’s throats for the stupidest of transgressions.

My other option is to send out an email to my company and see if anyone will be in need of a roommate come September 1st. That would be ideal because then I know at least that I would be living with responsible people who all have jobs and similar hours so there will never be late music or parties like I’ve had to put up with since moving here.

It’s been a long time. I apologize for that. I’ve not had it in me to write on the blog for a long time.

I’ve been busy but mostly with the same old things. Dated a girl, broke up after a month due to some lying on her part. Had a holiday party at work that was kickass, A+++, would go again. Met a new girl and have been on several dates. She breaks out with a lot of LOTR references which I find very nerdy yet incredibly attractive…will have to wait to see where this goes.

I still hate the place I’m living, and while I talk to everyone I really would want to live with only one of them if given the choice. I’m starting to save money to move to a new place when my lease is up (it will cost at least $2000 with first month/last month/security deposit). Ideally I would live with people from my company who are also looking for roommates, people who have stable jobs and care about living in a clean house.

On top of the issues with the housemates is the house itself, which is more than 100 years old. There were mice, though they seem to be gone now. The basement wall is basically just bricks cobbled together, probably the original foundation, and it leaks whenever the snow melts. Just called the landlady a 2nd time about that, but I really want it to get taken care of BEFORE all the snow starts melting for good.

I also just last week interviewed for a new position within my company on the German-speaking linkbuilding team, basically contacting German blogs and providing incentives for them to link to the company’s German products.

Oh and I’m working on paying off some credit cards and saving some money. I already accidentally paid one off this month by feeing it money from 2 accounts when I thought I was only paying out of one. So ya, now I can use some money I never missed to pay off something else!

I apologize profusely for the lack of updates. I’ve been quite busy since I started my new job, and just have not found the time or energy to sit and blog for the time needed.

The new job’s been going pretty well – simply put, I do quality assurance. I basically stare at a spreadsheet all day and answer a bunch of questions (was this done properly, was that correct, yes/no?) and go to various parts of the website to make sure that the offshore teams entered all the data correctly. I also get to handle things like pricing, which the offshore folks can’t do. I like to consider it a treasure hunt, or maybe a scavenger hunt. I sit down and then go on the wild goose chase of yes or no. I enjoy this for a few reasons. The first is that I like the feeling of completing projects, even if it takes 3-4 hours to do. The second reason is that everyone in my department has their own way of getting through the day. Most of us, myself included, throw on our headphones and listen to music (or more accurately in my case, NPR) and get down to business. I’m still learning and I’m now into my 3rd month on the job, but I’ve gotten the hang of it pretty easily. There are some easy projects that I do perfectly, but once they get more complicated I start getting edits. I always hate these because it makes me feel dumb but even the people who’ve been here for years get edits, mostly because we handle the most complex lines and staring at them for hours on end is an easy way to lose track of things. I’ve done a few weekends of overtime too, which were quite nice and helped make up for the taxes I lose from my base salary, but apparently things are slowing down now and we won’t have much of that this month

Lately I’ve been working on making this process easy as possible, which was helped by SQL (which everyone at work calls Sequel, thoughts on that?) and the DB definitions spreadsheet an assistant manager gave me. I pretty much cannibalized the existing lookup scripts provided in the QA spreadsheet and entered the relevant fields and tables needed to find the needed info, and then I paste it into excel. Then I paste the same info from the product list next to it and use if statements to tell me if all the data was entered correctly. I use this to check weights, dimensions, prices, and shipping. This is so much faster and easier than what I learned to do when I started, which was to look at the description page and then back to the spreadsheet and so on and so forth for 500+ products. This drastically reduces the time needed to check such things, and I’ve sent it to the manager in hopes of having the whole team adopt it.

My first month in Boston I got an email from a woman who used to work at my company about a conversation program that pairs you with an international student for a conversation hour. I jumped on this and was paired with a Korean guy who is really cool, and we get together once a week for an hour or so and shoot the shit. There are so many international people here in Boston, it’s amazing when you think about the size of the city but not surprising considering Harvard and MIT are here. I’ve met many Germans through Goethe Institute events, namely the $15 all-you-can-drink wine tasting, and also attend a German meetup as often as possible, so I still have plenty of time to practice my German.

Through my Korean friend I met a Korean woman named Lina, who I’m now dating. On top of being incredibly beautiful she’s also really really funny. Very sarcastic, much like myself, so we get along quiet well. I’ve enjoyed my time with her immensely so far. I love introducing her to new foods (mostly junkfood at this point) and seeing her face light up as she learns she likes it. These foods include nutty buddy bars, ho-hos, rice krispie treats, and pepperoni. We’ve been spending a lot of our evenings together which has kept me from sitting at the computer, but I can’t say I mind that at all and am much more satisifed by going out and spending time with her, meeting new friends, and just having fun instead of sitting inside watching tv shows (which I’ve found I don’t miss at all).

I recently went to NYC for a reunion of the alumni from the University of Graz, bringing together everyone who studied in Austria since the 1980s or so. I was easily the youngest person there, but it was a very good networking opportunity and very nice to see some familiar faces, ones I thought I’d never see again. I met some wonderful people, asked a Nobel laureate a question about the brain, and even got my picture taken with him. Later that night my German friend from MIT and his gf showed up and we went out and got some beer before heading back home.

So, I think that about it covers it for everything I’ve been up to in the last the 3 months. I will work on making this more current again, I promise!