|
|
Code || VBA, .NET, SQL |
Home |
Code: Excel, Access, DotNet, Website, SQL, Other, Sites |
Administration |
| Blog Entries |
| 2010-02-27 | Updated the site today, expanding the types of code shared to include DotNET, both C# and VB.NET, and website. I removed the X/HTML reference from the title, since I typically do not develop that kind of code, and is tertiary skill. |
| 2010-02-13 | Top Six Competencies that Predict Star Performance
I've often posted these bullets over my workspace, as a reminder of ways to maintain high performance. Not that I don't possess these qualities, but it pays to refocus from time to time, to maintain a high level of competence.
|
| 2010-01-04 | Many of the benefits with OOP can be applied to other design styles, e.g., design patterns (conceptual, not GOF) and reusability, but for me the differences have been primarily in object permanence and code quality.
I recently read a critic of OOP, and the point that I would agree from his article is that OOP provides a clearer set of rules, with the end result better code and the ability to program complex concepts more easily. Just some quick ideas:
|
| 2009-10-13 | The MBTI is a questionable measure, although I like it myself. Ten years ago I consistently tested as an ENFJ, but at some point I made a life choice to be more analytical, hence, the change from E to I, and from F to T. Nowadays, I consistently test as INTJ, although the I is just over the midline into I, and the T is just over the midline into T.
As a developer, I love the more abstract (N) concepts, such as design patterns, and eschew concrete how-to's (S), but my work-oriented websites would indicate I'm ISTJ, while my blogs and personal sites would indicate I'm INTJ. This made me wonder if the demands of work result in the IT personnel tests skewing to ISTJ. Rather than tech people being unable to change, I'd guess that the world of work, e.g., business people doing the hiring, allows limited change and requires a particular set of traits. The website used for the Type Analysis of sites: http://www.typealyzer.com/ |
| 2009-09-24 | A recent article questioned the values of recruiters, since many found them to be a worthless, hostility caused by too many dead-ends, unreturned phone calls, etc. For myself, recruiters, and the social networks that sometimes bring them to me, are a necessity. I am software developer, primarily for the financial industry in VBA for MS Excel and Access, and secondarily with other languages like C#, VB.NET, ASP.NET, HTML and PHP. I find the job market is pretty hungry for my skills, and my current spot was acquired through LinkedIn.
I have worked with websites for over 10 years, professionally and personally, and see them as a kind of marketing tool; part of one of my business school application essays was a website. I have always maintained a strong profile on the job sites, as well as maintained my own personal/professional sites, so when social networking came along, it became another avenue for development, and I naturally 'groom' my web presence. But, what works for me, a person that typically gets calls from many recruiters via the job sites, the professional social networking sites are just another step. For others, who don't have the luck, interest, inclination, or ability to pull in contacts via the web, those same sites are a waste of time. I do not get many calls from people that I network with, except those from former clients, although the recruiters that find me might, either because another developer passed my contact info on - I recently was introduced to a recruiter on LinkedIn via the HR head of a firm I interviewed for but was not hired by - or because they have a 'in' with the hiring manager. I was joking to myself yesterday, that the recruiters are my professional networkers, the people paid to do all the social stuff I don't. |
| 2009-09-21 | I generally hate the belief, that people are unmotivated because the work "was too easy/not challenging enough," since the ease of something probably bears little relationship to its motivation. Yes, some people like challenge, myself included, but motivation often stems from seeing the goal as having worth, either to one's growth, or to some larger goal. Challenge can be good, provided one believes there is a real solution, or that one's efforts would be rewarded, but motivation always matters more. Motivated people don't see challenges, they see opportunities, but you have to get to the point where the task is rewarding. Even then, real people have broader needs than simply goal orientation. Typical motivations: - Money (not my favorite, but a favorite cliche) - Recognition - Social obligation (make other people happy) - New technology (some thrive on novelty) - Work with friends - Advance one's skills Although this might seem common, it is probably forgotten, particularly in the world of men that coders inhabit, that feelings matter. |
| 2009-08-30 | The internet and computer use can drive people to multitask which is a great time and productivity waster, as well as reducing the intellectual drill-down required of intellectual thought. As a software developer, the ability to focus for long periods on single aspects is important, as is the ability to think about systems and to choose the correct solution. The focus on internet media, as well as the supposed tendency for youth to multitask, seems more harmful than good, at least in terms of smarts, or at a minimum, productivity. It's not that something positive can't arise from a reduced multi-target focus, but... |
| 2009-08-30 | I generally try to limit multitasking, but there are times it is more productive, when the wait time involved in the task is greater than then the time lost to switching. Anyway, the issue with multitasking is that there is time lost in switching tasks, e.g., time to recover one’s train of thought, and that needs to be balanced against the wait time involved in the tasks switched between. |
| 2009-06-11 | While I don't support wasting time and energy on worthless activities, I can't vouch for ever having found the recommendation to research to be of any value. I am definitely 'promiscuous' with recruiters, and not a believer in networking, although I have a fairly high profile in LinkedIn. Unlike most people that have a job that many people are qualified for, I am a niche contractor, not looking for permanent work. I work in a common language, or a set of tools, that few people specialize in, with a background at major corporations. When someone needs one of my specialties, I'll get calls and emails from 5 different recruiters. My specialization is also why I can never commit to one recruiter; when I need work, they don't have openings, but someone else does. And I have had repeat engagements from different clients, and with different recruiters. They find me jobs, take a percentage of the rate, and if the engagement is long-lasting, they barely have to milk the cow, just take the cream. For extroverts, and those looking to sell, networking might be wonderful, but I'm nauseous of the promotion of networking. People find me engaging, well spoken, helpful, and I can certainly self-promote, but I'm not that interested in other people: I'm kind of half introvert/extrovert. I enjoy people, but I also enjoy my alone time, with my wife, my music, my books, and my fitness activities. Most people are extroverts, and likely, most people are not finding much help networking. My primary focus, has been getting recruiters to see my resume on line. Right or wrong, I think recruiters look at people as a very narrow skillet and they have to fill/exceed the requirements, particularly in bad times. I keep my headline clear and simple. When searching I update my resume daily, or a few times a week. I focus on detailing my technical skills, not exaggerating my experience, or pumping it up with filler. In truth, it's been a little bit of luck - I have a desired, niche skill set in technology, with a strong background at major financials - as well as being able to capitalize on my niche market. |
| 2009-04-27 | On my CodeDotNet.com site, I added several new features, in particular a web service to return the system log as XML. It is parameterized to return all entries, page hits, or only errors. My next step is to provide the data as Excel/CSV downloads - a common aspect of what I do- as well as build a system for collecting ideas and code snippets. |
| 2009-04-27 | Access 2007 is supposedly easier for the basic user and tough for the power user. My applications built for 2002/2003 work without issue on 2007, the only issues being the default disabling of code, which once worked through aren't a continued hindrance, and the changes in screen real estate, which harm the form presentation a bit. As for ADP vs MDB (with linked tables) vs MDB (with stored procs) , the last last option seems the best option, as it provides the best upgrade path for the client and the best security. In a recently-delivered Access with SQL Server (stored procs) application, performance was very good - an ADP was not an option as it was very slow - and since I separated the application forms from the data via a separate class, the app would be fairly easy to migrate to a VB.NET solution using either the web or a fat client. As for security, having no linked tables meant that users couldn't harm the data. |
| 2009-04-20 | Still developing basic skills with .NET, I added an email form to my website, CodeDotNet, using an eMail class, and code-behind in a form to send email. Additionally, the details of the email are added to my site log.
I am looking to write the same code in C#, not difficult, but there are some complexities using the 2 languages in one project, details that I need to resolve. |
| 2009-04-13 | For my newish .NET site, CodeDotNet, I added a form for performing Black-Scholes European option calculator, utilizing a VB.NET class, code behind, and an ASP.NET web page. Some interesting new techniques, at least for me, were built-in form validators, as well as postback, which maintained the web page values between submissions. If interested, please view it. Comments are welcome. |
| 2009-02-26 | I've been developing an interest in the .NET sphere, and to increase my abilities I bought a new domain, http://CodeDotNet.com with which I will explore various techniques. My plans are to use the site to expand my skills by sharing ideas and techniques for .NET, combining an ASP.NET website, internet-based web services, a VB/C# desktop client and/or a widget to access selfsame web services. |
| 2009-02-12 | SQL work makes up a large portion of my day, but in various forms. Much of my work has taken place using MS Access, building queries, equivalent to SQL views, to generate management reporting. When needed, which is often, I open and edit the view as SQL as need, or reuse SQL in ways to speed development by reuse of elements. The work for Access encompasses the basic commands of SQL, SELECT, DISTINCT, TOP, AS, DELETE, INSERT INTO, UNION/UNION ALL, GROUP BY (MAX, MIN, COUNT, SUM), ORDER BY, INNER/OUTER JOIN, WHERE, keys, functions, etc. The Access reporting I was hired to build as a temporary solution is after two years being migrated to SQL Server with little modification, encompassing hundreds of tables, and hundreds of views, as well as specialized scalar functions for age and value grouping. I think this speaks to my foresight, planning, and general understanding of good design.
More complex work is typically done using SQL Server, or any DBMS, writing stored procedures/functions/triggers and designing tables to maintain relationships and data integrity. I've provided samples in XLS files that show my basic work, and the following details work done exclusively by myself, and as it specifically applies to SQL: 1. For a Deloitte forensic accounting group, I converted an Excel workbook that used SAS file-based data to use SQL Server. The application was designed to make the accounting data drillable, in that forms would provide server data from which users could select fields and parameters, passing the selections to the stored procedures, and returning the recordset data to pivot charts. In that capacity, I created SQL procedures for loading data (Bulk Insert, Create/Alter Table), and providing interactivity via parameterized stored procedures (#TEMP TABLES, Dynamic SQL, Transactions, transaction audit/logging). 2. For my current client, Transaction Management Group, I was recently asked to build a SQL Server-based application for management, as a prototype of a system they would like to build, to enhance reporting for operational losses, which is named dbIRS Viewer. Although the front-ends are done in Access, the data is normalized on SQL Server. All the data is accessed and modified via stored procedures and views. Some of the SQL is fairly basic, e.g., parameterized SELECT, but a few are more complicated to meet the need for relational data presented in Excel; for that I've used cursors and temp tables to return multiple cells into one. Additionally, I've created functions to provide text value grouping, as well as triggers to update time stamp columns, that I use to prevent users overwriting each other's updates (Optimistic Locking). As an aside, about 6 months ago, I interviewed for a hedge fund that tested my skills via PreVisor, but before the test I prepared and tested myself via Brainbench. At the time, I was only working in Access, and hadn't done hardcore SQL for about 2 years, but even then, I scored fairly well on a test of SQL (ANSI) Fundamentals. According to Brainbench, I "scored higher than 81% of all previous test takers. Demonstrates a clear understanding of many advanced concepts within this topic. Appears capable of mentoring others on most projects in this area." As for style, although the provided samples don't represent how my code is normally structured - they are downloads into Access tables - I code SQL as cleanly as I code VBA. Other developers are impressed and pleased with the readability of my VBA code, since it is structured, modular, well named, and if the code is long and multi-segmented, commented. |
| 2009-02-06 | Uploaded an example of a class module used to interface between Access or Excel application and SQL Server. It incorporates the typical elements of a class, enumeration, property get/set, and functions. It provides a persistent data object for use within an application which can be filtered and passed, as well as encapsulates invocation of stored procedures.
It encapsulates a specific implementation of SQL Server data, with numerous constants within the class, to separate the data interaction from the application. As for methods, it has numerous public methods for invoking stored procedures to retrieve, add, delete, and update records, filter/unfilter the recordset, and private methods for establishing/closing connections, verifying user access, and verifying the class version. |
| 2008-11-12 | Updated this site coding, modifying the embedded blog aspect by creating code to limit each blog display to 500 characters, and enabling the code to link the display to the complete entry. The PHP was fairly simple, and it will allow me to go into greater detail on code entries, as well as expand my thoughts on issues relevant to programming. |
| 2008-10-09 | Working with Access, I needed to calculate business days between dates, excluding holidays, and with an eventual conversion fo the DB to SQL Server, and rewrote the VBA into an SQL function. |
| 2008-06-24 | Uploaded a class module for zipping a file or files with appropriate verification of inputs. Although downloadable from this site, my other site, accessed via link above, will maintain the freshest version . |
| 2008-04-22 | Still uploading code elements and refining the front-end on a new wiki, VBA @ WikiDot. In the past week I've created pages for various classes, functions, and XLA's that I've created and find particularly useful. |
| 2008-04-09 | To facilitate maintaining updated code, I've created a wiki, VBA @ WikiDot. One of the nuisances of maintaining this website is that providing updated versions of uploaded modules requires working directly with the back-end database, but the wiki will make it easier to directly update code. |
| 2008-03-15 | Took a commonly shared bit of VBA code that sends (no prompt) a Notes e-mail, and modified it to be reusable as a function, to send a Notes e-mail from passed values for subject, recipient, body, and attachment. |
| 2008-03-29 | Uploaded an Access DB that queries the domain for objects, e.g., user ID's, along with the related account fields. Requires that you modify code to specify a domain. |
| 2008-03-06 | Uploaded an Access import class module that encapasulates redundant import-related file functions for source selection, table creation, data staging, date marking, table append (staging to master), and cleanup. |
| 2008-02-27 | Uploaded an Excel VBA module that changes the referenced database in data ranges, provided that all the ranges point to the same DB. Instead of populating an XLS with data via ADO, I've lately used data ranges that are refreshed by pulling data from a DB, and the procedures I write refresh the ranges in the Excel object. The benefits of data ranges are that they more easily incorporate into formatted Excel pages. |
| 2008-02-26 | Still at DB, and probably like many in the financial IT field, wondering if the looming recession will have an impact on the job market, or even my current situation. For myself, I see little signs of hiring letting up, but then again, I am not invested in searching for a new position. |
| 2007-10-29 | Busy, busy, busy. Building on the positive reviews of a recent application for unverified trades, I've been asked to create an application to report and/or settle positions for a equity derivatives group. Additionally, I've recently built an application to automate daily cash equity reporting, designing a DB to import numerous files and FX Rates, calculate USD values and count breaks, then output to Excel. Also, I overheard a person describing her need for something to automate reporting for a synthetic equity group, so I showed her 'model' I've been working on, and I have been asked to create something similar for that team. |
| 2007-09-12 | Deutsche Bank, my current spot, has recently rolled out an unverified trade reporting application I've created for them in Access, and I'm finishing up on the front-ends for an automated reporting application, which aggregated data from numerous sources, ultimately presenting the break count and value in a standard Excel/PDF format. |
| 2007-07-24 | Recent uploads include code for dynamically resizing forms (Jamie Software) based on current screen resolution, as well as code to backup all forms, queries, and modules in an Access MDB. |
| 2007-06-30 | I was recently asked to step in on a project, since the current application had issues, in retrospect related to bad design and some communication problems. So far, my design changes and testing rigor have paid off, with no errors reported during the week. Hopefully I'll be able to hand this back and move on to working on my own designs.
I'm implementing something similar, a step up from the other desig's use of a shared Excel workbook tied to an Access database, but I'll be using an Access front-end for the interface, as well as using synchronization for keeping users data up to date and performance times down; the users are spread across the continents, and a more robust back-end is not available. |
| 2007-06-13 | Uploaded a basic class module to provide the excel Workday function in Access. |
| 2007-06-13 | Still at DB, and still developing a reporting tool, but moving toward smooting and locking down the code so that it can be run by business personnel. My current project work is shifting toward analytical tools and charting for the group's trade-related data. |
| 2007-05-10 | I've been working intensely on developing a reporting tool for a trade risk management group at Deutsche Bank for the past two months, and although I have developed some interesting modules, I haven't uploaded any to the website. Expect to see a few updates over the next few days. |
| 2007-02-12 | Uploaded two modules, one is a function that can be used to calculate target dates, taking into account a date and hours/minutes of work, compensating for weekends, but not holidays, and the other is the entire list of Excel 2003 constants and values as VBA global constants, useful when late-binding Excel in Access automation. |
| 2007-02-06 | Some of my recent work with NCR has involved verifying user input, e.g., Excel worksheets, as well as Access elements, so I created and uploaded several functions to handle checking of columns tables, and queries. |
| 2007-01-02 | I've improved upon the text-handling features of the code search, as well as enabled the use of "and" and "or" searches in the criteria. Additionally, the header image has been changed to a distorted photo of myself, instead of the New York skyline. |
| 2006-12-28 | I've added a new feature to the website, the ability to search code descriptions, which I will likely enhance, by expanding on the search capabilites. Also, although only of value for myself, I've improved the graphics used in the administration pages. |
| 2006-12-27 | Uploaded modules to verify data format, specifically column headings, on user-supplied data from Excel. |
| 2006-11-13 | After a long hiatus, my wedding and related honeymoon, I've returned to update this site. I am still working with NCR, developing a database to manage information between Cisco and ATT. In the near future, I expect to upload some recently develop modules. |
| 2006-07-17 | Starting a new spot as a Senior Access/Excel Developer, this one remote and at least for the next 3 months, with The Bardess Group at NCR. |
| 2006-06-22 | I'm winding up my assignment for Deloitte, converting a XLW from using SAS file-based data to use SQL Server as well as improving the entire workbook. I've been told that I've given them a new application, since the original was both ugly and written terribly. Also, I've added new features that weren't in the requirements, and overall, created a faster, more functional tool for a forensic accounting team. |
| 2006-06-18 | In addition to the code for backing up stored procedures to Access, I've uploaded an entire MDB, removing much of the work involved, so using this would require only modification to the connection string. |
| 2006-06-16 | Uploaded two stored procedures that perform redundant function, one for creating crosstabs (pivots) in SQL and one that converts strings to tables. Both are written by others. |
| 2006-06-15 | Today's upload is an Access module that builds a table of stored procedures. You define the database, as well as some local variables for output, and the code builds the table, if needed. The procedure then returns all the code within the database's stored procedures, which is then output to an HTML file, since the string lengths are too long for Excel. |
| 2006-06-01 | Uploaded an module of autosave procedures, including code to add a dropdown menu for selection of times to a toolbar. |
| 2006-05-10 | The demo of the chart drilldown for my client went well, and according to a colleague, the work itself is "on the top edge of Excel/Access capabilities." It certainly is unique. I'll be uploading the latest version today.
Also, my client has offered me an incentive bonus for finishing early, prorated by how many weeks early I can deliver the product to UAT. |
| 2006-04-29 | I've uploaded 2 modules to enable drill down into pivot charts via Shift+Click. |
| 2006-04-20 | Uploaded several new Excel modules covering logging, formatting, worksheet naming, and worksheet-focused illegal character replacement. |
| 2006-04-20 | My current spot at Deloitte focuses on automating pivot tables and charts, as well as SQL stored procedures, and I expect to have some new shareable code uploaded soon.
The prior programmer's work was terrible - complex code written without comments, indentation, and defined variables - so, in addition to fixing the code, I've been able to modularize numerous redundant components, in particular ADO/SQL components. |
| 2006-04-04 | My current spot at Deloitte is giving me the opportunity to develop classes for pivot chart/table events, as well as working with stored procedures.
Recently uploaded a module that exports all internal Access tables to Excel. |
| 2006-03-24 | I'll be starting a new spot with a Deloitte forensic accounting group, a short-term spot doing Excel VBA. Also, this past week I've had interviews with two (2) hedge funds for permanent positions, both of which I am hopeful about hearing from. |
| 2006-03-17 | Two XLA's were added, both were built several years ago by myself. One converts between dollars and euros - conversion value is acquired from the internet or set by the user - and the other adds 11 operations management statistical calculations to Excel, with explanations on usage provided via a drop-down menu. |
| 2006-03-15 | Yesterday, I had an interview for an Excel spot, directed the interviewer to this site, then realized the paucity of Excel modules, so I've uploaded several files for parsing, formatting, documenting, and printing. |
| 2006-03-10 | My portal site has been modified to XHTML Strict compliance, which required greater use of CSS, illumnating the necessity of separating data from formatting for XML delivery. |
| 2006-03-09 | Uploaded two modules recently, one a VB Script that loops through the domain to show login information, and a second that exports Access tables to files, strings of insert commands that will rebuild the data in Postgresql. |
| 2006-03-09 | The site is now XHTML 1.0 Transitional compliant. The next step will be to increase the use of CSS and move the site to compliance with the Strict standard. |
| 2006-03-08 | The W3C organization provides a very useful set of free utilities for learning and referencing X/HTML and CSS.
I am migrating the underlying code to XHTML - it is currently HTML 4.01 Transitional compliant - prompting my use of the W3C material to make certain that I understand the standard thoroughly. |
| 2006-03-05 | Updated underlying HTML to 4.01 compliance; the CSS was already 2.0 compliant. Added icons to the site's footer component with links to the W3C Markup Validation Service. |
| 2006-03-03 | Uploaded several modules to automate various actions in Access:
|
| 2006-02-27 | On this site, I changed routines that set and used cookies to routines that used session variables, for better security and process flow. This idea will be expanded to include various system messages. |
| 2006-02-27 | Several enhancements were made to the site:
|
| 2006-02-26 | I renamed the site to accurately reflect its content, to Code: VBA, PHP, X/HTML, CSS, since the site has expanded beyond VBA, partially driven by my interests in PHP. |
| 2006-02-24 | After vacationing in the Southwest for the past week, I'm back with some clearer ideas of how to code PHP. My latest work has been beefing up security using cookies, as well as streamlining some administrative functions |
| 2006-02-15 | I'm in the process of documenting the database I currently support, and instead of the original timeline of one month, I've been given a few days. Also, I was expecting to provide depth and detail, but the team receiving the application wants a "view from 40,000 feet." All-in-all, the process is going fine, and the target date, today, is really not the end-date, as I will be returning to UBS after my vacation.
A small personal note, I'll be spending the next week with my fiancee in the Southwest, staying in Las Vegas, Idyllwild, and Laguna Beach. |
| 2006-02-14 | I've made an interesting change to the site today. I created a file that I include in each page that uses the name of the current page to display a different title. I plan on using the same technique to selectively show a different search bar for each of the blog, code, and sites pages.
Learning more PHP, it becomes more evident how I need to develop my OOP skills, particularly developing classes. |
| 2006-02-13 | I spent a large part of the day trying to program a BLOB insertion using PHP into a MySQL database, with the express purpose of enabling me to upload and share more VBA code, and in the process I've developed a much stronger understanding of PHP.
The Nomura interview I had seemed a wash; the technical interviewer took a disliking to me before he finished sitting down, and little I could say could sway him from his negative conclusion. Still waiting to hear the final opinion on an interview at Morgan Stanley for a project management and senior management reporting spot. Everyone except the senior person loved me, particularly the people I was supposed to work with, the same people who performed the technical interview. |
| 2006-02-11 | Although I've known how to create forms for years, this morning I expanded my forms knowledge to include inserting data into a MySQL server, allowing me to add to this site's blog without directly accessing the server tables.
This minor improvement has also made me aware of how much I need to plan, so that I can avoid issues of SQL injection, as well as other security risks. |
| 2006-02-10 | I have an interview at Nomura Securities today, for a VBA spot; Nomura is working towards a financial data warehouse, with a range of technologies, and I will likely be involved in a reporting function utilizing Excel/Access VBA. Additionally, I've added a new module to the available list of code, for getting and evaluating the Windows UserID, as well as entered a link to a great Access-focused magazine, Access Advisor. |
| 2006-02-09 | PHP, along with CSS, have greatly enhanced my ability to create a low-maintenance, consistent, modular website. CSS has been around for quite awhile, but I haven't bothered to learn it until recently - I don't earn my money making websites - and PHP has given me the ability to use my usual VBA data management skills, creating recordsets and setting to variables, with a web interface. |
| 2006-02-08 | Just toying with PHP and MySQL, and enhanced this site, pulling database data with PHP. In the process, I've found wonderful ways to reduce website maintenance by using a more modular design. |
| Friday || 13:27 Z || 2010-03-12 |
|