text: Principles of Mortification: www.prshockley.org Positional Sanctification Justification The spiritual life of the believer is the life of Jesus Christ reproduced by the child of God by means of the Holy Spirit Obedience is our responsibility: Gal. 5:16 Perfect Sanctification Holy Spirit is Transformer: 2 Cor. 3:18 Active - Passivity Doing - Undergoing Natural Person Faith is the instrumentmoment-by moment Carnal Person Trust Alone in Christ Alone Deliverance from the Penalty of 10/3/2015 Sin The Holy Spirit yields resultant fruit. Spiritual Person Trust Alone in Christ Alone Not merely a decision, but a life of consecration. Not merely consecration, but separation. Not Template copyrightbut www.brainybetty.com 2005 merely separation transformation. Progressive Sanctification Growing conformity to Christ by means of the Holy Spirit. Freedom from the Power of 2 Sin VIVIFICATION Walking: Controlling: Gal. 5:16 Eph. 5:18 Yielding: Rom. 6; 8; 12:1-2; Phil. 2:1-11; Col. 3:12-18. Accurate Knowledge & Obedience to Scripture: MORTIFICATION Confession of Sin: 1 John 1:9 Mortification of Sin: Rom. 8:13; Col. 3:5 Ps. 19:7-11; Rom. 12:2; 1 Pet. 2:2 Pro-actively depending upon the Holy Spirit in the details of daily living (vivification)-moment by-moment (time is a succession of moments). Pro-actively and sincerely confessing all known sins (confession of sin); Pro-actively mortifying selfish appetites, fleshly tendencies, & deeds-moment by moments. 10/3/2015 Template copyright www.brainybetty.com 2005 3 Consider the following quote: It is a most difficult and arduous achievement to renounce ourselves, and lay aside our natural disposition. For the flesh must not be thought to be destroyed unless every thing that we have of our own is abolished. But seeing that all the desires of the flesh are enmity against God (Rom. 8:7), the first step to the obedience of his law is the renouncement of our own nature…. Moreover, the very name mortification reminds us how difficult it is to forget our former nature, because we hence infer that we cannot be trained to the fear of God, and learn the first principles of piety, unless we are violently smitten with the sword of the Spirit and annihilated, as if God were declaring, that to be ranked among his sons there must be a destruction of our ordinary nature…. John Calvin and Henry Beveridge, Institutes of the Christian Religion, electronic ed. (Oak Harbor, WA: Logos Research Systems, 1996), III, iii, 8. 10/3/2015 Template copyright www.brainybetty.com 2005 4 Consider the following quote: ….Both of these we obtain by union with Christ. For if we have true fellowship in his death, our old man is crucified by his power, and the body of sin becomes dead, so that the corruption of our original nature is never again in full vigor (Rom. 6:5, 6). If we are partakers in his resurrection, we are raised up by means of it to newness of life, which conforms us to the righteousness of God. John Calvin and Henry Beveridge, Institutes of the Christian Religion, electronic ed. (Oak Harbor, WA: Logos Research Systems, 1996), III, iii, 9. 10/3/2015 Template copyright www.brainybetty.com 2005 5 What is Mortification? A. Mortification means to "put to death." As a verb, "mortify" is used 11 times in N.T. 9 instances it refers to a literal putting to death of a person. In the context of each of the nine references there is an "underlying hostility" (cf. Matthew 10:21 where it states "children will rebel against their parents and have them put to death." Other references include Matthew 26:59; 27:1; Acts 7 with the death of Stephen). The other two instances it refers to "putting to death" the misdeeds of the flesh": Romans 8:13; Colossians 3:5. 10/3/2015 Template copyright www.brainybetty.com 2005 6 What is Mortification? θανατόω (thanatoō): 1. execute, put to death; to be put to death, be killed (Mt 10:21; 26:59; 27:1; Mk 13:12; 14:55; Lk 21:16; Ro 7:4; 8:36; 2Co 6:9; 1Pe 3:18); 2. LN 68.48 stop completely, figurative extension of the above entry (Ro 8:13+) James Swanson, Dictionary of Biblical Languages With Semantic Domains : Greek (New Testament), electronic ed. (Oak Harbor: Logos Research Systems, Inc., 1997), DBLG 2506, #2. 10/3/2015 Template copyright www.brainybetty.com 2005 7 What is Mortification? θανατόω [thanatoo /than·at·o·o/]: 11 occurrences: Translates as "put to death" four times, "cause to be put to death" three times, "kill" twice, "become dead" once, and "mortify" once. 1 to put to death. 2 metaph. 2a to make to die i.e. destroy, render extinct. 2b by death to be liberated from the bond of anything, literally to be made dead in relation to (something). James Strong, The Exhaustive Concordance of the Bible : Showing Every Word of the Text of the Common English Version of the Canonical Books, and Every Occurrence of Each Word in Regular Order., electronic ed. (Ontario: Woodside Bible Fellowship., 1996), G2289. 10/3/2015 Template copyright www.brainybetty.com 2005 8 What is Mortification? 1. It pertains to the second tense of salvation, growing in conformity to Jesus Christ (progressive sanctification). 2. It focuses on the sinful appetites and fleshly tendencies; it targets habitual sins that the believer finds him/herself regularly confessing. 10/3/2015 Template copyright www.brainybetty.com 2005 9 What is Mortification? 3. Since mortification is concerned with personal and practical holiness, it deals with ones habits, one's character, and the decisions one makes. 4. A habit is a tendency to think, desire, act, and respond to something without consciously willing to do. Character has to do with the sum-total of one's habits. 5. Mortification involves moment-by-moment decision making as the believer attempts to "put to death" the misdeeds of the flesh. It is not merely confessing sin (1 John 1:9) but starving the habitual sin that haunts your footsteps. 10/3/2015 Template copyright www.brainybetty.com 2005 10 What is Mortification? 5. Consider the following two passages: "Put to death, therefore, whatever belongs to your earthly nature: sexual immorality, impurity, lust, evil desires and greed, which is idolatry." ~ Colossians 3:5: - The Greek tense of this command "Put to death" implies a decisive action, as if Paul said, "Do it now!" 10/3/2015 Template copyright www.brainybetty.com 2005 11 What is Mortification? 5. Consider the following two passages: - The list of sins Paul gives is not meant be complete. However, Paul's list highlights sins that most assuredly can take us places we never thought we would go: Immorality ("porneia" is "fornication") Impurity (a wider perversion) Lust (pathos, uncontrollable passion) Evil desires (illicit craving) Greed (coveting) which is idolatry because it "seeks satisfaction in things below and not above." 10/3/2015 Template copyright www.brainybetty.com 2005 12 What is Mortification? 5. Consider the following two passages: The Bible Knowledge Commentary observes: The Greek tense in this command suggests a decisive action, as if Paul said, "Mortify it! Do it now! Do it resolutely!" Of course, God has already done it, but Christians are to know this, count it to be true, and act accordingly (Rom. 6:5-14). In other words, they are not to go on living as though they are still alive to sin when in actuality they are not. They are to put away that old life, which springs from their earthly natures. ~ John F. Walvoord, Roy B. Zuck and Dallas Theological Seminary, The Bible Knowledge Commentary : An Exposition of the Scriptures (Wheaton, IL: Victor Books, 1983-c1985), 2:680. 10/3/2015 Template copyright www.brainybetty.com 2005 13 What is Mortification? 5. Consider Romans 8:13: "For if you live according to the sinful nature, you will die; but if by the Spirit you put to death [mortify] the misdeeds of the body, you will live." 1. "You put to death" implies it is your responsibility. 2. The verb literally is "putting to death" is in present tense. It is to be continuous in the life of the believer. 3. You must be dependent on the Holy Spirit as stated in the phrase "but if by the Spirit you put to death the misdeeds of the body [or "flesh"]." Self-discipline is insufficient. 10/3/2015 Template copyright www.brainybetty.com 2005 14 What is Mortification? B. To mortify a sin then involves the following: 1. Subdue a sinful appetite, tendency, or temptation; 2. Deprive the sin of its power; 3. To break the sinful habit that we developed we must continually refuse to give into the temptation of that particular sin. C. The goal of mortification is to weaken if not break a sinful habit or the yielding to a particular sin in your life that will one day become a habit: "Sow a thought, reap an action. Sow an action, reap a habit. Sow a habit, reap a character. So a character, reap a destiny." D. 10/3/2015 The overall means to breaking a habitual sin or fleshly tendency in your life is to be whole heartily obedient to Scripture by means of the Holy Spirit, whereby both inward desires and outward conformity harmonize together. Template copyright www.brainybetty.com 2005 15 How to Mortify these sinful habits and fleshly tendencies: 1. The overall mindset we must have in order to even begin mortifying sin is to see sin from God's perspective (regardless of its form or popularity in culture): – It is an act of rebellion against God. It is not merely something that is counter-intuitive to our new identity in Christ or something that causes uneasiness or feelings of guilty. It is an act of rebellion because you are exalting your desire above God's will. – Moreover, yielding to sin is to contribute to Satan's counterfeit kingdom. 10/3/2015 Template copyright www.brainybetty.com 2005 16 How to Mortify these sinful habits and fleshly tendencies: 2. Mortifying our sinful appetites and fleshly tendencies is saying "no" to any sinful desire we have. While the flesh may energize our sinful tendency, it is always possible, since we are freed from the enslavement to sin, to say "no" since we new creatures in Christ, indwelt with the Holy Spirit. Through the Spirit's agency we are free to say "no" to sin and "yes" to God in view of the choices we make moment-by-moment. 10/3/2015 Template copyright www.brainybetty.com 2005 17 Steps to Mortification: 1. 10/3/2015 Consider the dangerous consequences of sin both vertically (guilt; loss of peace & strength, grieve the Holy Spirit, & wound Christ) and horizontally (sin impacts others). Moreover, yielding to sin opens the door for God's chastisement, scourging, etc. When we willfully sin, as believers, we are inviting God to chastise us. Template copyright www.brainybetty.com 2005 18 Steps of Mortification: 2. Load the conscience with the guilt of sin. In other words, consider God's infinite patience & forebearance-for God has been so good to you. Why would you want to do that against Him? You are His living representative! 10/3/2015 Template copyright www.brainybetty.com 2005 19 Steps of Mortification: 3. Consider the state of one's self all times in terms of frailty, because of depravity, & vileness: the presence of sin. Even though you have been set free from the penalty of sin and are united to Christ, you are a very frail person. In other words, you are not immune to sin and sin will take you places you never thought you would go. Remember that you are a "saltine cracker" sitting on the floor of a child daycare center; one faulty & childish step--you are crushed. Though you can sincerely confess the sin, thus restoring your fellowship, you will have to live with the consequences from your sinful choices. But God is always present, available, and necessary. He is here. 10/3/2015 Template copyright www.brainybetty.com 2005 20 Steps of Mortification: 4. Trace the particular manifestation of sin to its root. It is not enough to deal with the fruit; you've got to get to the root! Seriously ask yourself: Why? Why? Why do you do you keep committing that sin sin over and over again? Get to the bottom of it! 10/3/2015 Template copyright www.brainybetty.com 2005 21 Steps of Mortification: 5. Relatedly, don't hurry to gain relief for your sin. While we need to immediately and sincerely confess our sins before God (1 John 1:9), we need to send ourselves into the bedroom and deeply consider why we do what we do. Once again, we've got to the get to the root of the problem and deal with it lest it leads to a place we never thought we would go. 10/3/2015 Template copyright www.brainybetty.com 2005 22 Steps of Mortification: 6. Act quickly against the first temptation to sin. There is a reason why the New Testament states that we are to stand firm against the devil and immediately flee from sin! 10/3/2015 Template copyright www.brainybetty.com 2005 23 Steps of Mortification: 7. Allow God to speak peace rather than assume you have it. This is very important because it is so easy to deceive ourselves into thinking that we are spiritually dynamic even though we are harboring and cultivating sinful habits and tendencies (e.g., cohabitating; premarital sex; gluttony). Sometimes we can start thinking that there are certain sins which are respectable or tolerable before God's eyes (e.g., cohabitation; premarital sex; adultery; gluttony). 10/3/2015 Template copyright www.brainybetty.com 2005 24 Steps of Mortification: Mental Weapons in the struggle against sin involve whole hearted trust, faith, & obedience; the presence of right thoughts; recognition of God's sovereignty, the effects of sin, the love & kindness of God, the wonder of calvary, & the reality of indwelling sin. ~ Dr. John Hannah 10/3/2015 Template copyright www.brainybetty.com 2005 25 Final Thought: "Tell me in the light of the Cross, isn't it a scandal that you and I live today as we do?" ~ Alan Redpath. 10/3/2015 Template copyright www.brainybetty.com 2005 26


text:
It only takes a minute to get started. Fill out our uncomplicated sign up form and login to the site.
This step is up to you but will seriously raise your chances of your profile being viewed by others. Write a little something about yourself, your interests and upload any nice photos of yourself.
Utilise our website's advanced search to discover members who match your desired criteria. Or just scan through listings and discover people that take your fancy.
Located someone who looks appealing? Send them a few words or just a wink and see if they're interested. With a little luck you'll be on your way to uncover your suitable partner.
It only takes a few minutes

subset: commoncrawl


text:
Bicienruta.com is a 1 year old domain, situated in Germany. The domain is linked to the IP address 188.8.131.52.
It was registered by Diego Gutiérrez Carrera , who can be reached at . The site is owned by Diego Gutiérrez Carrera. Registration details show that it was registered on 08 Jan 2016 through 1 and 1 and will expire on 08 Jan 2017.
The site returns a status code of 200. The site is being served through Apache.


text:
Perth, Feb 1: An all-round show by Glenn Maxwell and a devastating pace-bowling spell by Mitchell Johnson helped Australia crush England by 112 runs in a lopsided summit clash to clinch the cricket tri-series title here today.
Maxwell revived the Australian innings from a precarious 60 for 4 with a brilliant 98-ball 95 to take his side to 278 for 8 after being put into bat and then grabbed four wickets for 46 runs to annihilate England in their run chase in tandem with Johnson (3/27) at the WACA ground.
Johnson extracted the bounce offered by the lively pitch as he took three wickets in the space of 10 deliveries, including two in two balls, to rock the England run chase early on.
England never recovered from the early jolt and were all out for just 166 in 39.1 overs to lose the final match without any fight. Ravi Bopara top-scored for England with a 59-ball 33 as the visiting side crumbled under some impressive bowling by the Australians.
Opener Moeen Ali and Joe Root contributed 26 and 25 respectively at the top-order while Stuart Broad scored 24 down the order as England suffered a humiliating defeat.
Australia will now go into the upcoming World Cup at home with more confidence as they wrapped up their preparations on a high by winning the three-team tournament which also featured India.
England were sent into the backfoot soon after the start of their run chase of 279 with new-ball bowler Josh Hazlewood (2/13) drawing first blood by dismissing in-form opener Ian Bell (8) caught behind in the fourth over itself.
Johnson then turned on the heat to send James Taylor (4) back to the pavilion with Maxwell taking a low catch at backward point.
The left-arm pacer then got the dangerous Ali with a snorter as the opener was caught behind and next ball, captain Eoin Morgan went for a first-ball duck to give Johnson a chance to grab a hat-trick. But Bopara left Johnson's next delivery alone to deny the pacer a hat-trick.


text:
Intel shows future of mobile PCs
Chip giant Intel offered an insight into how emerging technology will soon change the way mobile users work, during this week's...
This Article Covers
Data centre
All-Flash: The Essential Guide
Access this guide to learn about all-flash storage, how it stacks up against hybrid and PCIe, and how to measure the benefits.
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.
The company provided details of its Mobile Internet PC 2004 vision, outlining a number of initiatives designed to improve mobile connectivity.
One such technology, dubbed "always best-connected wireless operation", is designed to provide users with wireless connections, either over a cell phone network or via the 802.11 wireless Lan standard.
The event saw a demonstration of how a user would be able to roam between a cell phone network and a wireless Lan, without losing the data connection when switching between the two types of network.
Intel also unveiled a new chipset called Manitoba, designed for future mobile phones. Based on the company's Xscale mobile processor, Intel said the new chip integrated flash memory, processor and cell phone capabilities into a single chip.
New phones based on the chip will offer support for USB and memory cards, allowing them to connect and exchange data with PCs and PDAs.
Enjoy the benefits of CW+ membership, learn more and join.


text:
by Suncast
Order now to get it by: Sunday June 04 - Tuesday June 06
Condition: New
Product ID: 626793
Delivery Information |Returns & Exchanges |Payment Methods
This stylish bench combines a comfortable place to sit with a handy place to store gardening tools, chair cushions and
more. Made of durable taupe resin with a faux wainscoting design, this storage seat works great indoors or outdoors. The
gracefully arched back provides extra support when sitting and the seat top flips open for storage. Use it year-round in
the garage, utility room, yard or anywhere you need extra seating or a place for boots, cleaning supplies, driveway
salt, scrapers and more. Made in USA.
Black / Chocolate Micro Fabric Shoe Rack Storage Organizer & Hallway Bench
ClosetMaid 1569 Cubeicals 3-Cube Storage Bench, White
SONGMICS 100% Natural Bamboo Shoe Bench 2-Tier Shoe Storage Racks Shelf Organizer ULBS04N
Espresso Shoe Cubbie Bench
Convenience Concepts Designs4Comfort Park Avenue Single Ottoman with Stool, Dark Espresso
SONGMICS 2-tier Shoe Bench Boot Organizing Rack Entryway Storage Shelf 100% Bamboo ULBS06N
Winsome Dayton Storage Hall Bench with Shelves
Simpli Home Adams Entryway Storage Bench, White


text:
Real Estate News: More Record Lows for Mortgages
Here is a look at real-estate news in today's WSJ:
More Record Lows for Mortgages: U.S. mortgage rates fell again, with the average rate on 30-year and 15-year fixed-rate mortgages furthering record lows.
LNR Property Gets $417 Million Infusion: Commercial real-estate company LNR has secured a $417 million equity infusion to help pay off a chunk of its debt.
House Talk: How To Rent Out Your Home: A reader in North Carolina seeks to rent out a vacation property and wonders how much to charge. June Fletcher has tips.
The Home Front: Michael Graves: Miami Twice: The architect's two Miami Beach condos are wildly different, reflecting recent life changes.
Private Properties: Dr. Phil Buys in Beverly Hills: Dr. Phil has bought an estate in Beverly Hills, Calif., for $29.5 million. Plus, a Forbes family ranch in Wyoming hits the market, and big properties in Kauai go up for auction.
Relative Values: Living in a Barn: These luxury "barn-style" homes in New York and Colorado have amenities like swimming pools and screening rooms.


text:
Finding solid ground for foster youth.
The typical 19-year-old makes mistakes, needs advice and could use some support from a caring adult. There are more than 300 19-year-olds who age out of the Nebraska foster care system each year, and they all need the same kind of support other teens are getting from their families.
The only difference is they had no one to go to for help—until now.
Neat Tweets
Connected Youth Initiative in Grand Island/Kearney/HastingsSarah Corey
Helping at-risk youth in North PlatteSarah Corey
Truck Brigade delivers more than just furnitureSarah Corey


text:
Combiflex 201/70
- Description
Combiflex 201/70
Convenient – straightforward – easy.
Roll-back cable lock protects against opportunistic theft. Ideal for securing luggage, motorbike helmet and much more.
Free choice of combination code, automatic cable winding 201/70.
Cable diameter: 1.6 mm, cable length: 70 cm, weight: approx. 50 g
- Shipping & delivery
We always give our best to handle your order as fast as possible. Orders which are placed before 2 pm on a work day are usually handed over to our logistics partner (DHL) on the same day if in stock.
Shipping cost
4.95 Euro within Germany, Poland
5.95 Euro in Austria, Belgium, Denmark, Luxembourg, Netherlands
9.95 Euro Spain, Finland, United Kingdom, Italy, Sweden
14.95 Euro Cyprus, Czech Republic, France, Greece, Hungary, Ireland, Portugal
19.95 Euro Estonia, Lithuania, Latvia, Malta, Slovenia, Slovakia, Romania, Bulgaria
All delivery costs are charged per order.
In our online shop, you can choose from various payment options. You can pay by invoice, direct debit, cash on delivery, credit card or finance.
- Reviews
- Product Questions (0)
Here you can ask public questions which can be answered not only by us, but also by other customers. If you have a private question, please use our contact form.
Fragen zum Produkt


text:
Newly built maisonette of 150 sq. meters at Ag. Georgios, Chalkidiki, located only 150m away from sea. The three-story maisonette consists of three bedrooms, two bathrooms, a living room with fireplace and kitchen. Garage, heating installation and warehouse area are also included.
Villa – Agios Georgios, Administration of Macedonia and Thrace, Greece
170,000 €
Total area: 150 m²
- Built in 2011
- 3-floor building
- Total rooms: 3


text:
Download Converting AS400 Spool Files to Excel
CONTENTS Overview Issue (1): Removing column headings and blank lines Issue (2): Formatting columns correctly into Excel Issue (3): Converting negative numbers Issue (4): Data mining – conditional selection of rows Issue (5): Denormalising level breaks into Excel Issue (6): Handling spool files that are too big for Excel Issue (7): Saving spool file run parameters into Excel Issue (8): Adding graphing and special formatting Issue (9): Automated conversion to Excel Issue (10): Real time distribution of spool files into Excel The Case Study: Nike's Excel automation project Conclusion Converting iSeries spool files to Excel - Top 10 issues resolved! OVERVIEW If you visit an iSeries/AS400 site and speak to the business users and ask them how they ultimately report on the information stored on their machine, 8 out of 10 of them will say via an Excel spreadsheet. Have you ever seen a green bar report cut and pasted into a board report? Have you ever seen a sales meeting where a green bar report is put on the overhead projector or into a data show? The tool of choice is Excel. Say what you like about Bill Gates, but 65 million Excel users can't all be wrong! Regular users of Excel know just how powerful a tool it can be to analyse numeric data and to present information in a clear and concise fashion. The problem if you are an iSeries/AS400 site is that your data warehouse is not immediately accessible to your Excel spreadsheets. More often than not your main financial reports are generated into iSeries/AS400 spool files, and frequently from 3rd party application software that simply cannot be modified in house. The result can be a data export nightmare. Exporting large and/or complex reports that originate from iSeries/AS400 spool files into Excel spreadsheets can create numerous challenges for business users and IT staff alike, but with the right tools and a little "know how" life can be simple again! This whitepaper identifies the top 10 issues facing users that need to export iSeries/AS400 spool file data into Excel spreadsheets and how to resolve them. The emphasis is on seamless automation – click and display solutions. Did you know? "There are an estimated 65 million users of Microsoft Excel worldwide and the number is still increasing" The top 10 issues relating to exporting iSeries spool files into Excel are: (1) Removing column headings and blank lines (2) Correctly formatting columns into Excel (3) Converting negative numbers (4) Data mining – conditional selection of spool file rows (5) De-normalising level breaks against rows to include a key or index for the row (6) Handling spool files that are too big for Excel (7) Saving spool file run parameters into Excel (8) Adding graphing and special formatting (9) Re-using and sharing your Excel format definitions (10) Automated real-time distribution of spool files into Excel via email or intranet Read on and wake up from your data exporting nightmare! www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! Quotes from users (…Overheard near your printer) "I've lost my report" Issue (1) Removing column headings and blank lines The single biggest hassle of exporting an iSeries/AS400 spool file into an Excel spreadsheet is inheriting the cosmetic "window dressing" along with the data you really need. When data is imported into an Excel spreadsheet, it is usually because you need to manipulate the information in some way; whether it is to perform formulas on columns or to include graphs, or to make some other change to the way that the information is presented. When spool files are printed in hardcopy you need to have certain page and column headings, as well as blank lines that act as visual "breaks" to make the report easier to read. Any page from the spool file may be separated from the rest of a report and still needs to make sense. Page numbers may also be required to keep the various hardcopy report pages in sequence. "Who took my report off the printer?" "I need this in Excel - not on paper" "Why does it take so long to re-run reports?" Figure 1: Example of a hardcopy report "What do you mean you can't reprint my report?" These important visual "tricks" and formatting might be necessary to convey the printed report information in the most easily understandable fashion to the reader, but exported into Excel they may serve only to render the spreadsheet practically unusable. At least, not without some significant manual editing. If the report in Figure 1 above was imported directly into Excel using Excel's built in Text Import Wizard, it would look something like this: "Why do we waste so much paper?" "I can't read this page because the toner ran out" "Is the printer jammed again?" Figure 2: Example of hardcopy report imported into Excel Before this spreadsheet can be used for anything meaningful, the next step would be to conduct a manual "clean up" of the data. A user would be required to page through the spreadsheet, row by row, deleting out the lines containing page headers, totals, blank lines, level breaks and so on. This is a slow laborious process, and most people have better things to do with their time. There has to be a better way – the good news is that there is! Did you know? "Of a 1000 regular Excel users surveyed, 55% never use 9 out of 10 of the most powerful features" © Copyright Asymex Pty Ltd Page 2 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! Key points Solution (1) Removing column headings and blank lines The removal of unwanted column headings, blank lines, and sub-total lines from reports when exported to Excel can be easily automated with the use of a 3rd party document management solution. There are essentially two main ways of removing the rows that are not required; Omission Rules OR Selection Rules. The example 3rd party software described in this whitepaper is SpooliT®, a document management solution from Asymex. SpooliT® uses a method known as "Templates" to define rules about how an iSeries or AS400 report should be imported into Excel. However, the concepts described may well apply to other 3rd party software solutions singularly or in combination. Omission Rules: These rules are defined to tell the software to automatically drop report lines from being imported into Excel when they contain certain text strings. This is the simplest way of ensuring unwanted lines do not find their way into the Excel spreadsheet. Figure 3: SpooliT® template omission rule In the example above (Figure 3) any row containing the text string "Total Qty:" starting in column 61 of the spool file will be automatically dropped and not imported in to the resulting Excel spreadsheet. This is an effective way of removing rows such as Total lines that may look very similar in structure and position to the detail lines that may be required. In the example above the user simply "drags and drops" to highlight a unique text string in the row that they wish to omit, they are then prompted to create a row omission rule for their Template. Interestingly the SpooliT®software also allows for the creation of an explicit selection rule, which behaves in the exact opposite to the omission rule, which may be required to select rows that are being otherwise dropped (this feature would allow for the concept of "Omit all rows containing this text string except where they contain this other value"). Another omission rule may allow certain types of lines to be automatically dropped, for example blank lines. In the case of SpooliT®, elimination of blank lines is as simple as specifying a parameter value on the template used to convert the spool file to Excel. © Copyright Asymex Pty Ltd Page 3 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! Figure 4: SpooliT® template properties In the example above (Figure 4), simply selecting "Ignore blank lines in Spool File" will cause blanks lines in the originating spool file to be dropped as they are imported into Excel. Selection Rules: Using a 3rd party software you may be able to define rules which map specific lines from the reports you want imported into Excel, with the software being "intelligent" enough to recognize lines based on the definitions you have created. Figure 5: SpooliT® template column definitions In the example above (Figure 5) the columns of data that are wanted in Excel have been defined, and the SpooliT® software is smart enough to drop lines that don't match the ones required. For example, the template rule has defined that an Order Quantity value starts in column 70; SpooliT® will then automatically drop any report lines that do not have a numeric value or blanks in this specified column position. This will cause column headings and page headings to be dropped from being imported into Excel. With the correct row omission and selection information specified, only the desired rows are imported into Excel. This eliminates the time consuming process of manually reading through your spreadsheet each time you import a spool file into it and deleting the lines that are not required. © Copyright Asymex Pty Ltd Page 4 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! The resulting spreadsheet should look something like the example below: Key points Figure 6: Spreadsheet with unwanted rows dropped With any good spool file management software you should be able to obtain results like this with a few minutes of initial setup and then simply drag and drop or click a button to convert your spool files to excel. Problem (1) solved! © Copyright Asymex Pty Ltd Page 5 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! Key points Issue (2) Formatting columns correctly into Excel When traditional methods are used to "dump" spool files into Excel there are a number of inherent problems caused by the restrictive and rigid nature of the process itself. There are many way of delivering this information; the most common way is to transfer the report "as is" via client access as a text file and then use the built in function of Excel, the "Text Import Wizard" to manually format the data (Excel -> Data -> Get External Data -> Import Text File). The second most common method involves programming effort to dump the report into a CSV (comma separated) file then again using the built in Excel feature to import a CSV file. Whilst both of these options will physically get the report information into Excel, it is not normally in a state that can be used immediately. So what are the problems with these traditional methods? 1. The Excel "Text Import Wizard" format cannot be saved and your specifications must be re-entered each time you import data (once you start your next Excel session) 2. You can not automatically "drop" columns of data, you get everything that was contained in the originating data 3. You cannot change the order of the columns of data imported, they must initially be imported in the same order as the originating data source 4. The Excel cell types are defaulted to "General", so any text fields that contain numbers will be treated as numeric and right aligned We all have better things to do with our time than make format edits to an Excel spreadsheet. Wouldn't it be great if you could save a definition of how you wanted the report data to look and it would be "remembered" each time your imported similar report data into Excel? - read on, your problems may be solved! The Excel "Text Import Wizard" definition cannot be saved Traditionally you cannot "drop" columns of data Traditionally Column Order cannot be changed at loading Traditionally Excel cell types cannot be dynamically set Use spool file management software to define re-usable Excel import rules Use spool file management software to drop unwanted columns Use spool file management software to automatically resequence columns at load Use spool file management software to default Excel cell types by column Did you know? "In 1981 US business consumed an estimated 850 billion pages of paper. By 1986, amid claims that the paperless office had arrived, the total had risen to 2.5 trillion pages.... In 1990, business was using 4 trillion pages" Solution (2) The solution to this problem is to again use a 3rd party spool file management software that allows you to specify rules or a saved "definition" of how to import data for a particular report type into Excel. The software that will be used to illustrate the solution to the above problems is SpooliT® but the needs that are met by this software are universal. 1. You need to be able to save your Excel import "definitions": SpooliT® allows you define a set of rules to map data from a particular spool file type into Excel, and then save these rules as an Excel "Template". These "Templates" may be saved externally to the SpooliT® application as ".tpl" files (a proprietary format) which can then be shared amongst other users of the software. © Copyright Asymex Pty Ltd Page 6 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! Figure 7: Working with templates within SpooliT® 2. A good spool file management solution will also allow you to define rules about which columns you want to import into Excel. You should be able to define different "views" over a single report, depending on what elements of the report you wish to focus on. This will essentially allow you to pick and choose which information from the report you want - if you do not need all the data in Excel why import it? Removing clutter allows the report message to be more clearly expressed. Within the SpooliT® application this is achieved by simply selecting which columns are required with the Template definition. Once the template is defined it can be used again and again each time a new source spool file is created, "remembering" the rules about how the report should look when imported into Excel. Figure 8: Selecting the "Cust No" column in SpooliT® Template In the example above the user has "dragged and dropped" to highlight the customer number column "Cust No." to indicate that this column is required in to be imported into the Excel spreadsheet. 3. Once you have defined the rules about which columns of data you want imported into Excel, changing the sort sequence or order in which they are imported into Excel should be trivial. A good spool file management solution should either allow you to select which columns within Excel the source data should be imported into (i.e. explicitly choosing columns A or B or C etc) OR should allow you to change the sequence relative to the other columns that you have selected. In SpooliT®, this is achieved using the latter option. That is, you can re-order columns within your template rules before the data is imported into Excel. The columns of data would then be automatically inserted into Excel in the sequence that you require. Page 7 of 33 © Copyright Asymex Pty Ltd www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! In the example below the sort sequence for the "Item Description" field has been changed to "45" to move it after the "Order Quantity" field within the template definition. This in effect moves the "Item Description" from Column C to Column D when it is loaded into the spreadsheet (swapping places with the Order Quantity field). Figure 9: Changing the column order in a SpooliT® template 4. It is important to be able to choose the field type of the destinations column within Excel. This allows for text fields to be aligned correctly, and for "special" fields like date fields to be imported automatically into an immediately usable format without further manipulation. Again a good spool file management solution should allow you to define and save the destination field types for your Excel spreadsheet. In the case of SpooliT®, each column defined with the Excel Template can have its field type set to any valid Excel type. No further editing required! Figure 10: Setting the Excel field types in SpooliT® templates Problem (2) solved! © Copyright Asymex Pty Ltd Page 8 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! Key points AS400/iSeries negative numbers normally have their negative symbol formatted on the right hand side Issue (3) Converting negative numbers One of the more challenging aspects of importing reports created on the AS400/iSeries concerns negative numbers. In AS400 and iSeries reports, negative numbers normally have the negative symbol formatted on to the right hand side of the field. E.g. $2,000.05- or $2,000.05CR In Microsoft Excel negative numbers are formatted with the negative symbol on the left hand side of the field. E.g. -$ 2000.05 As a result when a negative number is converted from an iSeries or AS400 report using traditional means the resulting field is treated as a text field in Excel. This means that your normal numeric functions such as Sum will ignore this value. Excel requires the negative symbol on the left hand side Figure 11: Negative number in Excel without formatting If a sum were applied to these values then Excel would give the result as 2267.70 (ignoring rows 5 and 6 above) whereas the correct answer would be –5706.80. Normally, the only way to correct this problem is for the user to read through the excel spreadsheet after importing the AS400 or iSeries report and then manually convert each field to a negative numeric. Not only is this a time consuming process but also it is also prone to "human error" which can compromise the integrity of the data in the spreadsheet. The solution is to use 3rd party document management software designed to convert negative numbers for you without any unnecessary user intervention. Did you know? "The average American office worker is estimated to use a sheet of paper every 12 minutes. That's a ream per person every two and a half working weeks. That's 100-200 pounds of paper each year" Solution (3) Any good document management software that allows conversion of AS400 or iSeries reports to Excel should automatically convert negative numbers formatted with the negative symbol to the right of the last decimal place to a "true" negative number. In the case of SpooliT®, formatting negative numbers is as simple as defining the field width within the Excel Template function to include one or two characters to the right of the last decimal place in the numeric field. © Copyright Asymex Pty Ltd Figure 12: Selecting a negative column in Page 9 of 33 SpooliT® www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! When this field above is imported into Excel the resulting spreadsheet for this column should look like the following example: Figure 13: Negative numbers correctly formatted into Excel Not only has this saved the effort and cost of having to manually edit the resulting spreadsheet, but now the possibility of user error has also been eliminated. Problem (3) solved! © Copyright Asymex Pty Ltd Page 10 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! Key points Issue (4) Data mining – conditional selection of rows We import data from iSeries/AS400 reports into Excel generally because we either want to do further analysis on the information (e.g. applying formulas etc), or to present the information in a more visually pleasing manner (e.g. graphing or formatting etc). Reports created on the iSeries/AS400 are often very large in content, and often try to be everything for everyone. Again this is often caused by the business reality of the architecture – iSeries/AS400 are often used to store large amount of data and the batch windows and CPU usage often dictates how many reports can be produced. Downloading large reports into Excel can be counter-productive. The Excel spreadsheets become unwieldy to use and formulas slow down. Downloading an entire month's sales Figures into Excel when you only need a week is overkill. One solution is the use iSeries/AS400 query to refine your data selection – however most occasional users of the iSeries/AS400 may not have the in-depth knowledge to do this. Another problem is that many reports may be built up from data from multiples files (again a reasonably complex option with query to define for the average user). In addition, some reports can only be created at certain times of the day, due to time or CPU limitations, or the availability of certain component data. Purchasing a dedicated EIS or data mining system and using a data warehouse may be another solution, and there are indeed many occasions when this is necessary. However, the downside is that such systems are notoriously expensive and complex to set up, and normally require significant user training. Again this may be overkill for users that simply want to filter report data by a limited set of data. There is another option that can give you additional flexibility – let your spool file management software do it for you! Downloading large reports into Excel can be counterproductive Limited iSeries/AS400 resources to produce "customized" reports Not all users are trained to create iSeries/AS400 queries Dedicated EIS/Data mining solutions are expensive and complex Give control back to your users "win/win" for business and IT Create multiple template "views" over reports Stop programming reports – use query and templates instead Did you know? "To store 2 million paper documents, an organization may spend between $20,000$40,000 on filing cabinets alone. Those files could fit on approximately 10 CD-ROMs" Solution (4) The logical place to apply conditional selection of data into Excel is at the time the information is extracted from the source spool files. Most good spool file management solutions that allow you to export data from spool files will allow you to do this. In SpooliT®, the example software that we are using in this whitepaper, this can be controlled at the time columns are selected within Templates. Different templates can be defined over the same spool file, allowing for different "views" over the same data with different data mining attributes. For instance, one template may select only invoice lines greater than a certain value; another may select invoices for a particular customer or product. By using a simple graphical interface, end users can define their own "views" over reports without the need for iSeries/AS400 Query training or even access to the iSeries/AS400, let alone training on a dedicated MIS/EIS system. The same product that delivers their information formatted into Excel can also be used to conditionally select the data they need. In addition this eliminates the need to run additional reports on your iSeries/AS400 – the "Super report" that does everything for everyone can finally be used for the purpose it was intended. This is a "win/win" solution for both business and IT users alike. The business finally has the control back to deliver the information they need into Excel in a timely fashion – they can change their minds and try different combinations without the time and cost of raising IT work requests. From an IT point of view this means less pressure to support business requests to extract different combinations of data and less pressure on the iSeries/AS400 to run more and more report combinations. IT can focus their energies on areas that deliver other benefits to your organisation. © Copyright Asymex Pty Ltd Page 11 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! The example screen shot below (Figure 14) shows how data mining attributes can be easily applied to a column in a SpooliT® Excel Template. The values in the green box allow for data mining attributes to be applied. In this case only report lines where the Order Quantity is greater than 1000 will be imported into Excel. Standard Boolean operators can be defined, including "Like" selecting values based on partial defined contents. Figure 14: Data mining definition is SpooliT® This solution leads on to a further "left field" concept: If the ultimate destination of a report is into Excel, why spend the time creating a programmed report on the iSeries/AS400 that presents the information in a "pretty" fashion. The IT area can simply "dump" a physical file with query into a query out file which can then be imported with SpooliT® using an Excel Template. The template will "data mine" the required information into Excel and format it in whatever way the user requires. This will translate into a lead-time for creating a new report measured in minutes rather than days or weeks using the traditional software development cycle - think outside the square! Problem (4) solved! © Copyright Asymex Pty Ltd Page 12 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! Key points A "normalized" report is essentially a report that doesn't display redundant information A "de-normalized" spreadsheet includes key or index information against each row in the spreadsheet Traditional data import methods into Excel will not result in a denormalized output Use spool file management software to map key or index values to appropriate columns against each row Templates will allow the denormalization rules to be "remembered" each time the report in exported into Excel Issue (5) De-normalising level breaks into Excel One of the major problems of importing data from iSeries/AS400 reports into Excel is that most reports have been "normalized" to display the information in a visually appealing way BUT to use Excel efficiently with meaningful formulas we sometimes need to have data denormalized. So what is "normalization" and "de-normalization"? A "normalized" report is essentially a report that does not display redundant information. For example, a sales report might be sorted by sales rep and show all sales for a particular time period for each sales rep, starting each sales rep on a new page. Figure 15: Example of a normalized report In the example above (Figure 15), we can tell that the various detail lines relate to the sales rep "R00001 – Alex Martin", without the need to see this sales rep's details appearing against each of the report lines. This sub-heading is also known as a "level break". However, if this report was imported into Excel we would normally need to see the sales rep code appearing against each row in the spreadsheet, essentially acting as a key or an index to that row. Figure 16: Example of a de-normalized spreadsheet Using traditional methods of importing spool files into Excel will not give this result without considerable manual intervention or additional programming. However, with the right software this can be achieved automatically without any additional work every time you want to download a report into Excel. Did you know? "Consider the cost of buying paper at $2 per ream. Add the cost of producing; printing, copying, postage, consumables etc and the cost can increase by up to 10 times the original cost - or up to $20" © Copyright Asymex Pty Ltd Page 13 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! Solution (5) Using a good spool file management solution you will be able to define level breaks or formats within a report so that the software can recognize components of a report and then know exactly where they should be mapped into your Excel spreadsheet. This will normally involve "teaching" the software how to recognize a report line and where each part of that line should be mapped. In the case of SpooliT® this involves defining formats within each Excel Template that match to each of the different line types you wish to import into Excel. Figure 17: Defining a format in SpooliT® In the example above (Figure 17), the Sales Rep line has been broken down into 3 components. SpooliT® is able to "recognize" a sales rep line by the fact that it contains a constant text of "ACME Sales Rep" starting in column 1. Figure 18: Defining a constant field in SpooliT® By choosing a field type of "Skip Column" in the Define Field Attributes screen (Figure 18) the constant is not mapped into the resulting Excel spreadsheet, and is only used to help SpooliT® recognize a particular report line as being a Sales Rep format line. © Copyright Asymex Pty Ltd Page 14 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! The other fields within the format may then be mapped to the appropriate cells with the Excel spreadsheet. The Sales Rep Code can be separated away from the Sales Rep Name and placed it its own column, in this case Column A (Figure 19). Figure 19: Sales Rep field The Sales Rep Name can also be mapped to appear in column B (Figure 20). Figure 20: Sales Rep Name field In most cases when defining a format it is not necessary to have to define a constant type field. The mere fact of describing a particular field as a numeric value in a certain column position will automatically eliminate report lines that do not contain numbers in that constant position. By defining the Sales Rep format in this way we have now effectively de-normalized the Sales Rep code and name into columns A and B respectively against each row of the spreadsheet. Another format would now be created over the Detail Lines of the report, mapping the first detail field against column C. Figure 21: Defining the next format in SpooliT® Each field in the Detail Line format may now be mapped to the appropriate column in Excel and the Template saved. Each time the report in exported to Excel using this template, the report will be automatically de-normalized. Problem (5) solved! © Copyright Asymex Pty Ltd Page 15 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! Key points The maximum size of an Excel spreadsheet is 65,536 rows by 256 columns A report of more than 1,310 pages would be at risk of exceeding the Excel row limits "Chunking" is the process of splitting a large report into more manageable blocks or "chunks" Once chunking is enabled then reports greater than the specified size will be automatically split into multiple Excel spreadsheets Issue (6) Handling spool files that are too big for Excel The maximum size of an Excel spreadsheet is 65,536 rows by 256 columns that may seem like an incredible size, but when you are importing iSeries/AS400 reports it can be surprisingly easy to exceed. So how large of a report could we typically import to Excel before we hit an issue? Let's assume that the maximum number of lines per page is 60, and we take up 10 lines for page headings, spacing and column headings, leaving us 50 data lines per page. On that basis a report of more than 1,310 pages would be at risk of exceeding the Excel row limits. Any sort of transaction-based report on a large database over a reasonable period of time would likely exceed that number of pages. So if you still need to import your large report information into Excel for further data modelling what options do you have open? Did you know? "A recent survey found that about 60% of Excel users don't ever use PivotTables" Solution (6) There is only one solution to this problem – "chunking". Chunking is the process of splitting a large report into more manageable blocks or "chunks", with each separately imported into Excel. Each chunk can be included as a separate worksheet within a single Excel spreadsheet or as entirely separate Excel spreadsheets, depending on the specific needs of the users. The first way of "chunking" a report involves a programming solution to break a report into multiple text files based on pre-determined parameters, and each one can then in turn be imported separately into Excel. This solution would involve some time and effort, and definitely some IT involvement. The second way involves using a spool file management solution that "chunks" a report automatically for you. For example, SpooliT® allows a chunking option to be dynamically switched on when ever the user wishes to engage chunking: Figure 22: Switching "chunking" on in SpooliT® Once the user enables chunking, then reports greater than the specified size will be split into multiple Excel spreadsheets as appropriate. The user will be prompted by SpooliT® to choose which page range they want to load into Excel. This avoids the issue of the row limitation in Excel, but also opens up other use and possibilities with the solution. © Copyright Asymex Pty Ltd Page 16 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! In the following example (Figure 23) the chunking rule was set to "Split into 1-page chunks when a file is greater than 1 pages"; this effectively allows individual pages from any report to be loaded separately into Excel. Figure 23: Selecting a page to load to Excel in SpooliT® The use of chunking in SpooliT® can be extended to importing Spool Files into other formats, chunking is not limited to just Excel. Problem (6) solved! © Copyright Asymex Pty Ltd Page 17 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! Key points Excel data becomes disassociated with the original run parameters Report lines can be highlighted from the originating spool file to be loaded into Excel every time the report is exported Issue (7) Saving spool file run parameters into Excel When a report is produced on the iSeries/AS400 there will invariably be certain information stored around the run parameters that generated the information. This might be as simple as including the date and time the report was generated, or as complex as including a series of parameters or values that the report was run against. When the resulting report is viewed, it is always considered in context against the backdrop of these parameters. Thoughts such as "What date was this report run for?" or "What values was this report run against?" will also help us interpret the information more accurately and help us come to the appropriate conclusions as a result. When traditional methods of importing information from these reports are used, the data becomes disassociated with the original run parameters. If the resulting Excel spreadsheet is stored away from the originating report, then it rapidly becomes impossible to determine what or when the originating data was constructed from. You may elect to manually add heading rows to include this information, but again, this is a time consuming nuisance each time you have to import data - and open to user error when transcribing the information. Did you know? "The number of pages printed in American offices is increasing by up to 20% each year" Solution (7) Again, the solution is to use a 3rd party spool file management solution which allows title or header lines from a spool file to be loaded once into the first few rows of the Excel spreadsheet. In the example below, SpooliT® allows report lines highlighted from the originating spool file to be loaded into Excel every time the report is exported using a SpooliT® template. Figure 24: Selecting a report title in SpooliT® In this case the first 3 lines from the spool file will be loaded into rows 1 to 3 of the resulting Excel spreadsheet. Figure 25: Report export to Excel with title Problem (7) solved! © Copyright Asymex Pty Ltd Page 18 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! Key points Not easy to preserve all of the formatting information each time you load data into a spreadsheet Go from a multiple page green bar report to a graphical summary report in Excel updated in real-time automatically as soon as the iSeries/AS400 report is created by ANYONE on your iSeries/AS400 A spool file management solution that will monitor your iSeries/AS400 and export a formatted Excel data spreadsheet in real time to a known location with a known name A master spreadsheet that uses standard Excel spreadsheet commands to extract and format the data as required. Issue (8) Adding graphing and special formatting One of the most powerful features of Excel is the ability to graph and format information quickly and easily, allowing for the effective communication of important report information. Combined with the ability to create formulas and calculations over report information, Excel is an extremely powerful reporting tool. If you have a regular report in Excel that requires you to download information from an iSeries/AS400 spool file, you will encounter the same problems again and again. It is not easy to preserve all of the formatting information, graphs etc from your last report the next time you need to load the data from a spool file. Typically this would at the very least involve a "cut and paste" operation from an interim spreadsheet to a pre-formatted spreadsheet, along with revisiting of formulas and graph conditions etc. This can be a time consuming process. Wouldn't it be great if every time someone ran the source report in question on the iSeries/AS400 your master spreadsheet was automatically updated in real time, without ANY user intervention? Sound like an impossible dream; nothing is ever that simple, right? – Wrong! It can be done… You CAN go from… Figure 26: Green bar report To… Figure 27: Master spreadsheet updated automatically from green bar report You can go from a multiple page green bar report to a graphical summary report created in Excel updated in real-time automatically the moment that the iSeries/AS400 spool file is generated by ANYONE. How? It is a lot easier than you think! © Copyright Asymex Pty Ltd Page 19 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! Solution (8) Your spool file management solution monitors for a specific report type to be created on your iSeries/AS400 and then automatically exports it to a share drive location formatted via an Excel Template A 2nd "master" spreadsheet that simply loads the data from the dynamically generated spreadsheet The solution to presenting your reports graphically in Excel involves two important components. 1. A spool file management solution that will monitor your iSeries/AS400 and export a formatted Excel data spreadsheet in real time to a known location with a known name. 2. A master spreadsheet that uses standard Excel spreadsheet commands to extract and format the data as required. The concept is a simple one, your spool file management solution monitors for a specific report type to be created on your iSeries/AS400 and then automatically exports it to a share drive location formatted via an Excel Template. This will of course overwrite any other excel spreadsheet in the same location, losing any formatting already prepared in that spreadsheet, hence the need to have a 2nd "master" spreadsheet that simply loads the data from the dynamically generated spreadsheet. In the previous example (Figure 27) the SpooliT® software was used to create an Excel Template (similar to the ones describes in Solutions (1) to (7)) that formats our "Month to date sales register" into a resulting "data" spreadsheet that looks like the following: A SpooliT® job is created to monitor for our ACME4R2 report automatically in real-time, and as soon as it is appears on an Outq, it is exported using our template to a specific share drive folder and given a specific user-defined name. Figure 28: Export job definition screen 1 in SpooliT® The screen above (Figure 28) shows the initial set up of the real-time job in SpooliT® that will monitor for our Month To Date report on the iSeries/AS400. The intention in this white paper is not to describe this side of the solution in great detail, but rather demonstrate what can be done with a good spool file management solution. Did you know? "An Electric and Gas company reported to save $239k in reduced paper consumption by centralizing files, reducing distribution and consolidating forms" © Copyright Asymex Pty Ltd Page 20 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! The next screen (Figure 29) in the real-time job definition allows the selection of a destination for the export spool file. In this case the path "C:\Asymex\White Papers" has been chosen as the target destination, although this could just have easily been an Intranet/Internet/FTP location or a share drive location. Figure 29: Export job definition screen 2 in SpooliT® The next step is to ensure the report is exported using the Excel Template that was previously defined and also to confirm the naming standard used for the exported file. The file-naming standard being used will result in an exported spreadsheet called "Data Spreadsheet.ACME4R2.xls". The option to "Replace previously exported files with the same name?" has also been selected to ensure that any existing data spreadsheet is overwritten whenever anyone creates a new ACME4R2 on the iSeries computer. Figure 30: Export job definition screen 3 in SpooliT® The first part of our automation is complete, now the master spreadsheet can be set up. © Copyright Asymex Pty Ltd Page 21 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! The Master spreadsheet being created will summarise the example Month To Date sales report into a one page executive summary with graph and a few extra formula cells. This is not intended to be an overly complex example, but really just illustrate concepts that a user can then build upon to create more elaborate summary reports. Firstly we need to understand what the Month To Date Sales report is actually reporting on. Simply put it shows total sales by customer and item with a quantity and dollar value, sorted by the Sales Rep that they relate to and throwing to a new page on change of Sales Rep. The summary report will show total sales for each sales rep, the number of sales each sales rep made, their average sales value, and then graphing the total sales by sales rep so that we can clearly see who the best performing sales rep is. Step 1: First things first, since our report is being updated in real-time let's give our report a dynamically updated title. The formula used was: ="My Month To Date Sales Report as at " & DAY(TODAY()) & "/" & MONTH(TODAY()) & "/" & YEAR(TODAY()) If you wish to be even more elaborate you could populate the title with the actual report run date and time from rows 1 and 2 of the data spreadsheet using the SUBSTR option. Step 2: The source report ACME4R2 may have a variable number of sales reps each time it is run. There is no guarantee that sales reps won't be added or removed, and we do not want to keep editing the master spreadsheet each time the report is run! So we need to dynamically load the sales rep names into Column A using a simple Excel Query. The option used is under the "Data" menu option in Excel, then "Get External Data" then "New Database Query". Next choose a data source of "Excel Files" and select "ok", at the next window navigate to your data spreadsheet and select it. Make sure that you do check on "System Tables" under table options: Select the column that contains the Sales Rep Name (using the preview button can help), then you will have the opportunity to apply filter options. In this case filtered on two values: • The columns value to "Is Not Null" to eliminate any blank names that might come from any excel report headings in the first few rows • The columns value to not equal to "Sales Rep Name" – the column heading already loaded into the data spreadsheet. Then select the option to sort by this column ascending. Finally rather than selecting the "Return Data to Microsoft Excel" select the "View data or edit query in Microsoft Query" option. This will allow one more very important attribute of the query to be set. © Copyright Asymex Pty Ltd Page 22 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! Setting "Unique Values Only" will ensure that each Sales Rep Names will appear just once in the Master spreadsheet. Now select "Return Data to Excel" from the "File" menu and you will then be prompted as to which cell to start loading the data into. The resulting query string that you have created will look like the following: SELECT DISTINCT `ACME4R2#txt$`.F2 FROM `C:\Asymex\white papers\Data Spreadsheet.ACME4R2`.`ACME4R2#txt$` `ACME4R2#txt$` WHERE (`ACME4R2#txt$`.F2 Is Not Null And `ACME4R2#txt$`.F2'Sales Rep Name') ORDER BY `ACME4R2#txt$`.F2 The next thing to do is right click on your tool bar in Excel and ensure that you have the "External Data" tool bar check on. Then select the first cell in your query and select the properties option from your "External Data" toolbar – the following window will be displayed. Check on the option to "Refresh data on file option" and depending on how frequently your source data changes then the "Refresh Every" option as well. Check of the "Include Field Names" and you can then give the column a more meaningful name rather than being limited to displaying the system column name. Check on the "Fill down formulas in columns adjacent to data" – this is crucial for the next step. Step 3: Now that we have our Sales Rep Names loading nicely into our master spreadsheet from our data spreadsheet, we can extract some summary information for each sales rep. The first column, Total Sales uses a SUMIF command: =SUMIF('[Data Spreadsheet.ACME4R2.xls]ACME4X2.txt'!$B:$B,A3,'[Data Spreadsheet.ACME4R2.xls]ACME4X2.txt'!$F:$F) © Copyright Asymex Pty Ltd Page 23 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! The next column, # of Sales, uses a COUNTIF command: =COUNTIF('[Data Spreadsheet.ACME4R2.xls]ACME4X2.txt'!$B:$B,A3) and finally the last column, Av. $ Sales is even simpler: =B3/C3 By selecting the "Fill down formulas in columns adjacent to data" this will ensure that our formulas get populated against each Sales Rep name, regardless of how many are added or subtracted from our source report. Step 4: Now the fun part, graphing our data. In this case the graph will simply show the top 15 sale reps. This is using standard Excel graphing functionality – and will be dynamically updated as our source data changes. In summary, the three steps to add graphing and special formatting using SpooliT®are: • • • Create the data spreadsheet via an Excel Template (which is reusable) Automate the Outq monitoring, then Create a master spreadsheet linked to the data spreadsheet Now all that remains is to display your spreadsheet and watch as it dynamically changes and re-graphs before your eyes in real-time the moment someone generates your source report on the iSeries/AS400 – SpooliT® magic! Problem (8) solved! © Copyright Asymex Pty Ltd Page 24 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! Key points Need 1: Re-use your formatting the next time you want to import this report into Excel Need 2: Share your formatting definitions with other users SpooliT® allows you to create an "Excel Template" which allows you to store your format definitions for re-use and sharing Issue (9) Re-using and sharing your Excel format definitions When you have created an elaborate format to map your source report from your iSeries/AS400 computer to Excel that does everything you could possibly want, there are two important things that you would now understandably like to do next: 1. Re-use your formatting the next time you want to import this report into Excel 2. Share your formatting definitions with other users This can be a problem with traditional methods of importing data to Excel. If you use the Excel "Text Import Wizard" you cannot save your definition, if you use certain 3rd party spool file management solutions you are able to save your definitions but you cannot share them. What is the answer? Did you know? "A telecommunications company reportedly save 25+ tons of paper by expanding the use of electronic purchase orders and invoices" Solution (9) As you might expect, the solution to this issue is to use a spool file management solution that allows you to 1) define your Excel export formats, 2) save them and 3) share them with other users. In the examples referred to in this whitepaper the spool file management software that is used is SpooliT®. As previously described, SpooliT®allows you to create an "Excel Template" which allows you to store your format definitions for re-use and sharing. Figure 31: working with templates in SpooliT® Templates can now be exported and imported as ".tpl" files ( proprietary format to SpooliT®). If you wish to share a template with another user you can export it to a .tpl file and the either email it or save it on a share drive folder. Libraries of templates can be created on share drive folders for users to import them as required. Imported Templates can easily be modified or "tweaked" for other views over the data without have to build a template from scratch. Problem (9) solved! © Copyright Asymex Pty Ltd Page 25 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! Key points If you are going to present information in Excel then you need to have it seamlessly integrated Ideally users should just open an email and see the spreadsheet delivered to them, or have a short cut set up on the desktop that displays the spreadsheet from a URL link Define tasks or jobs to monitor for certain types of spool files, then convert them to Excel using our template "rules", then either email a copy to the appropriate distribution list Define tasks or jobs to monitor for certain types of spool files, then write the Excel spreadsheet to a intranet, internet or FTP location and then just send the appropriate recipients URL links Issue (10) Automated real-time distribution of spool files into Excel via email or intranet Defining how you would like to format data from an iSeries/AS400 spool file into Excel is only half the battle; if you have to rely on another person to run the report or export it for you then you are almost back at square one. If you are creating a summary report for an executive, you can not expect them to have the time (or necessarily even the technical skills) to know where to look on your iSeries/AS400 to find the source report and then export it into Excel. If you are going to present information in Excel then you need to have it seamlessly integrated – click on one button and up pops the excel spreadsheet. Issue (8) covered a solution to this requirement that allowed for the resulting spreadsheet to be updated in real time. But what if you wanted to send that Excel spreadsheet to a number of recipients? Again you need to have the same level of simplicity – ideally the user should just open an email and see the spreadsheet delivered to them, or have a short cut set up on the desktop that displays the spreadsheet from a URL link. This level of automation would have been an impossible dream a few years ago, but now it is remarkably easy to set up with today's next generation of spool file management software. Did you know? "A major IT outsourcing company reported saving of $230k by putting large documents on the internet/intranet instead of printing" Solution (10) A good spool file management solution should be able to deliver on our requirement above with ease. Users should be able to define tasks or jobs to monitor for certain types of spool files, then convert them to Excel using SpooliT® template "rules", then either email a copy to the appropriate distribution list OR write the Excel spreadsheet to a intranet, internet or FTP location and then just send the appropriate recipients URL links. This is the ultimate "thin client" solution for distributing formatted iSeires/AS400 reports. The example software used to demonstrate this functionality is again SpooliT®, this time the SpooliT® Server and SpooliT® Scheduler automation software. Step 1: Firstly a few simple rules need to be defined to identify which report or reports we wish to convert to Excel – known as Categorization Rules within SpooliT®. The automation software will use these rules later as it monitors the iSeries/AS400. The Categorization Rules are divided into two types: o Categorization Rules by Attribute: Which identify the report/s by one or more combinations of the 8 spool file attributes; e.g. File name = "ACME4R2" and Outq must start with "Dayend" o Categorization Rules by Name: Which identify the report/s by content; e.g. the report must contain the text string "Month To Date Sales Register" The Categorization Rules can be as general or as specific needed to identify the required reports. However an Excel Template are usually very context specific for a particular type of report, the categorization rules would also need to be as equally specific. It is unlikely that an Excel Template created for one report would work over an entirely different type of report. © Copyright Asymex Pty Ltd Page 26 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! The screen shot below (Figure 32) shows the definition of a Categorization Rule by Attribute for our Month To Date Sales Register Figure 32: Setting up Categorization Rules in SpooliT® Step 2: Now that we have defined rules that allow SpooliT® to recognize our spool file/s on the iSeries/AS400, we need to set up a list of recipients for this report. This way SpooliT®can automatically email either a copy of the report converted to Excel OR a URL link to the Excel Spreadsheet that SpooliT® has automatically created in the specified Intranet/Internet/FTP location. Figure 33: Setting up report recipients in SpooliT® These recipients are not limited to just receiving the converted report in Excel, they can also chose from combinations of 5 other report types AND each recipient can have their own preference set up for how they would like their report delivered to them. © Copyright Asymex Pty Ltd Page 27 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! Step 3: We can now define our task or "Scheduled job" within SpooliT® to monitor for the reports that we are interested in. These jobs run off the iSeries/AS400 on a pc or application server, which means no performance impact on the iSeries/AS400. They can be set up to monitor in real-time, or at a predetermined intervals ranging from minutes up to a year. As with the solution described in Issue (8), we define an "Export To" job: Select the option to "Export iSeries report(s) by Categorization Rules" choosing the rule we set up in Step 1. It is worth noting that within this single job it is possible to also initiate the report to be created in the first place, by using the "iSeries Job" tab. We can now select the "Export To" tab to chose the destination for the converted report, either a folder location (any location mapped to the pc) or an FTP location. Figure 34: Defining a FTP location in SpooliT® The flexibility of using an FTP location means that you can export spool files automatically to a file server sitting behind a firewall in any format that you require. This enables a "thin client" solution for electronic delivery of iSeries/AS400 reports – that is your recipients only need security access to your web site and a web browser to be able to view spool files (converted into any format). They do not need any other software loaded on to their workstations nor do they need to have access to your iSeries/AS400 machine itself. You may set up short cuts to specific FTP locations to display a user's reports if you are delivering a known report to them on a regular basis, otherwise if the report delivery can be variable then you may prefer to automatically send them an email with a URL link to the reports in question. © Copyright Asymex Pty Ltd Page 28 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! The recipient list is inherited from the names defined in Step 2, but may be overridden and modified for each individual job. Figure 35: Setting up recipients to an export job in SpooliT® Step 4: The job may now be submitted to run under SpooliT® Scheduler. The job that was defined above will now monitor for the "Month To Date Sales Register" report in real-time. As soon as that report is created on the iSeries/AS400 by ANY user, the SpooliT® job will: • • • • Detect the spool file/s Convert the spool file/s to Excel using the template rules specified Export the spool file/s via FTP to the www.asymex.com/Spool files location Email our 4 recipients an email with a URL link to the converted report/s. A true "set and forget" solution with no further user intervention required! Problem (10) solved! © Copyright Asymex Pty Ltd Page 29 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! Key points Nike Australia implemented the SpooliT® Enterprise solution Many reports are automatically produced on a nightly, weekly monthly and yearly basis from automated batch processing All reports were currently printed The I.T. operations department with staff of two (at a cost of US$30K each per annum) are required to change paper, burst and collate reports, answer printer messages and produce ondemand reports THE CASE STUDY: Nike's Excel Automation Project Nike Australia implemented the SpooliT® Enterprise solution to automate the way certain AS400 reports were converted to Excel and how all of their reports were distributed to users. Business Model Nike distributes and sells a variety of sporting goods and apparel throughout Australia. Nike has a number of their own retail outlets, as well as supplying a large number of 3rd party retailers throughout the country. They have offices in a number of states. Computer Systems and Infrastructure Nike operates an iSeries computer system for most of their business systems, running some modules of the JDE software and a variety of in-house developed applications. Interstate offices are connected via a Windows NT network, with a variety of client PC operating systems including Windows NT, Windows 2000, Windows 98 and Windows XP. Communications are via TCP/IP over the network, using Microsoft Outlook for all e-Mail. Did you know? "A state bureaucracy reduced their print margins to .75 and reported an annual saving of up to $123k in printing consumables" Existing Business Operation Nike iSeries computer system services Ordering, Billing, Retail, Warehousing, and Sales Analysis. Other in-house systems include Marketing and Sales Reporting. Nike runs a 'Dayend' each weekday on the iSeries, together with 'Monthend' batch runs. Many reports are produced and printed on a variety of iSeries attached printers. Current Situation A detailed analysis highlighted that the iSeries computer was the source of a majority of the reports produced by the company. Many reports are automatically produced on a nightly and monthly basis from automated batch processing. In addition a significant number of reports are produced daily on-demand. All reports were printed. Several reports regularly exceed 1000 pages in size, whilst other smaller reports are printed with multiple copies (i.e. 20 copies sent to each sales representative). The I.T. operations department with staff of one (at a cost of US$30K per annum) are required to change paper, burst and collate reports, answer printer messages and produce ondemand reports. During the 'Month-end' batch runs the operator is required to work overtime in order to produce, burst, collate and distribute reports by start of business on Monday. The additional overtime cost for the operator was calculated at US$10K per annum. The monthly budget for the purchase of paper, consumables (such as printer ribbons and toner cartridges), printer replacement and maintenance contracts exceed US$10K per annum. The monthly budget for the purchase of paper, consumables (such as printer ribbons and toner cartridges), printer replacement and maintenance contracts exceed US$10K per annum. © Copyright Asymex Pty Ltd Page 30 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! Key points Nike made a commitment to substantially reduce use of consumables and implement a 'Waste-Busters Plan', promoting a 'Greener' and a more environmentally conscious corporate culture Temporary administrative assistants were occasionally employed at an annual cost of US$3K to assist during peak times. The envelope and postage costs amount to US$1000 per annum. Hard copy of reports are stored for audit and legal requirements indefinitely in secure off-site storage. The cost of storage is increasing annually at US$1000 as additional reports continue to be produced. Business Requirement After an audit of internal operations, Nike made a commitment to substantially reduce use of consumables and implement a 'Waste-Busters Plan', promoting a 'Greener' and a more environmentally conscious corporate culture. The linchpin to this plan revolved around the reduction of the use of paper and associated consumables by effective utilization of technology. A commitment was also made to reduce the number of physical reports produced for external parties such as suppliers and customers; e-mail, where possible, is to become the standard method of distribution. A decision was also made to reduce the operations department overtime and the use of temporary staff. Nike required a more efficient and timely method to distribute reports to head office staff, branch staff and Nike retail outlets. The need to be able to electronically backup or archive reports was an important consideration. It was mandatory that a solution be found to ensure that data was no longer re-entered from printed reports into Excel or other PC applications. The cost of such double handling was considered both expensive and inefficient. A decision was also made to reduce the operations department overtime and the use of temporary staff. Did you know? "Nearly 3.7 million tons of copy paper are used annually in the United States alone. That's over 700 trillion sheets" How did SpooliT help? SpooliT®Server was purchased and installed with the following goals: SpooliT® reduces the need to print hardcopy As reports are archived from the iSeries computer to SpooliT® they are automatically e-mailed or faxed to the Supplier or Customer with NO operator or staff intervention required Printer consumables reduction: SpooliT® reduced the need to print hardcopy. With SpooliT® Viewer (which has an unlimited site licence included FREE with every purchase of SpooliT® Enterprise, Server, Explorer or Browser) enabled all the Nike users may view archived reports on-line without the need for an iSeries connection or profile. Reports are imported into common windows applications Local Network printers were used to print reports, allowing iSeries printers to be decommissioned over time Operator over-time reduction: Jobs may be created within SpooliT® Server to monitor certain iSeries Jobqs and automatically archive their contents to SpooliT® . These jobs can be set to run after Dayend, Weekend, Monthend and Yearend. Categorization and distribution rules are also set up within SpooliT to "file" each report in an appropriate Category and then automatically e-mail it to the required users. Individual users with "special" requirements may even automatically receive multiple electronic copies of each report in a number of different formats (for example, as a Word document AND as an Excel spreadsheet). No programming was required and only minimal operator DIY ("do it yourself") training was required with SpooliT® using their comprehensive tutorial and help system. Temporary staff and postage cost reduction: Suppliers and Customers are set up within SpooliT® as Users, and e-mail addresses or fax numbers are entered for each of them. They are then set-up on distribution rules for the relevant Archive's Categories. As reports are archived from the iSeries computer to SpooliT they are automatically e-mailed or faxed to the Supplier or Customer with NO operator or staff intervention required. Nike even customized the format that the Supplier or Customer prefers to receive their reports in – with no effort required! iSeries Printer Cost reduction: Local Network printers were used to print reports, allowing iSeries printers to be decommissioned over time. © Copyright Asymex Pty Ltd Page 31 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! Key points Significant net savings obtained in first year Document storage cost reduction: Reports are archived to SpooliT and then cut to CD. The CDs are then duplicated and stored off-site in accordance with the existing backup policy. Saving in re-entry and manipulation of data: Reports can easily be exported into a variety of PC based application such as Excel, CSV files or Word, PDF or HTML. The saving in man-hours was extraordinary. Users could create templates to extract the required data direct from a complex report and reformat the data into Excel for further analysis with the click of button. Templates are reusable and automated jobs could be created to repeat tasks. The bottom line for Nike Saving - Printer and Consumable budget halved Saving - Operator Overtime Saving - Temporary Staff Saving - Postage and envelopes Saving - Document Storage Saving – Re-entry and manipulation of data Total Saving in first year US$5,000 US$10,000 US$3,000 US$1,000 US$1,000 US$20,000 US$40,000 Faster report distribution, easier and cheaper retrieval of archived reports "SpooliT® enabled us to take a big step towards becoming a true paperless workplace." Did you know? "According to the Minnesota Office of Environmental Assistance, the average office worker uses 10,000 sheets of copy paper each year" Intangible benefits include: Faster report distribution, easier and cheaper retrieval of archived reports, scalability, workflow benefits, and ability to support on-going diversified business requirements at virtually no cost. Testimonial: "SpooliT enabled us to take a big step towards becoming a true paperless workplace. That's good for us and good for the environment. We give them the big tick" - Nike Australia © Copyright Asymex Pty Ltd Page 32 of 33 www.SpooliT.net Converting iSeries spool files to Excel – Top 10 issues resolved! Key points Exporting spool files to Excel can be easy Re-usable templates can format the data the way you want into Excel Link Master spreadsheet to automatically generated data spreadsheets Automate your conversion to Excel by using 3rd party spool file management solutions Automate distribution to save money, time and be more efficient Conclusion Exporting data into Excel from an iSeries/AS400 computer doesn't have to be a difficult or onerous task. With the right tools and a little bit of "know how" it can be a fast, efficient and rewarding process. Excel is a tremendous tool for not only analysing data but also for presenting information in a clear and concise fashion – so use it more! You should not be constrained by the location of your data warehouse – if the information you need resides on your iSeries/AS400 you can still format it into Excel in a seamless and automated fashion. The issues and solutions described in this whitepaper are intended to spark your imagination and to get you thinking about how you can apply these concepts to your business. The examples described are just the tip of the iceberg – the next step is to take these ideas and apply them to your environment and start introducing real efficiencies. Start EXCELerating your iSeries/AS400 today! Author: Alex B. Martin BSc (Computer Science) For more information on the SpooliT please visit our web site at www.SpooliT.net © Copyright Asymex Pty Ltd Page 33 of 33


text:
|Message view
|« Date » · « Thread »
|« Date » · « Thread »
|Dave Cottlehuber <...@jsonified.com>
|Re: music database
|Wed, 22 May 2013 17:49:41 GMT
On 22 May 2013 15:46, Robin Qu <firstname.lastname@example.com> wrote: > And I am just curious about the outcome of accumulating a large amount of DB files in TB size, full of music attachments. Think about data migrations, load balance of music file streaming, and some other complicated logics concerning further processing of music files…... > > Couch will be a nice choice if you are playing around. In real production world? Think again. Or use bigcouch which will have no problems doing this. Or store your media elsewhere and use couchdb for metadata. At the moment there's not really enough information for us to give you solid advice on the options! A+ Dave
|View raw message


text:
Mumbai, Feb 7 (PTI) In its last major decisions under C BBhave''s leadership, Sebi today tightened noose around erringmarket players, promoters and investors, while streamliningthe public offer bidding process and grant of licences.
But a decision was elusive at a Sebi board meeting onrevising rules for company takeovers as also for newregulations for the way stock exchanges are owned and didbusiness.
The regulator decided to bring in a greater oversight onbrokers, mutual funds, bankers and other market entities bylimiting their initial licence to five years and said it wouldbe renewed into a permanent one based on their track record.
The move would also save the market intermediaries thepain of seeking renewals every year.
The decisions were taken at the Sebi board meeting underchairmanship of Bhave, whose term ends on February 17.
He would be succeeded by U K Sinha, head of fund house UTI AMCand chairman of mutual fund industry body Amfi.
Sebi also decided to suggest to the government to put inplace checks to avoid incidents like Satyam scandal beingrepeated.
In this regard, Sebi said that it would suggest theMinistry of Corporate Affairs to put in place a clause in thenew Companies Bill to bar any investors, including promoters,from voting on any matters where they have an interest.
In Satyam case, the board members, including the nomineesof promoters, had decided to acquire two companies -- MaytasInfra and Maytas Properties -- promoted by the family membersof Raju family, the promoters of the IT firm.
Taking a cue from Satyam case, it was decided thatsuggestion would be made to MCA that for passing resolutionswhere one of the investor has conflict of interest, theinterested investor should not be allowed to vote, Bhave said.
In another decision, Sebi made it less expensive forinvestors to buy shares in public offers and also rightsissues, where shares are sold to existing shareholders.
Sebi decided to make it mandatory for non-retail investorsto bid through ASBA -- a facility under which the money getsdebited from investors'' account only after share allotment.
The ASBA (Application Supported by Blocked Amounts)facility shall be mandatory for non-retail investors makingapplications in public/rights issues with effect from May 1,2011," Bhave said.
The move to grant licences initially for five years issaid to have been aimed to keep brokers, mutual funds,bankers, portfolio managers, depository participants and othermarket intermediaries under check for any possiblewrongdoings. .


text:
Open for business: that was the punch line in Prime Minister-elect
Australia grows when foreign investment grows. Since 2007, the Australian economy has grown by 14 per cent and now ranks as the world's 12th largest, up three places. In the same period, the total stock of foreign investment has risen by 30 per cent to reach twice the level it was in 2003. Unsurprisingly, mining investment was up two-thirds since 2007. But foreign retail investment rose by the same proportion and even the supposed laggard of manufacturing saw foreign investment grow by a third in the last 5½ years.
In mid-November the Foreign Investment Review Board will make a recommendation on US agri-giant Archer Daniels Midland's $3 billion bid for
Qantas chairman
Farming has, if anything, been neglected in the foreign investment wave, with only 1 per cent of foreign investment proposals in 2010-11 involving agriculture. Ninety-six per cent of Australian farmland is still locally controlled. But no one would think so from the horror headlines and general angst that foreign investors in the farm seem to bring.
Rural investment throws up some strange arguments. A recent Senate inquiry into foreign investment and the national interest recommended that approvals of farming land deals include examination of the investor's motives, "food security" being a red flag. By this logic, a motivated end-user buyer becomes a threat to Australian farms. At the same time as the inquiry was reaching this conclusion, Australia's then trade minister was in front of Asian audiences spruiking the same "food security" benefits as a reason for investing in a safe rural haven like Australia. The mixed messages are extraordinary.
High-value Australian food processors like dairy exporters Murray Goulburn say that the rural land controversy is just a distraction. What they, and others like major beef exporter AACo, want is the access to foreign markets provided by more free trade agreements which will allow them to earn high margins on Australian food sales into Asia.
Caught somewhere in between is ADM and the GrainCorp deal, which could plug Australia's most competitive grain producers into world markets if it doesn't run aground in the shallows of knee-jerk protectionism from the Nationals.
It is true that an ADM deal would put much of Australia's grain handling under foreign control, and GrainCorp is a near monopoly infrastructure provider in some areas. But Australia's beef processors are heavily foreign owned, without hurting the efficiency of the beef industry. GrainCorp under ADM's ownership would still have plenty of competitors as a buyer. And with the hefty price tag ADM is paying, it has every incentive to fill its infrastructure facilities with its own traders' grain and those of all the other buyers – so keeping up grain prices for growers. Moreover, the US company has the ability to keep investing through drought periods in Australia.
The Nationals need to think carefully about the message sent around the world if they try to block a transparent deal between a listed US company and a listed Australian company.
That would definitely not be open for business, and for the worst of reasons.
The Australian Financial Review


text:
× View Today's Deals
You missed it This great deal for Hang Gliding Tahoe is over. But don't despair, we've got more great deals every day.
Related Deals That You May Like
Hang Gliding Tahoe
One-Hour Interactive Hang Gliding Lesson
- Save 50%
- 1 purchased
deal over
deal over!
100% recommendedin LivingSocial customer ratings
There's no need to skip town for an out-of-this-world experience. Earn your wings with a private hang gliding lesson from expert Paul Hamilton:
• $199 ($400 value) for a one-hour interactive hang gliding lesson with video
• The FAA-approved aircraft features a modern design for extra thrills
• Take in the splendor of Lake Tahoe from a totally new vantage point
"It was an awesome and unique experience. Being able to see Lake Tahoe from a hang glider was amazing and beautiful." - LivingSocial Member
PAID VALUE EXPIRES ON May 22, 2022
PROMOTIONAL VALUE EXPIRES ON May 24, 2014


text:
Need an LH Test in Middleburg Heights, OH? Simply order online 24/7 and get a fast, affordable LH Test at a lab near you (Find More Locations):
What is an LH Test?
The LH Test, also known as a Luteinizing Hormone test, is a lab test that measures the LH level in the body. The LH Test is used to help guide fertility decisions for people in Middleburg Heights, OH.
How much does an LH Test cost?
Our LH Test price includes the doctor's order that you need to get tested in Middleburg Heights, OH, all lab fees and taxes, and a pdf copy of your results. For current prices, CLICK HERE.
What does an LH Test report look like?
For an example of an LH Test sample report, please CLICK HERE.
We are delighted to offer the LH Test in Middleburg Heights, OH.
LH Test in other cities near Middleburg Heights, OH:Berea | Broadview Heights | Brookpark | Brunswick | Cleveland | Fairview Park | Independence | Lakewood | North Olmsted | North Royalton | Olmsted Falls | Parma | Rocky River | Strongsville | Westlake