Free Business Idea for You

Once in a while, a kaleidoscope of ideas pass through our minds and we would like it to happen via our own hands or even via another person. I do have some of these ideas, which I would like any reader of this blog to know and possibly benefit from these ideas.




Here are some of my ideas, just for you. Please tell me your success on any of this.



1) Computer Writing and Touch Screen System:

Turn an optical mouse into a PC screen writing gadget. Optical mouse have a low resolution camera. By using a software that coordinates what's being shown on the screen and a modified optical mouse, it could be possible to draw on the screen or turn this combination into a touch screen application.




2) Electronic Appliance Plug-in and Play Configuration

Design plug and play appliances. Once in a while we disassemble such common appliances like an electric fan. The ability to disassemble and reassemble is a good thing for the consumers. By extending this design specifications to include the major components like the motor or the switch mechanism is a good enticement for buyers. This will also help diminish materials (mostly plastic components) that are dumped into land fills.




3) Oxygenated Fruit Drinks

We all have some cravings for soda. Aside from too much sugar contained therein, these drinks release carbon dioxide in our systems. Make a healthier alternative by oxygenating a sparkling drink. That way, a tired athlete or any drinker gasping for oxygen is helped along the way with added energy.




4) Tube Puncture Film

For the Chemists, infuse a punctured tire with a non-viscous fluid and then add a catalyst. The spinning action will then mix the two to create an elastic film to cover minimal puncture.




5) PC Piano Keyboard

Back in the days of Windows 3.1 I encountered several musical applications that can use the sound-card to reproduce piano and other musical instrument sounds and rhythm. I liked them but the problem was the PC keyboard or the onscreen keyboard were too cumbersome to use for playing the notes. With a usb PC Piano Keyboard, enjoying and discovering musical skills will be more fun.



Well, my friends, that's all for now. If you develop any of them, beep me up and I may give you other ideas. ciao

Quirky Linux

A spirit wanting to be set free.




Yes, quirkiness can be very pleasant indeed.

Lest people think I'm a Microsoft junkie, I have to let out some Linux geek in me. Yes, I do dabble a little to find other things, just like I would really love to script some Open Office counterpart to my DB Acctg series (w/c I think would be good to release open source style).

And with that Linux would be the better system.

So what have you tried yourself, lately? Or would you be scared messing up your MBR with this thing called Grub?

Well, if you're floyding on it, go buy a surplus harddisk, unplug your existing system and put the thing. My advices are the following:

- be sure you know how to go to your BIOS settings 'coz you may have to configure your pc to boot on the livecd. Unless of course you happen to find whatever key it is in your pc to choose the bootup direction on the right time. Hint, try "F2", "F9", "Esc" or "Del" button while the thing is waking up. No, you won't delete the system. You might just go either to a bunch of options from which to boot from or to a BIOS settings window. And while you are at the BIOS, be very careful and perhaps have a manual to see where your bootup preference settings hide.


- so, you think you are brave enough to experiment on your brother's pc. Then I suggest Mepis, Slax/NimbleX and Linux Mint. And you don't know how to fit them in one old 20G surplus hard disk?




Then, just play around booting into a live Slax CD, or for that matter anyone of them. Fiddle and diddle your way to find a partition editor where you can divide the space into 3 parts.


- perhaps, you will find Slax or NimbleX to be the easier to use among the choices, but the other two will be more compatible with your scanner, printer, web cam or what not. Plus the apps available may be more like what you are used to in terms of look.

- So install Mepis first. There is a system assistant there, where you can renew the grub (the counter part of MBR). Nuts, you don't know what MBR is? ...Then it's the first thing read in the hard disk. It serves as the pointer to where your system resides. If MBR messes up and you panicked, you may get a crappy tech suggesting a reformat of the hard drive.

But, don't worry, your brother's hard disk is not disconnected for no reason. Be assured he will not lose his pictures of Ana Kournikova (before she got hitched). And for this ride, you'll be using grub (unless you want lilo, or do you?) Well, let's talk about grub.

- At last, you got Mepis installed and working (it's that easy). Your grub gives you many booting choices, which you may not like clogging the choices screen on a boot-up. Then navigate to home folder and click, going up further to the root and see the boot directory where a subdirectory grub resides. There, you'll find a file named "Menu.lst". Clicking it will open in a text mode and there you'll see some clumps of lines. Cut out the clumps or paragraphs representing the choices, you don't like.

- Now select partition 2 on the desktop. Create a directory named slax and another named nimblex. Since these guys are light and takes less space, the 2 live cds may actually occupy only 1 cd (actually). Copy the files to their respective directories. These 2 may run using the pre-existing grub that the Mepis installed. Now go to the nimblex directory and cut the subdirectory "boot", paste it on the root of that partition and then renamed it into "n". Do the same with "Slax/boot", only this time renaming the "boot" subdirectory into "s" on the root of partition 2.



Go to the Menu.lst and edit it by adding another clump like:

title NimbleX
root (hd0,1)
kernel (hd0,1)/n/vmlinuz-nx08 ramdisk_size=7120 root=/dev/ram0 from=/dev/hda2/nimblex6 rw passwd=urpassword autoexec=xconf;kdm changes=/dev/hda2/nimblex/
initrd (hd0,1)/n/initrd-nx08.gz
boot

Since you are using only the surplus hard disk (right?), the second partition is "hdo,1" because the first partition of the said first (and only plugged) hard disk is "hd0,0".

The "vmlinuz-nx08" and "initrd-nx08.gz" must be present in the "n" directory or you have to adjust the Menu.lst if the vmlinuz and initrd files were named differently. Do the same edit representing slax and then you may have another set of entries like:

title Slax
root (hd0,1)
kernel (hd0,1)/1s/vmlinuz ramdisk_size=6666 root=/dev/ram0 from=/dev/hda2/slax6 rw passwd=urpassword autoexec=xconf;telinit~4 changes=/dev/hda2/slax/
initrd (hd0,1)/1s/initrd.gz
boot

You've done that and wallah: You can boot Mepis, Slax and NimbleX!

- After a couple of days, you now wish to try Linux Mint but first, is it Linux Mint 9? If it is, be aware that Mint 9 uses another version of grub, But don't worry I'm here to ease your task. Copy first your "Menu.lst" file from the Mepis partition to a diskette or usb flashdisk or sent it via email to yourrself.


Now just install following the default options to partition 3. Boot and you'll find that you may lose your Mepis boot splash screens and that Slax and Nimblex were gone. That's because, Mint has taken over as the holder of the grub files and it's grub version has no Menu.lst file.

So select Mepis on the choices given by Mint choices-screen (another way is to use a Mepis livecd) and find the System Assistant where you have the facility to rebuild the Mepis grub version. Rebuild it and then edit the Menu.lst making sure that the entries for Slax and NimbleX are present. If not copy from your backup file. Then add the following to have Linux Mint 9 available:

title Linux Mint 9
root (hd0,2)
kernel /boot/grub/core.img
savedefault
boot

Now boot and you have a hard disk that can boot Mepis, Slax, NimbleX and Linux Mint 9.

So you are a geek afterall as linux users are! But then your brother is coming from his weekend trip.


You have then a choice of unplugging your linux hard disk, putting your brother disk in its place. Or you may convince him to be a geek as well and plug his hard disk as a slave. (Yes, man brought slavery even to his toys).



Then do the following:

1) Set his hard disk as a slave by following the jumper settings from the markings (or hard disk manual).

Then edit the Menu.lst file and add the following entries:

title Windows NT/2000/XP
rootnoverify (hd1,0)
savedefault
makeactive
map (hd0) (hd1)
map (hd1) (hd0)
chainloader +1


Reboot and voila! You can boot even Windows!

Now, it all started with your quirkiness.

You can now be out of the Windows-box, to another paradigm.

-------------------

Cheers and heres an old-time quirky music to your new world:

Utilities, Add-ins, References

There so many references and materials nowadays to help us in our PWORP (programming without really programming) endeavors and may support the series DB Accounting. The latter is not limited to accounting really, but it is a conceptualization of Distributed Data Collaboration. It is applicable to many more systems development on an "As-It-Happens" time-frame or just a measure to store data in acceptable formats complimenting mainframe implementation.
_____________________________

The utilities below are compatible with Windows/MS Office environment which is the typical set-up.

1.) Tools are available, right there, in the applications we used. Among them, are the many wizards which we rarely use. This is perhaps, because our actual business requirements have their own quirks or because such wizards have no long lasting re-usability. However, with Data Coordination and Collaboration among these applications, the relevant templates and wizards will be of great help.

a) Here are some worksheet templates available under MS Excel (it may differ according versions):















b) MS Word Templates










c) MS Access also contains many ready made solutions and database wizards.

d) There are many other templates here:
- MS Access templates
- Microsoft templates
____________________________

2. ) In Addition, here are some other non-Microsoft utilities which you can download and integrate into your work-in-progress computerization:

a) Online csv viewer

b) CSVed (this can complement our DB Acctg sample of spreadsheet form using CSV Data Container)












c) J-Walk Enhanced Data Form Add-in

d) For Task management and reminders with little performance overhead, Skynergy's TaskPrompt may fit your requirements instead of MS Outlook





e)Application Managers and Organizers (can serve as an integrator switchboard for calling a Distributed Solution like in our DB Acctg Series)

- Stardock's Fences as seen below:


- Launchy as seen in this techmixer page
- AppManager from codeplex.com
- Folder view from tucows
- still others more
___________________________

3. ) For reference, the following videos and links may help you find the channels or sites that will give you step by step solutions for your needs:

a) Using Excel 2003 and below Dialog-boxes:



b) Data Validation:



c) Using Sparklines (Dashboard purposes -Excel 2007/2010):



d) Advanced Dashboard samples

e) Creating a Macro:



f) VBA code samples and reference

g) Conventions for declarations using prefixes

Acctg DB: CSV as Data Container

So we talked about The MS Office team. It's like the Chicago Bulls during it's heyday. MS Excel is our Michael Jordan and MS Access is our Pippen. What a tandem! Nevertheless, we need a center player and more, to handle the data. We are using data like a ball, we pass it in many creative ways and have it thru the goal in equally many ways.

Using SQL Express is like importing Shaquille to our team. But for now, there are many other center players. MJ and Pippen could be enough to carry our project, but there are many times that personal differences may happen. That is just like the data coordination using Excel and Access. Things may get in the way that I will have difficulty to explain to the intended layman audiences.

For automating these applications, we need the coach. The audience would not care about what length the coach has to do, to get them to work. So I decided to make an Excel "Add-in" for these matters. Running a team costs money and I may not be able to share this add-in freely.

So for our enjoyment, we're bringing in another center player - CSV. With this, we skirt the thorny issues between MS Excel and MS Access (automation - like security, hidden instances, etc.). We are also able to distribute the burden to a fairly versatile database player.

According to Wiki:

- "A comma-separated values (CSV) file is a simple text format for a database table. Each record in the table is one line of the text file. Each field value of a record is separated from the next with a comma. Implementations of CSV can often handle field values with embedded line breaks or separator characters by using quotation marks or escape sequences. CSV is a simple file format that is widely supported, so it is often used to move tabular data between different computer programs that support the format."





It is a very adaptable player and can be handled by the nuances of other applications or team players.

According to Wiki:

- "The CSV file format is very simple and supported by almost all spreadsheets and database management systems. Many programming languages have libraries available that support CSV files. Even modern software applications support CSV imports and/or exports because the format is so widely recognized. In fact, many applications allow .csv-named files to use any delimiter character."


So here is the beginnings of a triangle offense, that you can easily follow with some modifications as needed:




We are using the file "List.xls" we have earlier discussed. The said file has a VBA code for posting to MS Access but we are going to another range to create a version for posting to CSV.

We created a user form on an area. We have created a tabular container linked to that form and we have another table that will store each entry, accumulating them in rows of data. The same form or another sheet can be used as a Dashboard for viewing the data before eventually posting it to the CSV holder (but we are not showing it just now - the "viewer or dashboard").

Note on the video:
Part A)
- we are showing on the earlier part a review of how the List.xls file was able to undertake posting to MS Access.
- the "=COUNTA(C7:C12)" formula was used to determine the count of rows to post
- concatenation using the formula [="C7:G" &] plus the determined end row number designation was used to get the text containing the address of the tabular data to post (range name with dynamic dimension is not visible to MS Access)
- this address is complemented by the VBA code: [ Sheet1.Range([j5].Value).Name = "usrlist"] viewable on the VBA section privy for sheet1, w/c redefines the area covered by the range name
- there are also various notes/comments shown which reflect a possible way to determine completeness of the info needed - via count of the dimension of the table
- the code however for prompting and disallowing the posting of incomplete data was not included for now

Part B)
- we created a spreadsheet form via cell format, borders, sheet background picture (w/c was also made available on a separate video before this series installment)
- Selecting disconnected cell is done by pressing the 'Ctrl' button while we are clicking various cells or ranges. That way we can quickly apply similar formatting.
- The single row table is directly linked to various input cell area and is used to data-form it for posting to the temporary tabular storage
- the temporary storage range which will accumulate each posting for review is directly below
- We used the record facility of Excel to copy paste value from the data-former to the temporary data container
- The said data container can serve the purpose of final validation, as in when a check voucher is forwarded to the accounting manager for approval.
- Notice that for simplicity we have created no id field yet (w/c will be helpful when operating searches for deletion or edit of a specified row entry)
- For your purposes, you can also include the Data validation feature of Excel (for example on the input cell for age - to limit it to, say, '18' to '50' yrs)
- You can also create formula using the "If" functions, to catch when an input cell has undesirable entry (like "0" for middle initial) or to replace that entry by other characters (See Excel help for "If" function)
- Because of video length limit, we did not include application of cell and sheet protection

VBA Part:

More than 5 years ago, when I was doing a similar DB Acctg System, I was not fortunate to have an internet connection whether at home or office. Now, I'm glad, because of the wealth of materials available with which you can improve or build upon these techniques.

For this exercise, we are perusing MrExcel's forum post (opens in new window). And editing it to our taste. Kudos to "TommyGun" who posted the code, made available for us.

- we commented the MS Access code for posting the earlier section of the spreadsheet
- note that the code here is using the shell approach of calling the mdb file and also note that the mdb file has an "AutoExec" named macro that transfers spreadsheet data to another mdb container and then closes itself automatically

- for the "CSV" operation, we created a module and pasted the code from the forum
- then we replace the "*" character with null so the code is usable
- we edited the target CSV file: we included the path of our spreadsheet (but you may adjust this if the target file is not on the same folder as the spreadsheet) and we rename the target file to "UsersDB.csv"

The modified code will depend on the following cell contents (which may differ with you):






note:
-test data are already used and some were posted to the temporary storage
-there are formula for certain cells (cells which will be used by the code execution to find where to append and what data are involved)
- the file exist has a value of "1" if there is already a CSV file existing to make the append with or without field names

The temporary storage append is as follows:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/27/2010 by ****
'

'
Range("C33:H33").Select
Selection.Copy
Range("C" & [p37]).PasteSpecial Paste:=xlPasteValues

'replaced macro recorder codes:
'Range("C36").Select
'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

On the other hand we made some adjustment to the code for appending to the csv file as follows:

Sub Append2CSV()

Dim tmpCSV As String 'string to hold the CSV info
Dim f As Integer

CSVFile = ThisWorkbook.Path & "\UsersDB.csv"

f = FreeFile

Open CSVFile For Append As #f
tmpCSV = Range2CSV(Range([P36].Value)) 'we used a pointer cell to our source range
Print #f, tmpCSV
Close #f
[P33].Value = 1 'this is our signal that a file already existed and add'l append will skirt the field names
End Sub


Also we made a slight change to the function portion, because the code presupposes that the table to append starts with "A1" or first row as follows:

cr = list.Row '1 '''make adjustment to validate the starting row of table: if file is existent already don't include field names

Also we have defined the delimiter in the following section of the said code:

For Each r In list.Cells
If r.Row = cr Then
If tmp = vbNullString Then
tmp = r.Value
delimiter = ","
Else
tmp = tmp & delimiter & r.Value

At the start, the delimiter is null but gets the value "," thereafter (we can adjust this value if needed).


Conclusions:

We have found the starting technique for using csv file in our IT system. MS Access should have no problem linking from such files and then combining them based on some field IDs that are common to various data files gathered.

The implication is that if we create a system for PO Form (for purchasing) and another system for receiving (by the Warehouse), there is then a possibility of cross reference (all the way to an Accounting system, to the HR's mail-merge letters for certain parties).

Also if you are presently using "Quickbooks" or "Peachtree", these strategies can be used to extend the system. All you have to do is study the csv-import field requirements of your existing systems. This will be useful, especially that each business has a different pre-printed transaction tickets like the Check Vouchers, Checks, etc. which usually require typing. So we can type on them and capture the data for export to an existing system.

Most of this effort stems from our conceptualization and the spreadsheet know-how, which we can expect from most office personnel.

If this series may help you then subscribe! Enjoy.

Acctg DB: Pros with Cons

All right. Automating MS Office is no laughing matter. For example, the files I made in the article "Fast Forward" of this series, works to post data from Excel to MS Access.

Oops, I forgot to tell, that to make a procedure (VBA code) callable from different sheet or button or worksheet, you have to transfer it to a module. Yes, it may confuse a layman, so I decided to employ a different approach. To adhere to PWRP (Programming Without Really Programming) principle, I decided to make the complications of these matters to myself. You may not know how to stop an invisible instance of Excel via code or such issues that spun my head when I first implemented this years ago. That is why I decided to make an Add-in which you can call within Excel to implement the system with "KISS" principle.

In addition, I do not want to antagonize my JPIA and accountant friends with issues like MS Excel and MS Access being so hackable, so I am incorporating some security in the add-in that may help bulletproof the system. But doing such things are tough, I accede. However, I am conducting my own tests, and the strategy I found is fairly good (so far ... security vs cracking/hacking involves many aspects).

In the meantime, going back to the series installment, here is how you may create a data capture design within the spreadsheet: Video (opens in new window) to the tune of "El Bimbo" by Eraserheads.

The said video illustrates the design of forms using the spreadsheet. There is another back-end design strategy that involves going to the VBA window (can be seen by pressing "Alt-F11"). But I prefer to expose the user to the spreadsheet for the following reasons:

1. The user being at ease with UI (user interface).
2. Capability to populate the data by formula or reference to an existing Excel data.
3. Capability for multi-row data (like the items listed in a Purchase Order).
4. Ability for inter-field data dependencies (even inter-row) and dependent calculations.
5. Images, textbox and other drawing features can be utilized.

Anyway, the video shown above is about the following:
- changing the 'backgound picture' of the spreadsheet
- using 'fill color' on selected cells or range of cells
- creating 'merge cells' for data entry
- formating the cells for the following effects:
*sunken effect - using "Cell", "Format", "Border": combining dark single lines for top and right borders together with light border lines on the right and bottom areas
*raised effect - using light border on top and left sides with dark double border on right and bottom ( the top and left sides can also use double border styles for this effect)
- combining the cell unprotect and the sheet protection (can be password-ed, select unlocked cells to restrict the user selection or focus to specified cells and hide formula)

- the 'data validation', 'cell concatenation', 'conditional formatting' and other formatting techniques can also be employed here (see previous portion of these series or look for Excel help, in case additional info is needed for these features)

In the meantime google "prcview" if you need to stop any process that may be running invisibly, in your course of experimenting with some automation.

Also, there is a free useful app "TaskPrompt" from skynergy.com that can be incorporated in any system development consistent with this series. However the accompanying 'mdb' file of said download is password-ed. So if you want to interact with it with another interface, you have to ask them.

Ciao and enjoy! And watch out for my Add-in coming soon.

What now for Pacquiao

With Mayweather pretending that no, nothing was ever forwarded to him by word or call or paper regarding a fight with Pacquiao, it's now time to move on. Maybe Arum was avoiding a piss fight with the Mayweathers and he coursed all signals through HBO. Maybe HBO's Ross Greenburg have too many movies and shows to attend to and slept on it all these time. All we know is that these non-event is now a history. Unless, the parties involved, got back from amnesia, there is nothing else to do but go to other endeavors.



With the Pacman already a historic name, not only because of the game, but also the pinoy pugilist, the man is now imbued with more responsibilities as a global symbol from the islands. And so with his new job as congressman and stature in the sport, here is what I contemplate as an unsolicited advice.


1) One More Fight

- I think the people who are supporting and benefiting from Pacquiao's fight are egging him to fight (other non-chickens - pardon the pun, ' can't help it). American boxing may need some ironing out. Sadly many Americans consider boxing dear to their hearts, but when a fight that the sport requires can't be made with the existing rules and institutions, then boxing may need to go to alternative stage.

For me, Pacman may opt to concentrate in his duties as a congressman. But I think he has already committed to a fight date with or without Lil Floyd. So fight, he may, but who and where?

I join others who have misgivings about a fight with Margarito. Marg must have his day in the court of his peers and the authorities of the sport. As Pac's fights tend to be a historic event, Marg might get better reward than Cotto and that would be a shame. So for Pac, I am thinking about other names - even other than Cotto! Yes, Cotto has a new title but I think, how about Sergio Martinez?

Latest Tale of the Tape for Sergio Martinez:
----------------------------------------------------
Ht. 5'11"
Wt. 159
Age 34
Reach: 76"
Style: Slickster\Mover
Best Punch: Straight Left

Many Americans who want Pacman to lose (for some ignominious reasons) would recommend Paul Williams with his 82 inch reach and a towering height of 6'2". Martinez is the current WBC and The Ring World Middleweight Champion, beating Paul Williams.


The height and reach advantage is not that far. And fighting in a catch weight, an exhibition (even without title on the line) will be easy to sell. If Pacman wins, he can be an unofficial 8th world - 8 divisions People's Champ. Well going for 8th is too much for any one starting at flyweight, so a friendly but competitive match even for a 10 rounder will be in for global anticipation, held at a new global arena.


What would be the venue? Well, I'm done with the Nevada monopoly of big events and even with HBO (trying to pamper Floyd - to the disadvantage of other fighters). Cowboy Stadium, Abu Dhabi, Argentina, heck even the Philippines! That last thought should catch the new Philippine government - that would be a coup PR job!


2) Congressional duties

I would suggest that Pacquiao must utilize his goodwill to create endeavors that transcends his congressional duties. I suggest that he give to the people more than is required to get elected. For example, I suggest that since he is willing to dip into his pocket, he should create new system of program implementation. Let's say, he will be establishing a new medical facility. I suggest that he involved the whole community in a "Bayanihan" like project of putting that up. Let's say his district has a feeding program, I suggest, Manny gets to pay local people who brings in local produce which will be prepared by local people - they get fed, they got to have some pocket money and they gotta get busy producing food!

Also, I suggest, Manny have some tour around other countries were poverty is endemic. A Filipino entrepreneur has created folding house. This will be a good way to bring along, to even in the many American homeless victims of bank manipulations. That will provide some employment to Filipinos (not to mention, goodwill) and relief to the country visited. Plus, with this, perhaps he might not need to go to court to get his name cleared after all. He can use a little empathy from the otherwise jealous guys!


3.) Other sports

Even as a congressman, Manny can delve in other sports. He could even be a good ambassador of other less known event. How about Philippine baseball or football? To be his team mate will be a big honor to a budding player (even if Manny would play sparingly). Yes, he is an advocate darts or biliards. I think any such event will be well attended it Manny gets to play in doubles.

Lastly, because of his stamina, I suggest Manny participate occasionally in marathon. The New York marathon will be a good way to show his physical capability. This is an event where it is advisable to be runner.

Who knows, the Americans may egg Floyd to run too for the sake of their non-event!

Acctg DB: First Things First ...the FS

... continued

Here is the generated FS from the pivot table we have corrected as to the grouped accounts and the preferred order conducive to the FS presentation. I also created a modified database and used it to generate a pivot.

By going through the said file, you may have an idea of how the pivot table have helped us in our generation of the FS (Financial Statements).

If you would like to try other pivot views, just create other sheet tab and copy the pivot there. Because of the flexibility of pivot tables, putting other data near them is not really advisable. The pivot table can grow or shrink in area occupied, as you fiddle with them.

Also try to click an account title and it will create a drill-down detail in another sheet tab. This is the ledger posting that will serve as T-account of the book keeper. The created detail is a snapshot of the underlying data. If you post additional data to the pivot table source, the previously generated drill-down data is not able to be refreshed (the pivot table can be refreshed anytime as long as it can still locate the source data). So, you may have to create another drill-down operation to create an updated ledger of the said account.

Notice that the Balance Sheet for each month is composed of cumulative figures as of the end of that month.

The Monthly Income Statement (IS) is (usually) not cumulative so that owners can gauge the effectiveness of that month's operation (w/o dilution from previous month figures). There is, however, a carry over (cumulative) income or loss figure at the bottom of the monthly IS. The previous cumulative income or loss when added to more current month will result to the current Year to Date (YTD) income or loss. This, in turn, is carried over as adjustment (not formal adjustment) to Owners' Equity in the Balance Sheet.

It is only at the end of the Year that the formal transfer of cumulative income or loss is recorded formally as adjustment to the Owners' Equity (zeroing out the Income Statement accounts with that formal transfer in the books).


This exercise have given us a glimpse of the all around capability of Excel to handle data and serve the needs for record keeping, calculation, database of small business. The database accounting approach must be shared to accounting students and practitioners, because the tools within Excel alone will be underused if we stick to the conventional repetitive recording/transcribing from journal to ledger to special books to FS.

The manual Accounting know how, usually taught in school, requires a lot of manpower even with the use of computers (but it is important for understanding the concept). With Excel, Access and Word working together we have a formidable team to create a computerization system as the transactions developed.

Not to mention that we can include the other MS Office members and even bring in a web-enabled Database server to the mix. It is a shame, like a Mayweather and Paquiao non-event, to not be able to take advantage of the tools we have.

Buying a full-blown preconceived, pre-programmed ledger software is not an option in many instances, because in most cases, people cannot pre-determine the quirks and deviations that arises from the business. A new business inventory software, for example, can be foreseen but actual evaluation, testing up to purchase and deployment takes time. All the time, with the tools at hand, we can create a Distributed Data Collaboration in an As-It-Happens development phase.

So far we also noticed some limitations of Excel for book keeping: like the Ledger generated from the drill-down which is not live. Michael Jordan cannot win by himself all the time. In this instance, we have MS Access to help in handling ad hoc queries and reports where it would be cumbersome for Excel.

We have not yet even handled how to create the user interface (UI), or how we will post either to an MS Excel data container (like we did) or MS Access tables. What about, how the special books (like Cash Receipts Book or Sales Book) can be coaxed out of our general purpose data or ledger container tables? Or even about how we can print checks or voucher details on the pre-printed forms?.

But I hope you got a glimpse both in this "DB Acctg: First Things First" part and the "DB Acctg: Fast Forward" portions of the series.

Do check the other earlier parts of this series, if you have not yet done so.

In the meantime, here are some files related to "DB Acctg: Fast Forward" portion:
1.) List.xls
2.) Data Container (proxy for MS SQL Server)
3.) MDB file companion to 1 and 2 above
4.) MJ.xls Alley Oop Slam test file
5.) Pippen.mdb Alley Oop Slam test file

Put these files in "C:\DBAcctg\" folder (create one).

Subscribe and enjoy!

Acctg DB: First Things First ...continuation

... continued


As you can see, the database as generated by entries previously posted in the first part were done from the viewpoint of a non-accountant. It resulted from some redundant Account Descriptions which a database system being a machine will consider differently. For one, the "Checking Account" title must be separated from the check number used. Therefore, the database needs at least another field. Also the data entry could have employed some data validations which forces entry of Account based on a predefined list.

( See here is a sample of "Data Validation", "Range Name" operation and "VLookup" formula which would get to you the idea.)

( See here how to make the "Data Validation" source dynamic to accommodate changes from the source list. In addition you'll know about the "Offset" formula.)

Going thru our pdf workbook we find that for such reason accountants used what is called "Chart of Accounts". In this case, this one:



Combining "Data Validation" and the "Chart of Accounts" as the source for data entry we would have gotten a nicer Pivot table result for generating the Financial Statements (FS).

However, as it was entered we can play around with the "Pivot" table to create our FS.

We can select a contiguous row of field names, then right click to classify (or group) them as one:


We select group from the dialogs that appear and a new column will be inserted to reflect the original Description and the grouped items - named as Group1.


(The fields can be dragged into place, if needed, to make similar fields contiguous so that we can apply the above-mentioned grouping technique.)

By going to the cell where 'Group1' is used as label, we can then type the more accurate "Account" title using the formula bar.



We also notice that the date field was not sorted right by the Pivot because our entry was in the form "Dec/2010" and "Nov/2010". I suggest that we should have more precisely entered the date as in the form "mm/dd/yy". Excel will automatically format and recognized it as a date entry. We could then have another column (field of data) in the form "201011" and "201012" which will be useful for pivoting based on months (this will be sorted correctly).

For this session, we just edit the months to "201011" and "201012" and drag the latter to the right side of the column for the month of November.


... to be continued

------------
Data Validation:

Acctg DB: First Things First

To refresh a DB man about accounting here is a page created by Erin Lawlor.

I really wouldn't want to discuss how the accounting books is generated traditionally and browsing for one, I found this page simple and yet comprehensive enough for our purposes. So many thanks and credit to Erin. Do peruse said pages if ever you want an accounting refresher.

We will try to use the journal entries from the download-able file shown in the said page: workbook.pdf file, starting at page 11.

Entry No. 1:


Using a DB system to store such entry, I would suggest the following:

I). I suggest that the second (and succeeding) line entry will have the added details as included in the first line entry which are the date and reference. But for compliance (to accountants' convention) and readability it can be made invisible by making the added details' font color white or using the cell format code: ";;;"

II). I also suggest that line entries have a copy of the description via another field which we may call "Particulars" and such is made also be invisible on the interface except on the first line.

III). Adding a field named SeqID (for sequence ID) so that when such line entries are gathered in a central database, the original entry sequence can be recreated.

IV). Adding another field DrCr which will be positive if the line entry is a Debit and negative if it is a Credit. Such way we may pivot the data container using the DrCr amount.


-----------------
If you are unfamiliar with "Data Pivot" you may see some examples here (opens in new window):

- by :onewayslim
- by : 60secbusinessinsider
- from : Mr. Excel

or see this

-----------------


Entry No. 1-16:

Following the suggestions I to IV above, I have gathered all the entries in one database container and here it is.


If you select contiguous rows of line entries, you will notice that reference, date and particulars are stored on each line as seen in this screen capture image:


Sorting or pivoting the data therefore will not result to a lost of trail.

Here is a pivot of the aforementioned source data:




. . . to be continued

---------------------------------
Procedures to follow suggestions I:

a) manual cell formatting:


b)You can automate the application of formatting invisible by following this:


Your code may be like this:

Sub Macro1()
'
' Macro10 Macro
' Macro recorded 7/15/2010 by ****
'

'
Selection.NumberFormat = ";;;"
End Sub

Now you can call such macro by following this (opens new window). Or by placing a "Forms" button or image on the interface like this.


ciao

Acctg DB: Fast Forward

It took quite a while to update these series. I was thinking ahead and was shopping for an import player to play along with our MS Office team members. Looking around for a robust Database container is not that easy. My criteria were: - it has to be easy to work with, given the member players we have, it has to cost us nada and it has to be able to work in a networked system like a small business.

I tried and tested several but I found out that MS SQL Server 2005 Express is free for download and I tried it with satisfactory result. So I decided to include this as the Shaq center of our MJ Excel-lead team.

It is download-able at: MS SQL Server 2005 Express

And here I will show you a technique I will name Alley-oop Slam (data alley-oop slam - that is or DAOS). It can be compared to a Michael Jordan alley-oop to Pippen for a slam dunk.

Let's take this sample Excel list:

Name the range, say "usrlist". Then save the file as List.xls.
Open a blank MS Access database and create an MS Access macro as follows:













Note the following parameters:
Action: TransferSpreadsheet
Transfer Type: Import
Table Name: tblUsrList (.. referring to the destination MS Access Table container)
File Name : C:\DBAcctg\List.xls (.. .. please adjust depending on the directory and filename from which the data is extracted from)
Range : usrlist (.. the range name of content area from the spreadsheet)

Now if you name the MS Access macro as "AutoExec", it will run whenever the MS Access Database is opened. Therefore whenever we are in the Excel spreadsheet we can execute the DAOS by a VBA code that opens the MS Access file.

You can test it from the Excel UI by clicking on the 'record' button of the Excel 2003 "Visual Basic" toolbar and then clicking the 'stop' button. Press 'Alt' and 'F11' (simultaneously) and you will be taken to the VBA Editor window where you may complete the VBA code as follows:

Sub Macro1()
Shell "MSACCESS.exe C:\DBAcctg\Database1.mdb", vbMinimizedFocus
End Sub

(* note - The "Database1.mdb" above must be adjusted accordingly if have you named differently the Access file and/or the folder.)

For this to be executed properly we have to create some adjustments.

1) Our Excel VBA code must clear the list when it is already posted.

2) It must make provision for possible changes in the number of rows to be posted by readjusting the definition of the range name "usrlist".

3) It must contain a code to open the MS Access Database so that the data pass is communicated. (The above code will do)

4) The VBA Excel code has to get a signal from MS Access before clearing the posted data from the spreadsheet list. The signal can be via a refresh of existing pivot table with count operation of the data contained in MS Access.

Some other fine tuning can be as follows:

5) Creation of another MS Access file that will be the real container of the data. The table to be used in "Database1.mdb" will be linked from the Ms Access data file container (or MS SQL Server 2005 Express - if so warranted in the future, especially for web-enabling the system).

6) Adding the "Quit" or "close" MS Access macro line so that the file is closed after execution, leaving us with the spreadsheet interface.

7) Renaming this data transfer macro to "AutoExec" and fine tuning the Excel data encoding interface (via validation, conditional formatting, protection, etc).

For those who are more conversant with Accounting (and not even VBA) rather than Database, don't worry we will proceed on your phase of learning. So the code above is enough for now. You may also manually run the MS Access macro to test if data pass is successful.

The point here is, we have the capability to use MS Excel as a very adept player to handle the ball and create the openings before data is passed to it's proper container. Excel will be good especially as the computational capability will be there in the front-end of the Accounting DB system. That means we don't need to open a calculator application when encoding data. We have more than enough on the UI (user interface) to do even such complicated automated data population like in a complicated mortgage schedule. I have watched certain Oracle programmer automate this system and I can say they are not able to do it as easily.

Anyway, we will shift back and we will employ as much PWORP (Programming WithOut Really Programming) in next installment of this series.

Subscribe and enjoy!


-------------
Creating MS Access 2003 macro:


Creating MS Access macro (version 2007 & 2010):




note:

My examples will be using MS Office 2003 for backward compatibility. The MS Access macro above is captured from MS Access 2007 (they are essentially the same). Going around in MS Office 2007/2010 will be different because of the 'ribbon' interface design. For example, the "Developer" tab in the ribbon contains the buttons formerly in the "Visual Basic" toolbar of 2003 (and earlier). The MS Access 2007 macro creation is shown when you select the "Create" tab of the ribbon. The "TransferSpreadsheet" macro action in 2007 has some adjustments.


ciao

Acctg DB: Intro

To simplify the matter, let's present a top view of how the generation of books could have been done using a database approach. Without going though the official definition of the accounting process, let's just compare it to the manual municipal tabulation of votes. The usual procedure is to tabulate the result in specialized grid ledgers or forms. However, imagine that you tabulate at least 2 kinds of result per vote: a positive and a negative vote. That means one votes for a person he likes and at the same time votes for one he dislike in that position.

Such is the nature of double entry book keeping. If you are not trained or careful, you may forget and leave out other effects of a transaction. To tabulate it, you have to double the column for a particular item: one designated as a 'value received' and a 'value parted with' effect - the debit and credit sides. Single entry tabulation is cumbersome enough done manually, doing a double entry tabulation and transcription to various ledgers will at least double the time required or the manpower. With limited personnel, a db system can be made where the journal is simplified but still can generate the specialized grid forms like cash receipts book, cash disbursement book, debit/credit memo, sales book etc. With manual system, one go through all these forms separately.

For our purposes, the following will be our wish-list of the target we have to accomplish by creating this Acctg DB System:
1) Callable templates for recurring transactions
2) Central posting database from w/c various grid forms or tables like CDB, CRB, APBook, etc. are automatically generated.
3) Source transaction tickets or vouchers are generated automatically
5) Check Writer capability
4) Balance Sheet and Income Statement can be generated at will

You say the targets we list are impossible to do with MS Excel or a DB system with our layman capabilities? Well, we will try to be understandable by average layman and Excel with a DB Backend is quite capable to do it.

Stick around as we develop this system.

Acctg DB: Backgrounder

You pass the years of schooling, as well as the gruelling board exam. You are accepted in a young company and you realised that you have been tasked to reconstruct the books. The company had Financial Statements that are fairly suspected to be made of scientific estimate and with only a few trace of how the figures were turned out. It's a little consolation that the filing clerk have accounted for all the transaction vouchers. The owner doubts the valuation of the manufactured inventories and with it the income arrived at by the external auditors. Their working papers remained with them and there is not much books to speak of. What do you do then?

Such is the predicament of many companies. They were eager to operate and may have forgotten about the books, busy with their inception activities which morphed the earliest CPA employee into an Executive Secretary. The previous person who was later called in was successful to make himself be pirated by another company. The owners thinks you can reconstruct the books in 3 months and the Executive Secretary (who is more dedicated in hobnobbing with non finance executives) has promised them that you can easily do it. You and the lone filing clerk make up the accounting department of the yet small incubating company.

Now, you realised that the manual intricacies of financial record keeping taught in the schools may be insufficient with your department's manpower. You are reconstructing the books of previous year and processing/tabulating the expectedly growing current transactions. Realistically, you'll be hard-pressed to make it in 5, much less in 3 months. So you wonder whether there's an accounting software to arm yourself with. Luckily there are plenty nowadays. But do you have the time and energy to do the previous 2 and learn this new software at the same time. Perhaps even to suggest its usage and go to the expected bureaucratic process of evaluation onto acquisition of the software will take a month while the vouchers to prepare and tabulate keeps filing up.



This is a scenario I brought up to make a point: - the outdated mode of bookkeeping which we have studied at school. The system we read on books is a fairly manual cross transcription of the entries from the vouchers to the books, ledgers and then summarization into financial statements. Non-accountants, like most owners, think you have computers so 2 people in the accounting will suffice. They had difficulty themselves taking their few Accounting units and they believe you have great ability going thru years of schooling and even passing the board exam.

This is such a large reason why I have to create this series of topics in this blog. The Accountant is pretty much the 'Database' personnel of the financial angle of all business transactions. Whether you use the current Accounting software available or not, whether you come from the programming side or Dbase administration or you are the owner of the business, you will benefit from this series. We will learn the Excel spreadsheet nuances and it's versatility as well as it's inadequacy for such a task.

We'll try to be simple and layman in our approach. We'll use Excel like the Michael Jordan of our tools and later we have to get other Ms Office players involved as well. Perhaps we may later import a high capacity database player like the Shaq in our ranks so that we can be prepared for the possibility of online access by our demanding jet-setting owner.

Stay tuned and subscribe to this blog. We will try to have fun learning a ton!

Excel 2007/2010 Classic Ribbon Tab

This, I think, is by far the easiest way for you to bring your favorite icons/buttons in the way you like 'em arranged under a 'Ribbon Tab' of your updated Excel 2007/2010. I am bringing it here because I know some of you dudes have recently upgraded at your office and it will be a hassle to work your deadlines under an unfamiliar interface.

Steps:
1) Open your old Excel application or any pc where it is not yet upgraded.

2) Right click the toolbar area and select 'Customize'. A toolbar customization dialog will open. Select 'New' and a blank Toolbar, ready for you to populate with icon will appear. You can name this toolbar any name.

3) Drag your selected icons or menu item from those you see into the new toolbar. Place it in the sequence you are most comfortable with. You may also click the 'Commands' tab of the 'Customize' dialog, as shown here, to reveal other icons that you think you may need.

4) Take care that you do not over extend the new toolbar beyond the screen. If needed, you may create another blank toolbar to populate with icons or menu items. After you are satisfied, select the 'Reset' command button on the 'Customize' dialog under the 'Toolbars' tab as shown on the screen. This will repopulate the default Excel toolbars (you don't want to piss the user of that pc with missing icons).

5) Now, click the 'Attach' command button. This will make you carry these toolbars and buttons with this particular workbook. Save this blank workbook under a name you can easily remember and someplace with your network (or usb/diskette) where you can retrieve using your upgraded Excel version. When you open the said workbook, it will appear under the 'Addin' tab of your upgraded Excel ribbon.

There you have it. Congratulate yourself, you have programmed the ribbon without really programming! Bear in mind though that if you download any other add-in with it's own ribbon customization script, the buttons may clog on that same place. You may also reduce the space by right clicking the icons and selecting delete toolbar. Or you may learn the 'XML' scripting of the Excel ribbon (the steps of which, you may have otherwise already googled by now).

Anyway, you've learned the easier way here. And I will share some more ways for you to program without really programming, until one day, we may wake up and find out we are already programming for real! Enjoy.

Sample Classic / other fave icons imported into Excel 2007 (Note some buttons may have a different button/menu counterpart in the new versions):