Link FRx to Excel to Calculate Statistics

Pulling data from Excel into FRx

RevPAR (revenue per available room), Occupancy Rates, Census, Revenue per Employee, RPU (revenue per user), Utilization, Realization Rate, Per Client Profitability…these are just a few of the many performance metrics that can be measured using FRx. Every industry has its own measures of performance, and all are handled using FRx calculations.

The calculations depend on whether the statistical data lives in the GL or in Excel. For the purposes of this explanation, we’ll assume it is maintained in Excel. (For what it’s worth, I much prefer to see it in the GL, but that’s another story.)

So here’s how to pull that statistic in from Excel. There are two different ways to use Excel with FRx, and we’ll focus on the one that’s most commonly used with a single statistic, the Combined Method.

In the row for the catalog (report) that you want to link to Excel, doubleclick the column heading for column H, Link to General Ledger. The Links dialog box opens. Change the Link Type to GL + WorkSheet, and add the excel file name as shown:


Back in the row, the syntax used is this:
@WKS(C=A3, E=A17, G=A3, I=A17, K=A3, M=A17)/CPO


where C, E, G, I, K and M are the FRx column layout headings, and A3 and A17 are the Excel cell references.

The /CPO will make FRx pull the correct data for whatever period is being run, so you can use one Excel file with January through December in columns B through M, and FRx will count the number of periods over from column A (in this example) and pull the correct month’s data.

When the link is working correctly, you can continue in the row with a CAL format code to divide the statistic into revenue or gross margin or whatever needs to be used for your calculation. Might need to set the format as well, and it will look something like this if it’s a percentage:


Depending on the report design, you may need to set the calculation priority in the catalog, Report Options tab, Advanced tab, to Calculate Columns First.

Note that you can also link to multiple worksheets in a workbook, but the technique is different. You use the Separate Method, but I’ll save that for another day!


  1. yomero says

    I am very interest in this process, you can explain a little more
    whats happens whit the columm layout?
    thanks for you answer

  2. Aimee Crowe says

    Can you tell me how you would do this if your information was stored in GL instead of Excel?

  3. Aimee Crowe says

    We have unit accounts set up. I just noticed that in the column format in FRX, you can choose a book code and the example in the manual shows what looks like statistical options. But I don’t know how to get my stuff into a book code. I’ll check with them.


  4. Laura Osborn says

    Hi, Saw your comment that you prefer statistical data to live in the gl.
    How does that work?

  5. says

    Hi Laura…most mid-market GLs have different ‘books’ or ‘ledgers’ that you can use for non-financial information. They use account numbers just like the GL. Then FRx accesses these books in the ‘Book Code’ field in the column layout (and in the row format with a row modifier). So instead of having to link to Excel to pull in your stats, point to the Book that contains the stats and you enter an account number. In many cases this is much simpler than linking to Excel. Jan

  6. Beatrice Ackerman says

    I used FRx between 2005 and 2007, and have not looked at it since then. I need to know if there is a website where I can get a quick refresher and also see the features that have been added since that time.


  7. Jan Harrigan CPA says

    Hi Beatrice…because FRx is being phased out, there hasn’t been much new development on it, so there won’t be much change. You can always review the blog posts at this site and see if that helps any. I don’t know any other sites that focus only on FRx like I do. If you still want more, I have 2 FRx training manuals for sale, an introduction and an intermediate. They are self-study tutorials designed to give a solid foundation. You might be able to skip straight to the intermediate! Good luck! Jan

    PS. Here’s the link to the manuals:

  8. Jan Harrigan CPA says

    If the file is in the IO_data folder, then you can normally just put the filename. (I say normally because IO_data is the default folder for input/output in the Company Information screen.) You can put the file elsewhere, but the full path has to be there. Cheers—Jan

  9. Diego Chahin says

    Hi Jan

    I am pulling info from a worksheet. However, for accounts with credit balance, whenever I set the Normal Balance column to “C” it doesn’t change the sign as with regular GL links. Is this even supported?



  10. Jan Harrigan CPA says

    Hi Diego…whatever sign you want to pull into the report…that’s what you should put in the worksheet. Good luck…Jan

  11. Akber says

    Hi, I have one TOT column and wanted to develop the Dynamic Forecast report, however you can not bind the WKS column with total column as the error message coming “a link may not be added to a row when format code is present (in column c)”.

    HOw to solve this issue. Can you share your expeirence for this how I can print 9 months actual data and 3 months budgeted data from Excel file.

  12. Jan Harrigan CPA says

    Hi Akber…You can add the excel link to one of the rows that the TOT row references instead of the TOT row itself. For a forecast like this, though, I like to see budgets loaded in the GL, not in excel. Makes the report design a lot easier. Good luck…Jan

Leave a Reply

Your email address will not be published. Required fields are marked *