Open Access Good Practice Project: GW4 Offset Implementation Review

This review of current offsetting deals focusses on the practical issues of implementing the available deals and briefly compares the deals with the Jisc Principles for Offset Agreements.

Open Access Good Practice Project: GW4 Offset Implementation Review

This document has been updated in November 2015 to reflect an error in the data collection.
The review is an output of our Jisc Good Practice Pathfinder project, any feedback, comments or issues please tell us!

Finlay Jones
University of Exeter

Managing APC payments using Access and Excel: a comparison

Managing APC payments for Wellcome Trust and particularly RCUK requires the collection of a lot of data for each payment. Finding a way to record all the necessary information in an efficient way is a significant challenge.

The most common method for collecting this data is an Excel spreadsheet, with a few institutions opting for an Access database. Two of the GW4 Pathfinder institutions, Bristol and Exeter, have just transferred their data between these two systems in opposite directions. The following details their reasons and experiences.

Excel to Access
At Bristol we have been grappling with a large and unwieldy spreadsheet of APC data. This happened gradually as new demands were made on the data for reporting internally and externally, and over time, more and more columns were added to the spreadsheet. By the time the number of columns had gone round to the beginning of the alphabet again, it seemed like time to change the system.

Although unwieldy, the spreadsheet was functioning adequately but was dependent on a number of formula and data validation methods to automate the data entry as much as possible. This was not too much of a problem until the expanding team meant that the fragility of the system was revealed. The formulas were easy to override by simply accidentally typing in the wrong cell and not all members of the team had the Excel knowledge to fix problems that occurred. The spreadsheet had simply become too complex and was leading to mistakes that were time consuming to fix. Add to that the inefficiency of simply scrolling through the many columns and the problems caused by the fact that only one member of the team could use the spreadsheet at the same time, and it meant that the spreadsheet was becoming extremely problematic.

The solution was an Access database. This was not a quick fix, it involved considerable preparatory work to clean up the Excel data, plan the structure of the tables and determine the relationships between them. We decided to put in the time in this preparatory phase in order to maximise the benefits of transferring to Access.

We have now transferred from Access to Excel, but we are still learning. We have not yet determined how to do all of the tasks that we could previously do in Excel. Monitoring the spending, particularly the totals for individual prepayment accounts, has still not been completely resolved. Despite this, we are already seeing the benefits in the data entry. The ability to use queries and forms means that it is easier to concentrate on the required part of the workflow, and data entry is quicker and more accurate.

We are really pleased we made the change; getting data in is already significantly improved, and we’re confident that this will be true for getting data out soon.

Access to Excel
The change from Access to Excel was largely prompted by staff turnover. Both members of the OA team who had been involved with setting up the Access database, as well as two other Access experienced members of staff within the library, left the University.

Inputting data into Access was extremely easy. All the required fields were displayed on one form, which was completely visible on one screen. This meant all data for each request was immediately visible on one screen, and made searching for data easy. However, a lot of the data generation for reporting was not entirely straightforward and we also felt that we did not use Access as expansively as we could have.

We used Access as it had been left for several months, before a change in our OA payment practice prompted the change to Excel: We needed to edit the data input form on Access but no one within the library had the expertise to do so.

When we first made the change we spent some time ensuring the fields were correct for both our data input and reporting needs, and working on pivot tables for reporting. This has now made it simpler to get quick snapshots of where we are with our spending. It also gave us a good opportunity to liaise with our colleagues from Research Accounting, who were using Excel to gather data for the Wellcome Trust reports. Now we have a unified spreadsheet which both departments use, which has reduced duplication in the University, as there is no need for us to pass information back and forth.

The fact the RCUK recommended JISC APC data template that has come from the JISC Monitor project is an Excel spreadsheet also means we can now integrate the institutional specific data gathering with the data for the RCUK report. The nature of formulas and tables within this template means that the additional fields we needed to add to the spreadsheet had to be added to the end otherwise they corrupt the formulas present on the template and the auto-complete fields are not filled in. While this causes the order of some of the data fields to be slightly illogical, it does not create huge problems. All the extra fields we need to fill in are together, but simply at the end of the spreadsheet instead of at the beginning. The size of the spreadsheet also introduces some risk of error as you scroll across the numerous columns.

It is hard to answer whether or not it would have been preferable to stay on Access, if staff expertise were different. Data input is easier in Access, but we believe that Excel is overall easier to use and more accessible for new staff. It also helps us be more efficient and work more closely with our colleagues, as well as tying in directly with RCUK’s recommended data collection template.

Kathryn Smith University of Bristol
Finlay Jones University of Exeter

Key Fields for APC Internal Institutional Reporting

Following publication of the Open Access Reporting Checklist, identifying the data required for RCUK and HEFCE OA/APC reporting, we wondered if it might also be useful to investigate the data used in institutional internal APC reporting across the four GW4 collaborators to determine if any data fields are common and what, if any, overlap there might be with the data fields required for external RCUK and HEFCE reports.

The investigation revealed that internal reports provide summaries or overviews of APC activity focusing on one of two main areas; expenditure of the RCUK grant and the extent of APC practice within the institution. These measures appear to be the main criteria GW4 institutions use to evaluate APC activity but no institution reports on both. At least one summary report is produced by each institution but multiple summary reports are also produced for different internal bodies. Only one institution provides detailed data on individual APC articles to other departments or services. Recipients of the summary reports were OA steering groups, research committees, research management and scholarly communications groups. One institution provides an annual report to the Vice Chancellors group.

Data collection has been simplified, in some instances, by re-using the data template from the Jisc RCUK report for internal reports or by using the same data fields, or a subset, for multiple reports.

Analysis of the data elements within the reports revealed no data element was used by all four GW4 institutions in internal reports. Report objectives appear to be the determining factor influencing data content; institutions who focus their reports on similar areas may use the same data element. Common data fields with external Jisc and HEFCE reports only occur if the Jisc template has been intentionally selected to simplify report production or if internal institutional and external report objectives are similar. Even within reports with similar objectives the levels of detail required created diverse data requirements.

It is difficult therefore to identify data fields which are key to internal reporting as the selection and use of fields is dependent on the report objectives and detail required by each institution.

It would be interesting to hear of other institutions experiences with internal reports; how they decided the focus of their reporting, whether they had clear objectives for their reports and how this influenced the data they collect and monitor. It would also be interesting to know how many different reports are produced by institutions and if reporting was simplified and by what means.

Pathfinder update – September 2015

 The last few months have seen a number of outputs from the GW4 Pathfinder group based around models of payment, from off-setting, to the current state of play with APC intermediary services to a wish list for the Ultimate Prepayment Account.

 Our Exeter colleague, Finlay Jones, has been examining the situation around third party intermediary services for APC payments.  We assumed this would be a growth area when we started this Pathfinder Project, hot on the heels of the Jisc APC project. 

As is often the case in the Open Access landscape, this has changed dramatically.  There has been a significant cooling round the idea of establishing intermediaries.  As Finlay notes in his blog post, ‘the focus on savings has thus shifted. Any savings that we as a community might make now come from lowering, or controlling, the total cost of publication, rather than directly from removing administrative burden as was thought a little over a year ago at the commencement of the project.

This ultimately changed the overall focus of our project, and we’ve since moved towards looking at off-setting, in tandem with material emerging from the Jisc.  We have a brief report in progress on the key issues with off-setting at the moment, and the main models available in the UK.

 Back to the everyday functions of processing APCs, and we’re largely dealing with invoice payments.  We released a FAQs for Publishers guide which gave a few of the common problems faced by institutions paying APCs.  Encouraging use of a generic email address, for example, has also been included in a more recent Jisc guide of the Top Ten Tips for Implementing Open Access, although more from the perspective of communications with researchers.  

Our University of Bristol colleagues have been putting together an ‘Ultimate Prepayment Account’ wish list, based on their experiences with the APC deals they’ve encountered to date.  Their key issues are around who can access and authorise prepay accounts,  how to monitor and report on spend, and a focus on the authors’ experience with the account.

 The next few months will see us hold a workshop on Good Practice for APC Payment Management (18^th November at the EngineShed in Bristol). Join us there to discuss payment practices from institutional, publisher and third party perspectives.  We also plan to release some of the work we’ve undertaken around streamlining financial reporting and on the wider market effects of prepayment deals.

​