Amazon References

Monday, December 12, 2016

Convert incoming XML to Excel Sheet


Currently most of our clients got here with a requirement to create an excel file as an output of an xml. state of affairs was Proxy-SAP PI 7.31 (Java Stack)-Third celebration – output as Excel Sheet. First PoC of this requirement was to create a goal file by simply giving output.xls and see what fields populated. As anticipated output get populated as xml.
To populate goal file as Excel file we need to do transformation somewhere between the XML got and target file generation. we have 2 options –



  • Transformation at the time of Mapping
  • Write Adapter Module in Java

  • on the time of mapping we are able to achieve excel output via XSLT mapping. For this answer we deserve to convert incoming XML file to MS Excel XML structure.
    For the conversion of XML to MS Excel XML format we need to use under code in xslt.
    <?mso-application progid="Excel.Sheet"?><xsl:stylesheet version="1.0"xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:xsl="http://www.w3.org/1999/XSL/radically change" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:workplace" xmlns:x="urn:schemas-microsoft-com:workplace:excel" xmlns:ss="urn:schemas-microsoft-com:workplace:spreadsheet">
    explanation of this scenario can be found below.
    Convert incoming XML to Excel or Excel XML – part 1 – XSLT means
    second option is to write down an adapter module to transform incoming XML to CSV and then to Excel Sheet.
    Pre-Requisite and Assumptions of the building:



  • 1. Apache POI open average for excel Conversion
  • 2. SAP PI specific development libraries

  •   com.sap.aii.af.cpa.svc.api.jar
      com.sap.aii.af.lib.mod.jar
      com.sap.aii.af.ms.ifc_api.jar
      com.sap.aii.af.svc_api.jar
    complete clarification of this state of affairs can be discovered below.
    LEARN MORE



    Links and related books

    Excel 2007 VBA Macro Programming

    Microsoft Excel VBA Programming for the Absolute Beginner

    Learn Advance Statistics Training in Excel

    Friday, December 9, 2016

    Primary Excel Parser


    A simple excel parser that parses every row to an associative array.
    When to use this?



  • Your excel file has the header on the first row, and records on the leisure rows.
  • You want to map the header titles to database keys.
  • You wish to map the information values to database enums.
  • example
    You need to parse an excel file with this format.
    identify Age Gender mobilephone 1 cell 2 invoice 18 Male 222-222-2222 777-777-7777 Peter 25 Male 222-333-4444 555-666-7777 Monica 31 female 666-666-6666 555-555-5555
    and you wish to map the header titles to database desk fields, and map genders to 'm' or 'f'.
    $parser = new Jhesyong\Excel\Parser; // Map 'identify' to 'name', 'Age' to 'age', and 'Gender' to 'gender'. $parser->addHeader('identify', 'name'); $parser->addHeader('Age', 'age'); // Map the values: 'm' is for 'Male', and 'f' is for 'female' $parser->addHeader('Gender', 'gender')->withOptions(['m' => 'Male', 'f' => 'Female']); // you can use a daily expression and move a callback to map numerous header titles. // in this case, 'cell 1' may be mapped to 'phone_1' $parser->addHeader('/^cell \d+$/', characteristic($title) return str_replace(' ', '_', strtolower($title)); ); // Load the file and start to parse. flow a callback feature // and it will be called for each row. $parser->loadFile('filename.xlsx')->parse(characteristic($information) // information could be an associative array. var_dump($statistics); ); FAQ I actually have distinctive titles of the equal textual content, but I are looking to map them to distinct database table fields.
    you can add headers with the same title however distinctive box names within the desired order.
    // the first 'phone' should be mapped to 'phone_1', // and the 2d 'cell' can be mapped to 'phone_2' $parser->addHeader('phone', 'phone_1'); $parser->addHeader('phone', 'phone_2'); I just want to use the title as the container name
    that you would be able to omit the 2nd argument.
    // 'cellphone' can be mapped to 'mobile' $parser->addHeader('phone'); I need to pre-define the header mapping and the alternate options.
    that you would be able to prolong the Parser and write your rules in the constructor.


    Links and related books

    Thursday, December 8, 2016

    Excel to Html

    Description

    Finally a good program that will help you to import data in the web. Stop to write the data in the tables html, you just have to change the data inside the file Excel and this application read your excel and create the table html responsive with sorting..

    Table Responsive


    \\

    Contact Form


    \\

    Google Maps


    \\

    Features

    • 100% Responsive
    • Table Sorting
    • Pagination Table
    • Search for column
    • Ajax Contact Form
    • Single Table with 2 design
    • Multi Table
    • Google Maps
    • Browser Compatibility
    • Google fonts included

    Monday, December 5, 2016

    Generating Personalized Letters Using CSV Data From Google Spreadsheets

    Google Spreadsheets are great for collaborative number crunching. Much like Microsoft Excel, Google Spreadsheets allow you to produce financial models, analyze numerical data, keep track of accounts, etc. But what if you want to produce and deliver documents of a different nature, like letters to various recipients, based on the data contained in a Google Spreadsheet? Instead of going through each row of the spreadsheet one at a time and plugging data into a document template manually, you can use WebMerge to pull data from the spreadsheet and automatically fill in a template document, perhaps, to be delivered in PDF form to certain recipients!

    "How can I do this?" you might ask. Don't worry! We’ll walk through the process in the tutorial below.

    First, we’ll need to have a document ready for merging on WebMerge. In this example, let’s say we want to send students a report card based on data we have in a Google Spreadsheet. In the spreadsheet we have the students’ names, their email addresses, and their GPAs.

    We first go to WebMerge and upload or create a new document in the Document Builder. The document we are about to upload or create is the document that will be automatically populated with the data from our Google Spreadsheet. Our merge document might look something like the following. The following is an example of a cover letter with merge-fields for the students’ names and the students’ GPAs. The merge fields will get populated with the data in our spreadsheet.

    Image of Document Builder for Report Card Cover Letter

    After we have our document template on WebMerge, we want to: make sure our configuration settings are how we want them, test our document, and set up delivery.  Notice that in the delivery setup, we specify an <<other>> merge field. We didn’t create a merge field for the students’ email addresses, but we want to use the email address column in our spreadsheet to specify our email recipients.

    Specify the Merge Field for Emailing

    The above shows that we want to send the document to recipients according to a merge field that wasn’t in our document and has as yet to be specified. We want to send emails to the addresses in the email address column of our spreadsheet, so we specify that we want to create a new merge field called Email_Address and use it as a placeholder for the email addresses in our spreadsheet. In the next step we will match this placeholder as well as the placeholders in our document with the data we get from a CSV version of our spreadsheet.

    Now that we have made our document template and have saved our delivery method, we need to pull the information from our Google Spreadsheet. The easiest way to do this is to simply download the spreadsheet as a CSV file.

    Image of Google Spreadsheets Download as CSV

    Once we have downloaded our data as a CSV file, we can upload that to WebMerge.

    Image of WebMerge Upload Data as CSV

    Upload the data, then in our final two steps we map the merge fields to the columns of our spreadsheet and send our newly created documents off to our recipients.

    Image of WebMerge Map CSV Data to Document Merge Fields

    Click Merge the Data!

    Image of WebMerge Merge the Data Button

    Congrats! You just merged your Google Spreadsheet data with a document template and sent it to all the relevant recipients.

    Image of End Result of WebMerge and Google Spreadsheets Combination

    Want to do more with WebMerge? Check out all the ways you can streamline your documentation process today!

    Suggested Posts: 

    Take Your Mail Merge Online

    New: Merge Excel Spreadsheets on the Fly

    10 signals Your advertising team Have Outgrown Excel Planning



    Friday, December 2, 2016

    Take Your Mail Merge Online

    Mail Merge in MS Word has long been one of the most popular and widely used features of the office tool, but how often do you send letters anymore?  Today, it’s much more common to generate Word or PDF documents using Mail Merge and send them via email.  What if you needed to do more?  For example, what if you wanted to send those documents out for e-signature?  Or save them in the cloud?
     
    WebMerge has got you covered!  In addition to using MS Word document as templates, you can upload a fillable PDF or even build your own document using our online editor.  From that point you can setup your delivery method, which could be via email, an e-signature platform, the cloud, or to another 3rd party via a webhook.
     
    Once you have the document and delivery options setup, the last piece of the puzzle is uploading the mail merge information.  You can do this using a CSV file (which stands for comma separated values) that you can save directly from Excel.  After you upload the information, we’ll merge your documents and send them out!
     
    Imagine you were at a tradeshow and you collected information from 100 potential customers. You collected their name, email address, and which products they were interested in purchasing.  You saved all of this information in a spreadsheet.  Now that the trade show has ended, you’d like to follow up with each of these prospects and send them a proposal that includes information about each of the products they were interested in as well as the pricing.  All you need to do is create a new WebMerge document for the proposal that includes the prospect’s information and uses Conditional Sections to include the products they were interested in.
     
    Once you build the document, simply upload your spreadsheet and we’ll send your customized proposal to each of the prospects.  You could do all of this in as little as 30 minutes.  Think of how long it would take you to manually create all of these proposals!  If you need to mail the proposals, check out our integration with Lob
     
    Can you think of any other areas of your business that you could automate by using an online mail merge?


    Thursday, December 1, 2016

    New: Merge Excel Spreadsheets on the Fly

    Do you spend countless hours day after day, month after month, putting together the same spreadsheets for reporting, projections, proposals, and more - with only a little bit of the information changing?  We can help!  We’re excited to announce that we have just launched support for Microsoft Excel spreadsheets!  You can easily generate spreadsheets that use calculations, charts, and pictures.  You can even export them to a PDF.

    Below we’re going to show you how to setup a quick spreadsheet that is a proposal for customer acquisition that would be sent to a prospective client.  To start, we’re going to put in a little information about the customer, then we will enter some basic calculation parameters (current customers, growth rate, etc) and then our spreadsheet will calculate growth over the next year and even provide us with a nice chart at the bottom.


    Once we’ve got our spreadsheet all setup, we going to do a quick test and make sure everything is working correctly.  We’ll just enter some basic information that is easy to double check the calculations.


    As you can see, we’ve exported the spreadsheet to a PDF so that it is clean and professional looking when we send it off to our prospective client.  As you can see, the values in our calculations automatically calculated and the graph was updated accordingly.

    Doesn’t get much easier than that, does it?  We’re looking forward to seeing how you can use WebMerge to automate your excel spreadsheets.  What are a few ways that you can take advantage of this new feature?


    Tuesday, November 29, 2016

    10 signals Your advertising team Have Outgrown Excel Planning

    Excel is a lovely business tool. it is excellent for all sorts of functions: building a marketing plan, monetary modelling, income forecasting, reporting etc.
    The fact that Excel spreadsheet may be high-quality tool for planning a small enterprise, there comes a time for many successful corporations when they have got actually outgrown it. You comprehend you’ve outgrown Excel when…

  • Your coronary heart sinks two months before funds time comes round and you discover lots of pressing tasks that deserve to be accomplished first.
  • You seize your self whispering the “please open appropriately” prayer when loading your spreadsheet.
  • You delivery to agenda meal times and coffee breaks round when the spreadsheet must recalculate (or with no trouble to open).
  • You spend longer attempting to remind yourself of the file structure than truly brooding about next yr’s business.
  • You comprehend that you're heading off making critical adjustments to the company because the spreadsheet doesn’t assist them and the grownup who at the start developed it has left.
  • You find you have got mysteriously misplaced half of your sales forecast when a person by chance overwrites a crucial system with a number candy desires are not made from these


  • Value Error in Excel Planning Document

    You start to dream of spreadsheets stuffed fully with cells exhibiting #value! and go into work to locate here is genuine, because someone has ‘tidied up’ a essential linked spreadsheet or saved it with a unique edition of Excel.
  • Half manner during the financial yr you understand that finance and advertising and marketing were working off distinct models of the price range all 12 months.
  • You vow that you're going to in no way go through this hell once more and in fact will invest in a proper planning device once the budget is complete, however locate you don’t dare as a result of ‘all and sundry is aware of it’ and ‘it will be too intricate to exchange it’ and so…
  • Your heart sinks when funds time comes round once again…marketing’s Planning Burden

  • in lots of groups, in particular in direct advertising, a lot of this burden falls on the advertising department as they supply the numbers which aid the entire others.
    unfortunately, if senior administration fail to draw close the nettle promptly, the spreadsheets become swiftly more complex and the fear of changing the method for an increasingly gigantic enterprise ends up in a paralysis of concern. It needn't be so. moving to a ‘correct’ planning equipment does not must be daunting and even specifically costly.
    When moving from an easy spreadsheet-based planning method, you really want to do little greater than replicate the existing performance, while addressing essentially the most severe user complaints and the weaknesses inherent to a file-primarily based device like Excel. In specific, that skill gazing a small variety of principles:

  • everything hangs off the advertising and marketing plan. In a advertising-led business, the advertising and marketing plan is on the root of all activity. whether the economic projections are taken at once from the numbers provided through advertising, or the advertising and marketing plan is developed to aid the excessive level economic objectives (or, extra commonly, a bit of of each), it's essential that a strong advertising/sales plan is constructed from which the different numbers required by the company (financials, product demand, customer provider requirements and so forth.) will also be derived. marketing/earnings should ‘personal’ or at least purchase in to the numbers within the plan if the business is to have a hope of reaching the price range and alterations to the advertising plan may still lead directly to changes within the different numbers.
  • A single edition of the certainty. in its place of the possibility of multiple file models, scattered around the company, have a single primary database which will also be seen with the aid of everyone. This turns into the business budget so that all and sundry is working to the equal dreams. even though some capabilities (Finance, Product supply) want/need to add buffers or margins for contingency, they should still delivery from the equal advertising plan because the leisure of the company.
  • Embedded logic. as a substitute of formulae being determined in spreadsheet cells where they're prone to being deleted or overtyped unless protected, the business good judgment may still be safely developed into the equipment where it cannot be overwritten.
  • constant calculations and terminology. All departments and business devices use the identical equipment, in order that they all have to use the identical terminology and calculations. problem all requests via distinctive constituents of the enterprise to have their personal points – is their company basically so diverse from the relaxation of the enterprise? should or not it's? evidently distinctive markets require distinct advertising and marketing, but don't underestimate the price of retaining company procedures simple.
  • Immediacy of outcomes. as a result of all constituents of the company are using the same gadget, as quickly as changes are committed by using one part, they're instantly attainable to the relaxation of the enterprise. here is especially critical as soon as the planning technique is over and the genuine outcomes delivery flowing in. Any updates to revenue forecasts should still propagate to different clients of the information as quickly as viable.
  • Versioning. Excel isn't brilliant at comparing one version of a marketing strategy to a different – specifically if the materials have modified. A properly designed database should still handle this and not using a difficulty – enabling evaluation between diverse drafts of the price range and between actuals and plan. This also implies that ideally the same device that was used for planning may still be used to file on actuals to allow these comparisons.

  • So, if you adventure that common sinking feeling every time annual planning time comes around, you should act now. Don’t are trying to over-engineer, don’t think you need to have the latest off-the-shelf product from SAP or whoever, and don’t let your IT branch inform you it will can charge hundreds of thousands and take years to implement. especially, don’t extend: in case your company is turning out to be, it'll most effective get tougher the longer you depart it!

    Alternative Planning Platforms

    Zoho Creator.- Streamline your business processes and collaborate with your team

    Monday, November 28, 2016

    Month-to-month funds Excel Calculator

    Budgeting, balancing, banking and funds are all very traumatic things that all and sundry has to contend with. no matter if you're making $25,000 or $one hundred,000 a 12 months, having a month-to-month finances can aid you manage your money appropriately. The difficulty isn't many americans comprehend the place to beginning. An excel template has rows and columns that can aid prepare your monthly finances. one could readily get returned heading in the right direction with a month-to-month price range calculator Excel template.
    monthly funds Calculator Excel: money Coming In
    you can beginning off by means of making an Excel sheet dedicated to the funds coming in during the month. each and every kind of profits can have its own row. as an instance, Sarah’s teaching job salary may also be in one row and her outdoor tutoring earnings can have a different. along the precise of this sheet may also be columns for dates. That approach, you could see what day Sarah got paid for her instructing job with the aid of searching at the columns and rows.
    Monthly Budget Calculator Excel Template
    no longer most effective will you understand if you acquired paid right through the month, however that you can set up an Excel feature so as to add the all revenue. this is very useful as a result of after you are taking out the entire deductions for expenses, meals and different necessities that you could see how an awful lot you've got left over for discounts and social activities.
    monthly funds Calculator Excel: funds for necessities
    Make a further web page for funds you spend on needs. which you can divide the document into sections: domestic, mobile, tv and internet, and many others. inside the home area, you can make a title column for labels like personal loan, heat, water, and garbage decide upon-up. In each and every of the rows which you could write how tons you paid for that particular expense.
    continue this method with every of the other sections. After each area is completed, that you can add a feature to make each and every part’s subtotal and an ordinary total. This lets you see how much you are deciding to buy each and every area and overall. this may assist you see where you are spending too much and where which you can reduce returned.
    Having your whole costs and revenue in a single document is a great way to keep on with a finances. once you deploy the doc, all you ought to do is add totals right through the month. A monthly budget calculator can aid you see where your funds is going and keep your budget in order. download our free template on your month-to-month finances.
    linked Templates: Your free template will download in 5 seconds.

    Friday, November 25, 2016

    How to Make BCG Matrix in Excel

    BCG matrix is a strategic choice making approach that helps in aid allocation amongst diverse cost facilities or charge objects with the aid of categorizing or rating them based on their capability to generate cash inflows towards money consumption or money outflows.
    BCG matrix helps inserting products in four distinctive quadrants of graph in keeping with their relative market share and market growth. Such presentation aid take prioritizing and identifying what product to be given how a good deal consideration and elements.
    right here is the BCG matrix developed correct in Excel:
    bcg2
    Its truly easy to make. All you want is an appropriate data and how to plot the bubble chart with few tweaks if necessary. Following are step-with the aid of-step to get the chart with all the vital bits and relaxation is basic formatting:
    Step 1: For BCG matrix the data is rather straight ahead. Following is the records I used:
    bcg3
    the entire numbers are given except the relative market share which is calculated by using taking the ration of Market share % with finest competitor market share.
    Step 2: once we've the information, readily select only the figures of the ultimate three columns i.e. relative market share, revenue salary and Market increase %. Go to Insert > charts neighborhood > click on scatter chart button > click bubble chart. A chart should be inserted that is likely not what we require.
    bcg4
    Step three: In my case as chart isn’t plotted against with the correct variables on y axis, so it is going to require an adjustment. Its convenient to do. effortlessly click on any bubble to opt for the series and shuffle the quadrants of salary and Market increase with each and every different. this may fix the chart.
    bcg5
    Now that we've our chart equipped, we should make cosmetic changes to it like moving both axis within the middle to make four quadrants and give it a pleasant heritage to make issues effortless to bear in mind.
    Step four: right click on on x-axis > click structure axis. change the minimum certain to 0 under axis alternatives.
    “below vertical axis crosses” choose axis cost and mention half of maximum relative market share is on your records or another correct determine. As my facts is plotted till “2.00” and that i have “0.0” because the minimum so half is 1.00.
    click on Labels and select low from the listing of label position. I additionally made adjustments to axis color to make extra popular.
    bcg6
    Step 5: correct click on y-axis and click structure axis. Set the maximum value to 1.0.
    beneath “Horizontal axis crosses” select at price and mention 0.5
    click labels and for label position opt for low.
    once more, I change the color of the axis and additionally made it a bit thick to make it trendy.
    bcg7
    Step 6: For heritage we want a picture divided in four quadrants ideally with distinct colours. I used Excel to try this for me. And made as following:
    bcg7
    Step 7: Now here is the nifty trick. click on “print display” button in your keyboard and go to MS Paint and have it pasted. once pasted, select the mandatory half and click reduce.
    bcg8
    Step 8: once chosen and cut, go returned to Excel correct click on inner chart > click on format plot enviornment. below fill choose photograph or texture fill. instantly Excel will pull the newest photographs from clipboard.
    bcg9
    this is definitely it!
    With few extra formatting tweaks like changing chart title, your chart is competent. however, I did one further step in order that i can put x-axis and y-axis labels as smartly. And for that I shifted the graph to an extra worksheet. Aligned it with cells. introduced labels round it and then used camera device to seize that latitude and display under the information range.
    bcg10
    as soon as formatted quite simply choose the enviornment and hit digicam button to capture it. after which go to worksheet where you want photograph to be pasted and easily left click and Excel will put it there. that you may trade the dimension of picture just like usual one but to keep the element ratio intact I held SHIFT key on the keyboard and adjusted its measurement the usage of only diagonal elements.
    bcg11
    next I added labels to the products so that you possibly can quite simply respect which product is plotted where. To get the names however, there is another trick worried and that is to get the labels from the latitude of cells.
    bcg12
    And right here is the last outcomes with delivered borders around bubbles:
    bcg2
    down load thoroughly labored File
    which you can download the wholly worked file free of cost but if you just like the effort and gained anything helpful then that you can set the cost something you deem healthy and pay. if you want to download it at no cost then with no trouble set the fee to “0”. take pleasure in!
    down load Now take a look at greater Excel tutorials concerning company evaluation
  • Dynamic monetary situation evaluation using Excel
  • damage-even + goal profit analysis with Excel aim are searching for – how to
  • Dynamic typical dimension and DuPont evaluation economic Statements in Excel
  • ABC inventory evaluation the use of Excel Charts
  • 10 the right way to present variance analysis stories in Excel

  • Related books

    Wednesday, November 16, 2016

    7 Excel Tricks to Make You a power user

    What does it take to succeed in business today? increasingly, probably the most vital expertise you could have is digital literacy.
    according to Cornell college, digital literacy is defined as: "the skill to locate, evaluate, utilize, share, and create content the use of information technologies and the information superhighway."
    just about eight in 10 middle-skill jobs now require digital advantage, in keeping with fresh research by means of Burning Glass. What's greater, these jobs requiring digital skills pay an ordinary of 18% more than those that don't.
     Excel tricks to make you a power user
    in case you read my blog posts continually, you will know i'm a massive fan of coding as the most important ability an entrepreneur or worker can have nowadays. gaining knowledge of to code offers so many valuable advantages, together with enabling you to retailer money on advertising and net design features, and supplying you with greater standpoint when hiring out coding tasks.
    however when it comes to certain courses, one of the most important ones to grasp is that historical Microsoft standby: Excel.
    truly, 67% of jobs that require digital literacy also require skillability in each note and Excel. maybe you used Excel in college however have not touched it seeing that, or most likely it be some thing you battle with (and despise) on a daily basis. or not it's not truly called a application people love working in, however you can not deny the sheer power of Microsoft Excel.
    in fact, WordStream became born out of my very own should automate a lot of the services i was doing continually as a search advertising advisor. I spent total days (and nights) wrangling Excel and making it do backflips in the mid-2000s. Conditional formatting, pivot tables, forecasting and different services can be crazy elaborate to be trained, but are so vital to understand.
    So I get it. I believe your ache... I needless to say knot for your gut in the event you hear the notice "Excel." It can also be super complicated, there is absolute confidence.
    however Microsoft Excel can also support you shop time, make better enterprise selections, and in the end be a more advantageous asset within the office, no matter if or not it's your personal enterprise or a person else's.
    this is why this infographic I came across is so decent. UK practicing business STL compiled their precise seven Excel tricks in a visible book known as 7 essential Excel tricks each workplace employee should comprehend.
    In it, they present super primary shortcuts to effective Excel capabilities like VLOOKUP, quick analysis, energy View and greater.
    try it out:
    Excel tricks infographic
    graphic credit score: STL

    Tuesday, November 15, 2016

    The way to write data to Excel/CSV in Jmeter the usage of BeanShell scripting?

    You may have faced considerations in writing facts to excel/csv in JMeter. during this blog post, we can see how to write facts to Excel/CSV in Jmeter using BeanShell scripting? by using typical Expression Extractor, you can extract statistics from the response. to write extracted data to excel/csv in Jmeter, you need to use BeanShell PostProcessor sampler.
    a way to extract data from the response?
    you can refer my slideshare presentation on how to extract information from the response.
    the way to write information to excel/csv in JMeter using BeanShell PostProcessor?
    Add Thread community and then right click on Add > post Processor > BeanShell PostProcessor as proven below.
    [auto_thumb width=”150″ height=”150″ link=”” lightbox=”true” align=”center” title=”How to write data to Excel CSV in Jmeter using BeanShell scripting – Add BeanShell PostProcessor” alt=”How to write data to Excel CSV in Jmeter using BeanShell scripting – Add BeanShell PostProcessor” iframe=”false” frame=”true” crop=”true”]http://qainsights.com/wp-content material/uploads/2013/09/How-to-write-statistics-to-Excel-CSV-in-Jmeter-the use of-BeanShell-scripting-Add-BeanShell-PostProcessor.jpg[/auto_thumb]
    E.g. to write down Order Reference number to CSV/Excel file, paste under code to your BeanShell scripting as proven below.
    refnum = vars.get("Reference_Number"); log.information(refnum); // in case you are looking to log something to jmeter.log file // flow authentic if you are looking to append to present file // in case you need to overwrite, then do not circulate the 2nd argument f = new FileOutputStream("yourpath/influence.csv", authentic); p = new PrintStream(f); this.interpreter.setOut(p); print("Reference number is," + refnum); f.shut();
    [auto_thumb width=”150″ height=”150″ link=”” lightbox=”true” align=”center” title=”How to write data to Excel CSV in Jmeter using BeanShell scripting – BeanShell PostProcessor Sample Code” alt=”How to write data to Excel CSV in Jmeter using BeanShell scripting – BeanShell PostProcessor Sample Code” iframe=”false” frame=”true” crop=”true”]http://qainsights.com/wp-content material/uploads/2013/09/How-to-write-facts-to-Excel-CSV-in-Jmeter-the use of-BeanShell-scripting-BeanShell-PostProcessor-sample-Code.jpg[/auto_thumb]
    Thanks for traveling QAInsights! check out our new portal Testifications.com to get updates on application checking out related certifications. We’re always posting unique articles on QAInsights. I request you to subscribe so you don’t omit out the rest.
    looking for QA Jobs Openings, determine it at www.QAJobOpenings.com
    wish to beginning a weblog like this, please consult with SpidyDomain to buy inexpensive net internet hosting and register domain.
    Subscribe to our QAInsights YouTube Channel | Subscribe our free weekly e-newsletter or Google feeds

    Drastin Unveils the realm’s First Conversational Analytics Product Powered by means of AI

    dr
    via Angela bet
    based on a recent press liberate, “Drastin Inc., an emerging leader in conversational analytics and relational search-driven data evaluation know-how, today introduced the release of Drastin 2.0, its software for actionable facts insights. Drastin presents search-driven facts evaluation and exploration with the convenience of a ‘Google-like’ search box. using essential English questions, any enterprise person can communicate with facts, function ad hoc information exploration and achieve moments of readability to expedite more desirable resolution-making. Drastin 2.0 is obtainable for deployment as a SaaS offering within the cloud, or behind the firewall. information is not duplicated and no hardware is required. it really works on diversity of information sources including: (1) Cloud sources: Salesforce, ServiceNow, NetSuite, Office365. (2) On-premise sources: Oracle, MS SQL Server, MySQL, PostgreSQL. (3) Hadoop. (4) Flat information: Excel/CSV.”
    The free up continues, “Drastin 2.0 combines the vigor of machine discovering and natural language processing with relational search to deliver a magnificent records analysis answer for all and sundry in an business. Deployable in just minutes, Drastin requires no user practising. users can event convenient and intuitive search on day 1 devoid of researching complicated query syntax of physical facts sources. listed here are some the powerful capabilities of the product… real time & On-demand: function records analysis with primary English queries in herbal language using the search box. Enter complete questions as opposed to performing a key phrase search. Guided & Responsive advice: event smart counsel powered via desktop studying that learns from you. powerful Visualization: View solutions within the form of effective visualizations that are customized for every consumer via desktop gaining knowledge of. superior analysis: discover information extensive and find new insights.”
    study extra at PR Newswire.
    picture credit: Drastic

    Related Books

    Wednesday, November 9, 2016

    Excel information for search engine optimization and information analysis

    Everybody has likely already realized that there's essentially no facts that we can not get. we are able to get records about our website through the use of free equipment, however we additionally spend lots of money on paid tools to get even more. examining the competition is barely as handy, competitive intelligence tools are in every single place, we regularly use Compete or Hitwise. Opens web site Explorer is incredible for getting more data about our and opponents back-link profile. No rely what counsel we are trying to get, we will, with the aid of spending fortunes or no funds. My favorite half is that almost each device has one average feature and that is the "Export" button. here is the most powerful function of all these equipment as a result of through exporting the facts into Excel and we will model it, filter it and mannequin it in any manner we want. Most of us use Excel on the regular foundation, we're widespread with the simple functions but Excel can do approach greater than that. In the following article i will try to current essentially the most usual statistical options and the better part it is that we do not need to memorize advanced statistical equations, it be every little thing developed into Excel!
    records is all about amassing, inspecting and decoding facts. It comes very convenient when resolution making faces uncertainty. through the use of information, we can overcome these cases and generate actionable analysis.
    records is divided into two foremost branches, descriptive and inferential.
    Descriptive data are used if you happen to understand all of the values in the dataset. for example, you're taking a survey of 1000 people asking in the event that they like oranges, with two selections (sure and No). You compile the results and also you find out that 900 answered yes, and a hundred answered No. You discover the percentage ninety% is sure 10 is not any. pretty fundamental appropriate?
    however what occurs once we can not look at all of the facts?
    if you understand most effective a part of your records than you should use inferential statistics. Inferential data is used in the event you know handiest a sample (a small half) out of your information and you make guesses in regards to the total inhabitants (records).
    Let's accept as true with you are looking to calculate the email open expense for the last 24 months, however you've got facts best from the last six months. in this case, assuming that from 1000 emails you had 200 people opening the e-mail, which resulted in 800 emails that didn't convert. This equates to twenty% open expense and eighty% who didn't open. This information is true for the closing six months, but it could now not be actual for twenty-four months. Inferential information helps us have in mind how close we are to the whole inhabitants and the way assured we're during this assumption.
    The open fee for the pattern can be 20% nonetheless it might also vary a bit. for this reason, let's agree with +- three% during this case the latitude is from 17% to 23%. This sounds relatively good however how assured are we in these facts? on the other hand, how many of a random sample taken from the entire inhabitants (data set) will fall in the latitude of 17%-23%?
    In statistics, the 95% self assurance degree is regarded to be authentic statistics. This potential ninety five% of the sample statistics we take from the complete population will produce an open expense of 17-23%, the other 5% should be either above 23% or below 17%. but we're 95% bound that the open cost is 20% +- three%
    The time period statistics stands for any cost that describes an object or an experience corresponding to visitors, surveys, emails.
    The time period records set has two add-ons, observation unit, which is as an example visitors and the variables that can signify the demographic features of your guests corresponding to age, income or training degree. population refers to every member of your neighborhood, or in internet analytics all the friends. Let's assume 10,000 friends.
    A pattern is only a part of your population, in accordance with a date latitude, visitors who transformed, etc. but in statistics essentially the most helpful sample is considered a random sample.
    The information distribution is given by way of the frequency with which the values within the records set happen. with the aid of plotting the frequencies on a chart, with the range of the values on the horizontal axis and the frequencies on the vertical axis, we acquire the distribution curve. probably the most usual distribution is the regular distribution or the bell-formed curve.
    an easy solution to take into account this is with the aid of when you consider that the number of company a website has. for example the number of visits are on regular of 2000/day however happens to have greater visits equivalent to 3000 or less one thousand.
    here, likelihood conception turns out to be useful.
    probability stands for the chance of an event happening similar to having 3,000 friends/day and is expressed in percentages.
    essentially the most common illustration of likelihood that likely everybody knows is the coin flip. A coin has two faces, head and tail, what's the chance when flipping a coin to have head? well there are two chances so a hundred%/2=50%.
    adequate with theories and let's get a bit bit more purposeful.
    Excel is a good tool that can help us with facts, or not it's not the top of the line but all of us comprehend how to use it so let's dive right into it.
    First, deploy the evaluation ToolPack.Open Excel, Go to options -> Add-ins->on the bottom we are able to locate
    Hit Go ->opt for analysis ToolPack->and click adequate.
    Now beneath the data tab we can discover information evaluation.
    The statistics analysis device can provide you supper fancy statistical counsel however first let's start with some thing less difficult.
    imply, Median, and Mode
    imply is the statistical which means of normal, for instance the mean or standard of 4,5,6 is 5 how we calculate in excel the suggest? =regular(number1,number2,etc)
    mean=usual(AC16:AC21)
    with the aid of calculating the imply we understand how tons we offered on general. This assistance is helpful when there aren't any intense values (or outliers). Why? It seems like we bought on usual $3000 value of products, however in reality we have been fortunate that someone spent extra on September 6. however in fact we did pretty poorly right through the outdated six days, with a normal of only $618. apart from the extreme values from the imply can replicate a extra imperative performance fee.
    The median is the observation headquartered in the middle of the facts set. for instance, the median of 224, 298, 304 is 298. with a purpose to calculate the imply for a big set of statistics we will use right here components =MEDIAN(224,298,304)
    When is the median positive? well, the median is effective if in case you have a skewed distribution, as an example you're promoting goodies for $three up to $15 a bag but you have some very high priced candies for $100 a bag that no one in fact purchases on a daily basis. at the end of your month you need to make a report and you will see that you simply bought mainly low-cost goodies and only a few the $100. during this case calculating median is more a good option.
    The easiest method to examine when to make use of the median vs. the mean is with the aid of making a histogram. if your histogram is skewed with an severe, you then understand that the most fulfilling approach to head is by calculating the median.
    The mode is essentially the most ordinary cost, for instance the mode for: four,6,7,7,7,7,9,10 is 7
    In Excel you can calculate the mode through the use of the =MODE(4,6,7,7,7,7,9,10) formula.
    despite the fact this appears first-rate bear in mind that in Excel the lowest mode is regarded, or in different words, in case you should calculate the mode for here information set 2,2,2,four,5,6,7,7,7,eight,9 you can see that you've two modes, 2 and 7 but Excel will show you handiest the smallest price: 2.
    When do we use the mode function? Calculating the mode is really useful only for total numbers corresponding to 1, 2 and 3. It is not positive for fractional numbers comparable to 1,744; 2.443; three,323, as the probability to have duplicated numbers, or a mode, is terribly small.
    a pretty good instance of calculating the mode, or probably the most widely wide-spread number, can be probably on a survey.
    Histograms
    for example your blog currently acquired lots of of visitor posts, some of them are very respectable ones but some of them are just not that respectable. might be you need to see how many of your blog posts acquired 10 back-links, 20, 30 etc, or maybe you are interested in social shares akin to tweets or likes, however why not just with ease visits.
    here we are able to categorize them into groups by using a visual representation referred to as histograms. during this illustration i will be able to use visits/articles as an easy example. the style I setup my Google Analytics account is as follows. I have a profile that tracks best my blog, nothing else. in case you don't have such profile setup yet, then that you may create a section on the fly.
    How are you doing this? relatively elementary:
    Now go to export->CSV
    Open the excel spread sheet and delete the entire columns other than touchdown page and Visits. Now create the tiers (also called boxes) that you just wish to be categorized into. for instance we need to see how many articles generated a hundred visits, 300, 500 and so forth.
    obtained to facts -> records analysis->Histograms->ok
  • input range could be the visits column
  • Bin range can be the organizations
  • Output range, click on the mobile where you need your histogram to exhibit up
  • verify Chart Output
  • click on ok
  • Now you have got a nice histogram that shows you the variety of articles categorized by visits. To make it less demanding to understand this histogram, click on any telephone from the Bin and Frequency table and kind the frequency with the aid of low to high.
    inspecting now the records is even more straightforward. Now go returned and sort all of the articles with much less or equal to one hundred visits (talk over with drop down->number filters->Between...0-one hundred->ok) within the remaining month and update them, or promote them.
    Visits via supply
    How useful this file is for you?
    it be pretty respectable but not magnificent. we can see united statesand downs but...how a whole lot did YouTube make a contribution in February to the entire visits? that you could drill down however it's added work, and it is very uncomfortable when the query arrives on a cellphone name with a shopper. To get probably the most out of your graphs, create positive self-descriptive reports.
    The file above is so a lot more straightforward to remember. It takes greater time to create it nonetheless it's more actionable.
    What we are able to see is that in may, facebook had a bigger quantity of contribution to the full than in frequent. How come? likely the may advertising and marketing crusade turned into more helpful than in different months, resulting in a lot of traffic. Go returned and do it once again! If it was a working solution, then repeat it.
    in case you believe that may additionally is only unintentionally larger than the rest of the months, then remember to create a Chi-rectangular verify to make sure that the increase in visits is not accidentally and it's statistically confirmed the effectiveness of your campaign.
    The exact column is the number of visits, the anticipated column is the mean(typical) of the "actual" column. The formulation of the Chi-rectangular test is =1-CHITEST(N10:N16,O10:O16) the place N10:N16 are the values from specific and O10:O16 the values from anticipated.
    The effect of 100% is the self belief degree so you might have when because that the work invested in every month campaign affects the variety of company coming from fb.
    When growing metrics, make them as effortless as viable to take into account, and principal to the enterprise model. all and sundry may still bear in mind your studies.
    The video below explains pretty smartly a different illustration of Chi-square characteristic:http://www.youtube.com/watch?v=UPawNLQOv-8
    moving normal and linear regression for forecasting
    We regularly see graphs just like the one above. it may possibly represent revenue or visits, it would not actually count number, it's invariably going up and down. there's loads of noise within the records that we doubtless want to eliminate to generate a higher realizing.
    The answer, moving commonplace! This technique is now and again used by way of merchants for forecasting, the stock prices are booming someday however in the 2nd they are hitting the flooring.
    let's examine how we can use their basic ideas to make it work for us.
    Step 1:Export to excel the number of visits/income for a very long time duration, akin to one or two years.
    Step 2:Go to data-> information analysis -> moving general ->adequate
    enter range might be the column with the number of visits
    Interval might be the number of days on which the common is created. here you'll want to create one relocating ordinary with a far better number such as 30 and an extra one with a smaller quantity akin to 7.
    Output latitude should be the column right subsequent to the visits column.
    Repeat the steps for the interval of 7 days
    very own option: I did not check the chart output and commonplace error field on purpose, i will create a graph later on.
    Your information now doubtless appears corresponding to this:
    Now if you opt for the entire columns and create a line chart it will look like this:
    This illustration has much less noise, it's more straightforward to examine and it shows some developments, the green line cleans up a bit bit within the chart nevertheless it reacts to basically every essential adventure. The purple line as a substitute is greater strong and it indicates a true trend.
    at the end of the line chart you can see that it says Forecast. it's forecasted facts in line with old tendencies.
    In Excel there are two techniques for making a linear regression, the usage of the formula =FORECAST(x,known_y's, known_x's) the place "x" stands for the date you wish to forecast, "known_y's" are the visits column and "known_x's" are the date column. This approach isn't that advanced but there is an easier approach to do that.
    with the aid of deciding on the entire visits column and dragging down the field tackle it will immediately forecast for the following dates.
    note: be sure to opt for the whole records set with a purpose to generate an accurate data set.
    there's a concept when evaluating a 7day moving usual and a 30day. As talked about above the 7day line reacts to almost every principal exchange whereas the 30day one requires greater time to change its course. customarily of thumb when the 7day relocating ordinary is intersecting the 30day moving commonplace then you could predict an incredible alternate so that it will final longer than a day or two. As which you could see above round April sixth the 7 day relocating usual is intersecting the 30 day one and the number of visits are taking place, round June 6th the traces are crossing once more and the tendencies are going upward. This technique is useful should you are dropping site visitors and you don't seem to be yet certain if it is simply the style or it is only an everyday fluctuation.
    Trendline
    The identical outcomes will also be done through the use of the trend line function of excel: right click on the wiggling line -> choose: Add Trendline
    Now that you would be able to choose the Regression classification and you'll use the Forecast function as smartly. Trendlines are probably probably the most beneficial to discover if your traffic/income are going upward, downward or are with no trouble flat.
    devoid of the linear function we can't confidently tell if we are doing better or not. with the aid of adding a linear trendline we can see that the slope is advantageous the trendline equation explains how our vogue is moving.y=0.5516x-9541.2
    X represents the number of days. The coefficient to x, 0.5516, is a positive quantity. This capability that the trendline is going upward. In different words daily that passes by using we raise the variety of visitors with 0.5 as a vogue.
    R^2 represents the level of accuracy of the mannequin. Our R^2 quantity is 0.26 which suggests that our mannequin explains 26% of the adaptations. effectively observed: we're 26% confident that every different day that passes with the aid of our number of guests increases with one new tourist.
    Seasonal Forecasting
    Christmas is coming soon and forecasting the iciness season can be beneficial chiefly when your expectations are high.
    if you failed to get hit with the aid of Panda or Penguin and your earnings/company are following a seasonal vogue, then that you can forecast a pattern for income or visitors.
    Seasonal forecasting is technique that permits us to estimate future values of an information set that follows a routine model. Seasonal datasets are in all places, an ice cream store could be very ecocnomic throughout the summer time season and a gift store can attain the highest earnings all through the winter vacation trips.
    Forecasting data for close future may also be very a good option, specifically once we planning to make investments cash in advertising for these seasons.
    here illustration is a primary model but this can be multiplied to a greater complicated one to suit your company model.
    download the Excel forecasting instance
    i will be able to spoil up the method into steps to be more straightforward to comply with. The gold standard way to put in force it for your business is by using downloading the Excel spreadsheet and following the steps:
  • export your statistics, the more records you have got the superior forecasting you could make! and location the dates into column A and sales into column B.
  • Calculate the index for each month and add the statistics in column C
  • with a purpose to calculate the index scroll down on the backside right of the spreadsheet and you'll find a desk referred to as Index. The index for Jan-2009 is calculated via dividing the revenue from Jan-2009 through the ordinary revenue of the complete year 2009.
    Repeat calculating the index for each month of every year.
    In column S38 to S51 we calculated the commonplace index for every month
    because our seasonality is every 12 month we copied the index capacity into column C over and over once again matching up every month. As which you can see January of 2009 has the equal index statistics as January 2010 and 2011
  • In column D calculate the Adjusted facts with the aid of dividing the monthly income by way of the index =B10/C10
  • select the values from column A, B and D and create a line chart
  • opt for the adjusted line (in my case the purple line) and add a linear trendline, investigate the "display Equation on Chart" box
  • Calculate the backcasted non-seasonal information via multiplying the monthly income by using the coefficient from the trandline equation and adding the consistent from the equation (column E)
  • After growing the trendline and we displayed the Equation on the chart we trust the Coefficient the quantity which is elevated with the aid of X and the steady the number that's constantly has a negative sign.
    We location the coefficient into phone E2 and the regular into mobile F2
  • Calculate the Backcasted Seasonal information by using multiplying the index (column C) with the prior to now calculated records (column E)
  • Calculate MPE(imply percentage error) by means of dividing earnings via Backcasted seasonal minus 1 (=B10/F10-1)
  • Calculate MAPE (imply adjusted percentage error) via squaring the MPE column (=G10^2)
  • In my case cell F50 and F51 represents the forecasted statistics for Nov-2012 and Dec-2012. phone H52 represents the error margin.
    through the use of this approach we will say that in December 2012 we're going to make $22,022 +- 3.eleven%. Now go to your boss and display him easy methods to predict the longer term.
    normal deviation tells us how a great deal we deviate from the imply, in other phrases we are able to interpret it as a self assurance stage. for example in case you have monthly income, your each day income should be diverse each day. Then you could use the ordinary deviation to calculate how tons you deviate from the month-to-month standard.
    There are two normal Deviation formulas in Excel that you should use.=stdev -when you've got sample data -> Avinash Kaushik explains in more details how sampling works http://www.kaushik.web/avinash/web-analytics-statistics-sampling-411/
    or
    =stdevp -if you have the total inhabitants, in different words you are examining each visitor. My very own option is =stdev just as a result of there are instances when the JS tracking code is not carried out.
    let's have a look at how we can practice common Deviation in our everyday life
    doubtless you see the wiggling graph in analytics every day but it surely is not very intuitive. by using general deviation in Excel you could easily visualize and take into account more desirable what's happening with your information. As you can see above, general every day visits have been 501 with a common deviation of fifty three, also essentially the most crucial, that you can see the place you passed the common so you can go lower back and take a look at which of your advertising efforts brought about that spike.
    For the Excel document use right here link http://weblog.instantcognition.com/wp-content/uploads/2007/01/controllimits_final.xls
    Correlation
    Correlation is the tendency that one variable exchange is involving a further variable. a typical example in net analytics can also be the number of company and the number of sales. The more certified company you've got the extra sales you have. Dr Pete has a nice infographic explaining correlation vs. causation https://moz.com/weblog/correlation-vs-causation-mathographic
    In Excel we use here formulation to investigate the correlation:=correl(x,y)
    As you could see above we now have a correlation between Visits and revenue of 0.1. What does this suggest?
  • between 0 and zero.three is regarded weak
  • between 0.three and zero.7 is standard
  • above 0.7 is robust
  • The conclusion in our case is that every day visits don’t have an effect on day by day earnings, which also means that the guests that you are attracting don't seem to be certified for conversion. You even have to agree with your business sense when making a call. but a correlation of 0.1 may also now not be neglected.
    if you wish to correlate three or extra datasets that you can use the correlation function from the statistics evaluation device.
    statistics->facts evaluation->Correlation
    Your result will appear corresponding to this one:
    What we will see right here is that not one of the points correlate with each and every different:
  • revenue and company= correlation of 0.1
  • sales and Social Shares = correlation of 0.23
  • Descriptive statistics for brief analysis
  • Now you have decent figuring out of the mean, average deviations and many others. but calculating each and every statistical aspect can take a long time. The information evaluation device provides a brief abstract of essentially the most average features.
  • Go to statistics->data evaluation-> Descriptive records
  • enter range - select the records you want to analyze
  • Output range - select the phone where you desire your desk to be displayed
  • verify abstract information
  • The result is fairly excellent:
    You already comprehend lots of the elements but what's new right here is Kurtosis and Skewness
    Kurtosis explains how far peaked the curve is from the mean, in different words the bigger the kurtosis value is the bigger the top is on the sides, in our case the kurtosis is a very low number which means the values are spread out evenly
    Skewness explains in case your records is negatively or positively skewed from a normal distribution. Now let me exhibit you extra visually what I suggest:
    Skeweness: -0.28 (the distribution is more probably oriented towards the bigger values 2500 and 3000)Kurtosis: -0.forty seven (we've a really small height deviation from the center)
    These are one of the vital options for you to use when analyzing information, the greatest problem behind data and Excel is the ability of making use of these strategies in various situations and never being restricted to visits or earnings. a very good example of distinct statistical procedures carried out together became realized through Tom Anthony in his submit about hyperlink Profile tool.
    The examples above are only a small fraction of what can also be done with statistics and Excel. when you are the usage of different options that aid you're taking faster and improved selections i'd love to hear about them within the comment section.