Skip to Content
Author's profile photo Former Member

Leveraging on SAP Excel Integration in Maintaining Exchange Rates

It is a standard process for finance users to maintain the exchange rates for foreign currency transactions in SAP. The frequency (i.e. daily, weekly, or monthly) of update for exchange rates can vary per company and it largely depends on the company’s practice.

The practice of most companies is to maintain the foreign currency exchange rates directly in the currency exchange table.

Figure 1.png

Figure 1. Screenshot of Currency Exchange Rates (t-code: S_BCE_68000174)

While it is not too difficult to maintain the exchange rate table for infrequent maintenance (e.g. monthly) and for fewer currency pairs, finance users will request for the maintenance of exchange rates to be uploaded from a file (e.g. excel file) if the frequency and volume increases. Most consultants will suggest developing a customized upload tool for the user.

Little known to most, SAP provides an off-the-shelf solution in maintaining market data in the SAP using Excel Integration Tool. The SAP standard program is RFTBDF_OLE. The program is intended for the SAP Financials – Treasury application for update of market data such as currencies, securities prices, interest rates,  and others. The program is flexible enough that it can be used by finance users to update the exchange rate table using Microsoft excel as upload tool.

So, how difficult is it to upload the using the SAP standard application? It’s fairly simple and easy 2-step process: (1) Preparation of excel template and (2) Uploading to SAP.

1- Prepare the Excel Template


    1. Maintain Exchange Rate entries in the Excel Template based on SAP required format

The formatting and explanation of the fields for the excel template are quite straight forward.


Below is the field description and comments as published in the SAP documentation.

Field

Column Description

Field Length

Required (R) / Empty (E)

Comments

Example Data

1

Class

2

R

Fixed Value: 01 – Exchange Rates

01

2

Key 1

20

R

From Currency

EUR

3

Key 2

20

R

To Currency

USD

4

Cat

15

R

Exchange Rate Types

M

5

Date

8

R

Valid From Date

1/1/2014

6

Time

6

E

Time of Value Data

7

Valuation

20

R

Exchange Rate

1.1876

8

Currncy

20

E

Not applicable

9

“Fr” ratio

7

R

Translation Ratio From

1

10

To factor

7

R

Translation Ratio To

1

11

Addt.

5

E

Not Applicable

12

Status

2

E

Error Status

13

Error message

80

E

Error Message

Below is a screenshot of the excel format of the excel template

Figure 2.png

          b. Ensure to save the workbook in 1997 – 2003 that is it must have an “.xls” as extension.

Figure 3.png

2. Upload the file in SAP

          a. Access Transaction Code: TBEX

          b. In the Market Data Transfer from Spreadsheet Initial Screen, Select the following:

      • Currencies (Market Data)
      • Exchange Rates (Direct Quotation or Indirect Quotation)

Figure 4.png

          c. In the Spreadsheet Parameters, choose the excel template file in the user’s desktop

Figure 5.png

          d. Click on Icon icon 1.png to start the transfer

          e. System will load the file in the spreadsheet tab. Note that the Status and Error will be blank

Figure 6.png

          f. Click on Import Market Data icon 2.png

         

          g. In the spreadsheet tab, the status will update as OK.

You’re done. Users can then verify the exchange rates updated in the system. It’s simple and easy step leveraging SAP excel integration. 

Assigned Tags

      12 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Sridhar Bolisetti
      Sridhar Bolisetti

      Hi Harvey Gocuay,

      Nice document and wonderful explanation.  Thanks for sharing.

      Regards,

      Sridhar

      Author's profile photo Former Member
      Former Member

      good catch and so much for killing spreadsheets.

      Author's profile photo Former Member
      Former Member

      Nice, good tutorial.

      Author's profile photo B.Ranjith Reddy
      B.Ranjith Reddy

      Nice document.

      Author's profile photo Alex Alberto Torres Solari
      Alex Alberto Torres Solari

      Buen Material Felicitaciones

      Author's profile photo Prashant Govind Rane
      Prashant Govind Rane

      Hi,

      Very nice and thanks for sharing!

      B/R

      Prashant Rane

      Author's profile photo Joseph Juanson
      Joseph Juanson

      Simple and easily understandable. Thanks for this!

      Author's profile photo Krzysztof Siwiec
      Krzysztof Siwiec

      Nice work. But still the manual work remains. If one want to get rid of the manual work with currency exchange rates maintenance for European Central Bank (ECB) I can reccomend you the EasyExchangeRates ECB solution offered by BCC. http://www.bcc.com.pl/en/offer/solutions-168/bcc-extra-products/easyexchangerates-automatic-takeover-of-exchange-rates.html

      BCC offers free of charge trial that can be used for three months. Regards.

      Author's profile photo Former Member
      Former Member

      Hi Krzysztof,

      yours is a great product, but anything that can be stored in a directory accessible by an SAP server can be automated via batch process, so there's a way to automate spreadsheet uploads as well as long as the data provider utilizes that format.

      thx,

      gm

      Author's profile photo Mallikarjunr r
      Mallikarjunr r

      Very clear and precious steps to upload ...

      Thanks Harvey

      Regards

      Arjun

      Author's profile photo Former Member
      Former Member

      Hi,

      Nice document. Thanks for sharing your knowledge.

      Regards

      Karthi

      Author's profile photo Former Member
      Former Member

      I like using MarketXLS. It’s great.

      Exchange rates in Excel