Why do you need to define a period for the IRR, MIRR and NPV financial functions in Easy Excel Analysis and SQL Data Analysis

Easy Excel Analysis and SQL Data Analysis added support for financial functions in version 2.  One quirk you may have noticed is that for the IRR, MIRR, and NPV functions, you need to enter the column containing a period.  Excel doesn’t require this, so why is it required here?

Let’s take a step back and use a simple example in Excel to explain this requirement.  Say we have a single investment we want to calculate the NPV for.  We would list the net cash flow in chronological order and calculate the NPV accordingly.

The point to note is that the order of the net cash flow needs to be sorted by period to be accurate.  If the values were not sorted, we would not get the correct value e.g.

Now say we have to deal with multiple ‘groups’ of investments.

In the example above, we are calculating the NPV for each investment.  In Easy Excel AnalysisSQL Data Analysis, we get the same values when we group our data by the Investment column.

Now say we want to compute the NPV of each category of investment.  In Excel, we can’t just sort by the Category column to compute the NPV.

neither can we just sort by both the Category and Period columns this way:

The correct way is to sum the Net cash flow values for each period by category i.e.

To perform the same computation In Easy Excel Analysis and SQL Data Analysis, we just drag the Category column to the grouping area like this:

That is the reason why Easy Excel Analysis and SQL Data Analysis requires a period value when calculating NPV, IRR and XIRR.  It uses the values in that column to determine how to group the cash flow values when grouping rows together.

To see the NPV value by individual investments,  we just drag the Investment column to the grouping area.  Now we can easily see the NPV per category and per investment.

Thus it’s just a matter of dragging and dropping which columns you want to group by, and Easy Excel AnalysisSQL Data Analysis will calculate the NPV for you at each grouping level.  It could not be easier.

Download a 14-day trial of Easy Excel Analysis or SQL Data Analysis, and start analyzing data in ways you never could in Excel, or would never do because it was too time consuming.

Financial and statistical functions in Easy Excel Analysis and SQL Data Analysis

In Easy Excel Analysis and SQL Data Analysis 2, we added statistical and financial functions to the analysis tables.

To use these functions, right click on the column header you want to compute to bring up the context menu.

Select the advanced item.  This then brings up the Advanced Summary Item window where you can define the data source for the various parameters required for the function you select.

See also:

 

Search text in multiple PDF files fast

So you want to search for text in multiple PDF files?  You can do that in Adobe Acrobat, and Google will turn up a few guides on doing that.

That’s all good and fine, but what if you need the search results fast and you need to search hundreds or thousands of PDF files?  Then you should consider Easy PDF Search.

Speed

Easy PDF Search is fast.  Watch this video comparing Easy PDF Search with Adobe Acrobat.  In short, to search for a word the second in 46 files totaling 1 GB in size, Easy PDF Search took 3 seconds while Adobe Acrobat took 3 minutes 13 seconds.

We have a user who regularly searches his collection of over 12000 PDF files using Easy PDF Search, and he gets his search results in less than 20 seconds.

Search multiple words simultaneously

Search for multiple words simultaneously.  Why waste time searching the same files for different words?  Easy PDF Search lets you search for as many words or phrases as you require.

Quickly see where your words were found

Easy PDF Search doesn’t just tell you which files your words were found in, it tells you exactly which page you can find the words in, and the frequency of the words on each page and the entire file.

In the integrated PDF viewer, all your words are highlighted on each page.

View results from past searches

Easy PDF Search maintains a search history of the words you searched for and also of the search results.

This means you can easily view the search results from past searches without having to reperform the search.

By now, you can see that Easy PDF Search is designed to save you time and help you search for text in multiple PDF files fast and easily.

In addition to the above, there is a lot more you can do with Easy PDF Search like:

  • merge all the pages from the search results into a single PDF file
  • copy all the files in the search results
  • extract text from the pages where the words were found in
  • perform proximity searches e.g. NEAR (authorities “homeland security”, 20)
  • perform exclusion searches e.g. monitoring NOT daily
  • search PDF annotations and file attributes

Download a 14-day trial of Easy PDF Search and start using your PDF collection to their full potential, or visit our web site for more details.

Extracting attachments from an Access database using SQL Image Viewer

Access has a curiously interesting data type – attachment.

I say interesting because it does so much, but much of it is done behind the scenes.  To the user, it’s just so easy to attach one or or more files to that column.

For starters, some file types seem to be compressed automatically by Access.  I attached a 23 MB text file, and the database size only increased by 3 MB, which is about the size of what my text file would have compressed to.

Also, a single attachment-type column contains 4 sub-columns in them – FileName, FileType, FileData and FileURL (for Sharepoint databases).  These are the sub-columns we need to include in our query in SQL Image Viewer when we want to extract the files from the attachment-type columns.

Say we have a table defined as such:

with the following data:

Row 1 has 1 attachment, and row 2 has 2 attachments.  If we run a query that selects just the 3 columns from the table in SQL Image Viewer, we get this:

The files column, which is the attachment-type column, displays only the names of the attached files, and is returned as a text column.  If however we selected the sub-columns:

Access returns 3 rows instead of 2.  Because our 2nd row contained 2 attachments, Access returns 2 data rows, one for each attachment.

Thus, to extract the data from the attachment-type columns in SQL Image Viewer, we need to select the filedata sub-column of the attachment-type column.

Since we added support for attachment-type columns beginning SQL Image Viewer 11, the same query above now returns this:

 

Extracting attachments from an Access table using Access OLE Export or SQL Blob Export

Access OLE Export 4 and SQL Blob Export 4 have added support to extract files from attachment-type columns in Microsoft Access tables.

Say we have a table named table001 with the following structure:

Attachment-type columns in Access contain sub-columns that contains details of one or more attachments stored in that row.  If we just select the table during the export process like this:

we would not be able to extract the attached files.  What we need to do is to select the Extract items using SQL query option, and enter a query to retrieve the sub-column (FileData) that contains the actual file contents.  The other useful sub-column is FileName, which stores the original file name.

We select the sub-columns by entering the column name (files), followed by a dot (.), then the sub-column name.  In the example above, to retrieve the contents of the attachment column, we enter

files.FileData

and to retrieve the original file name, we enter

files.FileName

Now if we want to, we can name the exported files using the original file name.  Enter

<filename>

as the column to use to name all the files that are exported, in the Other types section.

Access OLE Export or SQL Blob Export will then export each attachment returned by your query, and name the files using the original file names.  Of course, you can still use a different naming convention if you need to do so.

Full text index for your PDF files

Are you considering creating a full text index on your PDF files, so that you can frequently search for words and phrases fast?  That’s what Easy PDF Search was created for.

Say you have a collection of PDF files for various topics.  You can organize your files into libraries so that when you run your search, you can choose to search only in specific libraries.  You don’t have to always search your entire PDF collection.

In Easy PDF Search, you can search for multiple words simultaneously.  Here, we are searching for all files containing the words monitoring, splices or pressure.

Our search results are then returned, grouped by each search word.

And on each page, our search words are highlighted in a different color.

Now what can you do with those search results?  In Easy PDF Search, lots.

For starters, you can export the search results listing or just the file names, for future or offline reference.

Next, you can work with the PDF pages from the search results.

You could extract each of the pages containing your search words and compile them into a single PDF file.  You could also extract the text found on those pages, or extract the pages into individual PDF files, and much more.

Easy PDF Search also keeps a search history, so you can just refer to it whenever the need arises without having to reperform the search.

Give Easy PDF Search a try.  We offer a 14-day fully functional trial so you can experience for yourself how easy it is to create a full text index for your PDF files and search those files fast.

Introducing Easy PDF Search 3

Easy PDF Search (EPS) 3 focuses on 3 areas – support more search options, more user actions on the search results, and general performance improvements.

More search options

In version 2, we added the option to search only the existing index.  This allows you to make very fast searches without having to check for new or modified files to index, or when the indexed files are not accessible.  In version 3, we added an additional option to search the existing index only for files in the selected libraries.

We also added the option to return only the file names from the search.

A good portion of the search duration is actually spent identifying which words to highlight in the search results.

When you only need the list of files where the search words were found, then selecting the  Return file names only option would speed up your searches even more.

User actions on search results

In previous versions, while you could work with the search results like combining all the pages into a single file, extracting the search pages into individual files etc, you could not work with the results listing itself.

In version 3, you now have a context menu that allows you to perform various actions on the search results listing, like copying the list of files to the clipboard, opening the containing folder etc.

General performance improvements

We have improved the performance where possible, especially when dealing with large collection of files.  The search history listing now loads faster too.

Miscellaneous UI improvements

We have also made various minor UI tweaks to improve usability.  An obvious addition is the availability of in-built icons you can easily add to your library definition.

This helps you to quickly make your libraries more distinctive.  Of course you can still always use your own icons.

If you would like to give Easy PDF Search a try, you can download a free 14-day fully functional trial here.

Standards and audit compliance in PostgreSQL databases

In DB Doc 9, we made a couple of additions to our existing reports that help in ensuring compliance with company standards with regards to table and column definitions.

At the database level, there is now a listing of all the tables across all schemas.  This allows you to easily look for similarly named tables, and ensure that they conform to your naming standards.

Similarly, there is now a listing of all the table columns across all schemas.  This allows you to easily ensure that the column naming conventions are consistent, and that the data types are also consistent for similar columns.

At the schema level, there is also a listing for all the columns in the schema.

What can you do with these reports?

  • you get a quick overview of all tables across all schemas in your entire database, allowing you to compare attributes like naming convention, size, estimated rows, etc
  • you get a quick overview of all columns across the entire database and across all schemas, allowing you to easily check for compliance with naming convention and data types/domains
  • your database administrators and developers get a single point of reference of your database schema, which can be updated as frequently required using DB Doc’s command line execution

Customizing the reports

By default, all the above 3 listings are included in the HTML, XML, CHM, PDF, and DOC reports.  You can remove one or more of the above listings by simply omitting the relevant user defined values, as explained in the help file here.

You can also easily customize what is displayed in each of the listings using DB Doc’s advanced scripting and report template features.  For e.g. you could include the index size in the table listing, or display the nullable and unique attributes in the column listing

Give DB Doc a try now

Download the installer here and see how DB Doc can help you generate accurate and timely database documentation in minutes.  We have a walkthrough available here to guide you for new users.

Easy PDF Search – the search options explained

When searching for words and phrases in Easy PDF Search (EPS), you have 4 options:

For the first option, the process flow is as follows:

  • EPS looks for all the folders set up in the selected libraries
  • in each folder, EPS compiles a list of all the files matching the search pattern
  • for each new file, EPS will index that file
  • for each modified file, EPS will rebuild the index
  • EPS then searches for the entered words/phrases in the list of files it compiled in step 2 above

For the second option, the process flow is as follows:

  • EPS looks for all the folders set up in the selected libraries
  • in each folder, EPS compiles a list of all the files matching the search pattern
  • for each file, EPS deletes any existing index, and builds the index again
  • EPS then searches for the entered words/phrases in the list of files it compiled in step 2 above

For the third option, the process flow is as follows:

  • EPS looks for all the folders set up in the selected libraries
  • in each folder, EPS compiles a list of all the files matching the search pattern
  • EPS then searches for the entered words/phrases only in the files where an index has already been created

For the fourth option, the process flow is as follows:

  • EPS searches for the entered words/phrases in its existing index.

The point to note is that in the first 3 options, Easy PDF Search only returns results from files that exist.  If a PDF file has already been indexed previously but no longer exists, EPS will not search the index of that file.

Converting DICOM images to png, tif, jpeg

In DICOM Search 1.3, you can now convert the DICOM images in your search results to other formats, like png, tif, jpeg, bmp, and gif.

You need to first run a query to retrieve some images.  Let’s search for all MR images for the brain, from the sample images used by our tutorial.

This returns 13 images.

Let’s convert those 13 images to another format.  Click on the Copy images button.

This brings up the Copy Images window.

Enter the folder to copy/export the images to.  If you don’t select the Convert image to another format, then the files will just be copied from the source folder to the selected folder.

If you do select the Convert image to another format option, you can select the format to convert to.

Note that only the TIFF format supports multi-frame images.

In this example, we will also add a gray border around our image, so that the tag values from our information profile do not overlap with our images.

When converting our DICOM images, we also have the option of using our information profile to embed DICOM tag values into the converted images.

Let’s select the Generic information profile, and click Next to start the export/conversion process.

This is one of the exported images.

As you can see, we added a gray border around our image, but perhaps we could have made the left border larger, as the tag value covers part of the image.  Or we could have made the information profile font smaller.  Or we could adjust our information profile so that the values are only displayed on the right sides.  There are so many options open to us, so do experiment to see what works best for you.