MethodologyContact usLogin
This user guide is accurate for version 1.3.934 of the Excel Add-in. Please refer to the release notes section for User Guides for other versions.
Thank you for choosing to use Fastmarkets Excel Add-in! As part of Fastmarkets platform strategy, we will provide customers with richer, more flexible mechanisms to securely access our data. This tool will allow you to pull our pricing data directly into Excel, thereby enabling you to embed our prices into your workflow.
This user guide will help you get the most out of Fastmarkets Excel Add-in to support your business needs. Please refer to the accompanying Technical Guide to install and log in for the first time to the Fastmarkets Excel Add-in.
Example templates of all of the functions included in this website and how to work with them are available on the Excel Add-In Support page for your data licence.
If after reading these instructions you have further questions about how to use Fastmarkets Excel Add-in, please contact your the Customer Success Team.
– Europe, Middle East and Africa: +44 (0)20 3855 5581 – Asia: +65 3163 3458 – Americas: +1 708 329 2641 – Customer Success Email: customersuccess@fastmarkets.com – Client Services Email: client.services@fastmarkets.com
For more information on Fastmarkets’ products and services, please use the following links or click on the Fastmarkets Excel Add-In Ribbon buttons:
Additional Links:
Version 1.3.934 provides the following new features and functionality. For ALL customers who are below version 1.3.186 this is a mandatory upgrade which cannot be postponed. If you are upgrading from a previous version of Fastmarkets Excel Add-In you will still be able to access everything you had previously.
If you are on version 1.3.186 or above it will be possible to postpone the update.
This release includes new functionality and features to enable the option to filter out preliminary prices when exporting from the Dashboard to Excel Add-in with :
Fastmarkets’ Excel Add-in enables you to easily obtain rich data around the commodities you follow. The table below summarizes the functions and pricing data available to you in this version. Please note the user of the word “symbol” in the table and through this User Guide. Each Fastmarkets price has a symbol – an alphanumeric code unique to that price.
v1.3816
New ‘WeightAverageofTrades’ Price Calculation Type introduced for Cobalt symbols MB-CO-0004 and MB-CO-0005
v1.3.728
Currency and unit of measure conversion available
Currency and unit-of-measure conversion available.
Currency and Unit of Measure conversion available
Please note – it is not recommended to nest any Fastmarkets functions inside another Fastmarkets function.
Let’s explore the functions individually to see how you can make Fastmarkets’ Excel Add-in work best for you.
The GetInstruments( ) function enables you to view a list of all symbols and their corresponding reference data to which you’re entitled in your data package. The table below provides a summary of this function.
Get the list of prices and their symbols1. Select a cell where you want the prices to be displayed (for example, A2)
2. Click on the “function wizard” icon (fx). Choose the “Fastmarkets” category, select the “GetInstruments” function and click OK.3. To display the full list of symbols and prices available for the data package you have chosen, leave the parameters blank and click OK. If you wish to filter the list so that you only see, say, aluminium symbols and prices, type “aluminium” in the “Filter” field.The list of all available prices will be inserted in the sheet (see following table). The first column contains the symbol unique to each price. The remaining columns contain the corresponding reference data (currency, unit of measure, product, location, incoterm, commodity, price type, frequency, source, status and description). Please review the Appendix for a complete description of what each field displays.
The GetPriceCalculationTypes( ) function will identify the price calculation types that are available for each symbol. Use this function as a starting point to determine the type of data you wish to return – whether it is “Actual” for assessed physical prices or an average price for a particular frequency – “WeeklyAverage”, “MonthlyAverage”, “QuarterlyAverage” or “YearlyAverage”.
This release introduces a new ‘Weighted Average of Trades’ Price Calculation Type required for Cobalt instruments. Please note this Price Calculation Type is only available for the following instruments:
• MB-CO-0004 – Cobalt alloy grade, in-whs Rotterdam, $/lb
• MB-CO-0005 – Cobalt standard grade, in-whs Rotterdam, $/lb
NOTE: London Metal Exchange averages are not yet available through Fastmarkets’ Excel Add-in.
The parameters will enable you to easily populate a table to compare actual physical pricing and corresponding averages for a symbol across the same row or column.
The averages price calculation methodology for Fastmarkets US prices changed as of January 2021. You can find out more here: www.fastmarkets.com/support/averages-changes-faq
The table below provides a summary of this function.
Get a list of Price Calculation Types for your specified symbol(s)
1. Select a cell where you want the prices to be displayed (for example, A2).
2. Click on the “function wizard” icon (fx). Choose the “Fastmarkets” category, select the “GetPriceCalculationTypes” function and click OK.3. Enter the symbol. In this example we’re using MB-AL-0004
4. For the “VerticalHorizontalOrder” field, enter “V” if you want the Price Calculation Types to appear vertically or “H” if you want the Price Calculation Types to appear horizontally. This field is optional, if you leave it blank the values will return horizontally by default. In this example we will leave it blank. Then click OK.The Price Calculation Types will return as in the screenshot below.
The screenshot below shows how the Price Calculation Types return if the “VerticalHorizontalOrder” field is set to “V”.
The GetInstrumentFields( ) function can be used to identify the fields that are available for each symbol and price calculation type. Remember, each Fastmarkets price has a symbol – an alphanumeric code unique to that price.
Some fields provide reference data, such as currency and unit of measure. and other pivotal data fields including:
A full description for all fields can be found in the Appendix.
Get the data fields available for a particular price
1. Select the cell where you want the data fields to be inserted (for example, A2).
2. Click on the “function wizard” icon (fx), choose the “Fastmarkets” category, select the “GetInstrumentFields” function and click OK.3. Enter the symbol. For this example, we’re using MB-IRO-0004.
4. Enter the PriceCalculationType. This field is optional; if left blank, it will return fields for “Actual” (actual physical prices). If you know the PriceCalculationType, enter it in speech marks (for example, “MonthlyAverage”). You can also cell reference it if you have used the GetPriceCalculationType function.
5. For the “VerticalHorizontalOrder” field, enter “V” if you want the data fields to appear vertically or “H” if you want the data fields to appear horizontally. (This field is optional; if left blank, it will return instrument fields horizontally.) Then click OK.The data fields available for the symbol will appear in the spreadsheet as shown in the following screenshot if you select “V” for Vertical order.
The screenshot below shows how the data fields would appear horizontally if “H” were typed into the “VerticalHorizontalOrder” field instead of “V” as referenced in Step 4. (Note: Several fields are cut off for the viewability of this screenshot.)
The GetLatestPrice( ) function enables you to view the latest price information for your chosen symbols and their price calculation types.
From this version it is now possible to convert price data to the currency and/or unit of measure specified through the TargetCurrency and TargetUnitOfMeasure parameters. These are optional parameters and if left blank price data will return in the assessed currency and unit of measure. Please refer to the Currency & Unit of Measure Conversion section for an example.
View the latest “Actual” – physical price data for one or more symbols
1. Copy the symbols for which you want to get the latest prices and paste them into a new sheet. For this example, we’ve chosen five aluminium premiums.
2. Fill in the column headers with the required price fields. Fastmarkets’ Excel Add-in supports the fields outlined in the table in the Appendix. For the example shown in the following screenshot, we’ve chosen Description, Low, High, Mid and AssessmentDate for our field.3. Select the cell where you want the first price to be inserted (for example, B2).4. Click on the “function wizard” icon (fx). Choose the “Fastmarkets” category and select the “GetLatestPrice” function5. Fill in the following parameters as shown:Symbol: Type the cell reference where the first price symbol has been inserted and apply the dollar sign ($) to the column (for this example, type $A2, since that’s where symbol MB-AL-0231 has been inserted). Applying the $ will allow you to copy the formula over to other cells to fill out the table.PriceCalculationType: This parameter enables you to determine the type of data to return for the symbol (physical pricing or averages). The parameter is optional; if left blank, it will return “Actual” for physical pricing. In this example, we will leave it blank.Field: Type the cell reference where the first price field has been inserted and apply the dollar sign ($) to the row (for this example, type B$1, since that’s where “Description” has been inserted). Applying the $ will allow you to copy the formula over to other cells.TargetCurrency: This is an optional parameter, if left blank it will return the assessed currency for the price. For this example, we will leave it blank.TargetUnitOfMeasure: This is an optional parameter, if left blank it will return the assessed unit of measure for the price. For this example, we will leave it blank.
6. Click OKThe description of the instrument has now been inserted into cell B2 (see screenshot below).7. Copy the formula over to get the information for all of the data fields you’re seeking (see following screenshot). You can either grab the little square in the corner of the cell with the function in it and drag it across and down to highlight your entire table; or you can copy and paste the cell with the function in it into your entire table. Because you applied the dollar sign ($) in the formula, all retrieved data will point to one of the price symbols in Column A and to one of the fields in Row 1.Refreshing the latest pricing dataDepending on your Excel settings, your pricing data may update automatically. If your Excel is set to update calculations automatically, you may simply refresh the data. To change this setting, go to Options -> Formulas -> Calculation options and change Workbook Calculation from Automatic to Manual. If Workbook Calculation is set to manual, the keystrokes are as follows:
– F2 and Enter to update a specific cell– Ctrl + Alt + F9 to update the entire spreadsheet
View the latest monthly average price data for one or more symbolsFastmarkets produces independent, fair and representative price assessments and indexes of ferrous, non-ferrous and scrap metal prices on a daily, bi-weekly, weekly, bi-monthly or monthly basis. Fastmarkets calculates and publishes monthly averages based on these independent, proprietary assessments which can be easily retrieved using the Excel Add-in. For details on how these monthly averages are calculated, please review our methodology documentation at www.fastmarkets.com/methodology
1. Copy the symbols for which you want to get the latest monthly average prices and paste them into a new sheet. For this example, we’ve chosen the same five aluminium premiums from the example above.
2. Fill in the column headers with the required price fields. Fastmarkets’ Excel Add-in supports the fields outlined in the table in the Appendix for averages AND actual physical prices. For this example, we’ve chosen Description, Period, Low, High, Mid and AssessmentDate. Period is a useful new field that allows you to view the week date range, month/year or year for the average data you specify.3. Select the cell where you want the first price to be inserted (for example, B2).
4. Click on the “function wizard” icon (fx). Choose the “Fastmarkets” category and select the “GetLatestPrice” function.Fill in the parameters as shown below:
Symbol: Type the cell reference where the first price symbol has been inserted and apply the dollar sign ($) to the column (for this example, type $A2, since that’s where symbol MB-AL-0231 has been inserted). Applying the $ will allow you to copy the formula over to other cells to fill out the table.
PriceCalculationType: To retrieve averages, this field needs to have a value entered. If you are unsure of what to enter, please refer to the earlier section on the GetPriceCalculationType function. For this example, enter “MonthlyAverage”.
Field: Type the cell reference where the first price field has been inserted and apply the dollar sign ($) to the row (for this example, type B$1, since that’s where “Description” has been inserted). Applying the $ will allow you to copy the formula over to other cells.
TargetCurrency: In this example,it is left blank to return the assessed currency for the price.
TargetUnitOfMeasure: In this example, it is left blank to return the assessed unit of measure for the price.
6. Click OK.The description of the instrument has now been inserted into Cell B2, as shown below.
7. Copy the formula over to get the information for all of the data fields you’re seeking (see following screenshot). You can either grab the little square in the corner of the cell with the function in it and drag it across and down to highlight your entire table; or you can copy and paste the cell with the function in it into your entire table. Because you applied the dollar sign ($) in the formula, all retrieved data will point to one of the price symbols in Column A and to one of the fields in Row 1.
An important note on monthly averagesWhy am I not able to retrieve monthly average prices for US weekly scrap composites?
The following weekly scrap composite prices are an average of the daily composite prices calculated for the week from Friday to Thursday. Published monthly averages are not available for these prices. The corresponding daily composite price should be used to retrieve monthly average prices. The averages price calculation methodology for Fastmarkets US prices changed as of January 2021. You can find out more here: www.fastmarkets.com/support/averages-changes-faq
The GetPriceHistory( ) function enables you to view historical prices for a symbol. From this version, it is possible to convert price data to the currency and/or unit of measure specified through the TargetCurrency and TargetUnitOfMeasure parameters . These are optional parameters; if left blank, price data will be returned in the assessed currency and unit of measure. Please refer to the Currency & Unit of Measure Conversion section for an example.
The table below explains how the historical prices return with each Fill Setting within the parameter, enabling you to fully customize the time series data you work with.
View the physical price history for a particular symbol
1. Select the cell where you want the price history to be inserted (for example, B4).
2. Click on the “function wizard” icon (fx), choose the “Fastmarkets” category, select the “GetPriceHistory” function and click OK.3. Fill in the Symbol field ONLY. This is the quickest way to get the full physical price history for assessment-only dates. You may fill in the StartDate and EndDate fields if you wish to narrow the price history to a specific range. The date format (mm/dd/yyyy versus dd/mm/yyyy) will be recognized based on your regional date and time settings. For this example, we will use the symbol “MB-STE-0566”.
4. Click OK.The price data history has now been inserted into the sheet.
The GetPriceHistory( ) function also allows you to customize the way the price history is displayed in your spreadsheet. The following three, step-by-step examples show how the FillSetting parameter can be used to customize your returned data. The second and third examples (“CarryForward” and “Null”) are particularly useful if you plan to align, in your spreadsheet, the returned data with your own data or other sources of daily published data.
– “ValueOnly”: returns prices on their assessment dates – “CarryForward”: returns prices for every weekday which are “carried forward” or repeated between assessment dates – “Null”: returns all weekdays but displays prices only on their assessment date, other dates are blank
NOTE: PUBLIC HOLIDAYS ARE NOT EXCLUDED. Fill Settings “Null” and “CarryForward” will return ALL WEEKDAYS. This is important to note if manually calculating an average period and comparing to published monthly average prices when entering “MonthlyAverage”.
“ValueOnly”: Return monthly average prices for assessment dates with specific data fields
2. Click on the “function wizard” icon (fx), choose the “Fastmarkets” category, select the “GetPriceHistory” function and click OK – just as you did before.
3. Fill in the parameters as below:Symbol: For this example, we’ll use the symbol “MB-CU-0002”.PriceCalculationType: “MonthlyAverage”StartDate: “01/01/18” (January 1, 2018)EndDate: “12/28/18” (December 28, 2018)FillSetting: “ValueOnly”VerticalHorizontalOrder: “V”AscendingDescending: “A”TargetCurrency: In this example, it is left blank to return the assessed currency for the price.TargetUnitOfMeasure: In this example it is left blank to return the assessed unit of measure for the price.Field1: “Date”Field2: “Period”Field3: “Mid”Field4: “AssessmentDate”
The following three screenshots show all of the parameters entered into the function arguments as outlined above.
Once you have finished populating the fields, click OK. The historical price table will flow into the sheet according to the specified parameters, as shown in the following screenshot. NOTE: These are average value on their published/assessed dates (holidays are factored in). This is the official Fastmarkets average for the instrument.
“CarryForward”: Return physical assessment prices with specific data fields that are rolled forward on non-assessment dates
3. Fill in the parameters as below:Symbol: For this example, we’ll use the symbol “MB-CU-0002”.PriceCalculationType: This parameter enables you to determine the type of data to return for the symbol (physical pricing or averages). The parameter is optional; if left blank, it will return “Actual” for physical pricing. For this example, we will leave it blank.StartDate: “01/01/18” (January 1, 2018)EndDate: “02/02/18” (February 2, 2018)FillSetting: “CarryForward”VerticalHorizontalOrder: “V”AscendingDescending: “A”TargetCurrency: This is an optional parameter; if left blank, it will return the assessed currency for the price. We will leave it blank in this example.TargetUnitOfMeasure: This is an optional parameter; if left blank, it will return the assessed unit of measure for the price. We will leave it blank in this example.Field1: “Date”Field2: “AssessmentDate”Field3: “Low”Field4: “Mid”Field5: “High”
The following screenshot shows the same example with the FillSetting parameter set to “CarryForward”.
1. Once you have finished populating the fields, click OK. The historical price table will flow into the sheet according to the specified parameters, as shown in the following screenshot. These are physical price assessments for the instrument.
Note the difference between the “Date” column which displays every weekday, and the “AssessmentDate” column where the assessment date is “carried forward” on non-assessment dates, along with the corresponding prices.
Holidays are NOT factored in; if this format is used to calculate the average, please ensure holidays are manually removed from the calculation.
“Null”: Return physical assessment prices with specific data fields filled on their assessment dates but blank on non-assessment dates
3. Fill in the parameters as below:Symbol: For this example, we’ll use the symbol “MB-CU-0002”.PriceCalculationType: This parameter enables you to determine the type of data to return for the symbol (physical pricing or averages). The parameter is optional; if left blank, it will return “Actual” for physical pricing. For this example, we will leave it blank.StartDate: “01/27/18” (January 27, 2018)EndDate: “02/28/18” (February 28, 2018)FillSetting: “Null”VerticalHorizontalOrder: “V”AscendingDescending: “A”TargetCurrency: This is an optional parameter; if left blank, it will return the assessed currency for the price. We will leave it blank in this example.TargetUnitOfMeasure: This is an optional parameter; if left blank, it will return the asessed unit of measure for the price. We will leave it blank in this example.Field1: “Date”Field2: “AssessmentDate”Field3: “Low”Field4: “Mid”Field5: “High”
The below screenshot shows the same example with the FillSetting parameter set to “Null”.
4. Once you have finished populating the fields, click OK. The historical price table will flow into the sheet according to the specified parameters, as shown below.
Historic pricing data: frequently asked questions.
1. What is the longest time series I can download?All historical pricing data is available through Fastmarkets’ Excel Add-in. The length of the history will vary depending on when the price was launched.
2. Can I choose between daily/weekly/monthly average prices?Daily physical pricing (“Actual”) and weekly, monthly and yearly prices are available for all metals instruments from version 1.2.612 and above.
3. How can I see if a price was corrected?In case of a price correction, the function returns the latest available version of the price (the latest corrected value). A correction flag field – “Correction” – is available in Fastmarkets’ Excel Add-In version 1.2.612 and above. An output of “TRUE” denotes a price that has been corrected; an output of “FALSE” denotes a price not requiring a correction.
4. Are non-assessment days excluded/included from the time series?The FillSetting parameter enables you to customize how time series data returns using the GetPriceHistory( ) function. Select from “ValueOnly” for assessment-only dates and prices; “CarryForward” to have the function roll the last available price forward if there is no updated price for a given day; or “Null” to display prices on their assessment dates and empty cells if there is no updated price for a given day.
5. Are holiday calendars taken into account when displaying price history?No. At present, Fastmarkets’ Excel Add-in displays all weekdays (including public holidays) between the start and end dates of the period if the FillSetting parameter is set to “CarryForward” or “Null”. We recommend retrieving price history for Fastmarkets published averages using the Price Calculation Type rather than a manual calculation to ensure holidays are removed. However, we appreciate some customers will want to calculate averages manually, please note to remove public holidays as above. Please refer to Fastmarkets’ methodology for more information on holiday calendars for specific ferrous and non-ferrous market prices and averages: www.fastmarkets.com/methodology.
6. Can I use other Microsoft Excel functions such as =TODAY() or =NOW() to calculate or automate a specific date or number of days/periods in the GetPriceHistory() function? No. It is not recommended to nest or use volatile Microsoft Excel functions such as =TODAY() or =NOW() with Fastmarkets functions. For advice or assistance with this, please contact our Customer Success team: customersuccess@fastmarkets.com
GET PRICE HISTORY NON PRELIMINARY
The GetPriceHistoryNonPreliminary( ) function enables you to view historical prices for a symbol, providing results without preliminary prices when exporting from the Fastmarkets Dashboard.
All parameters & functionality remain the same as the above GetPriceHistory function.
Below are examples where preliminary prices have been hidden (in the widget Settings panel) when exporting from the Dashboard and the results when copied for Excel Add-in:
The GetPrice( ) function enables you to view the price for one or more symbols and price calculation types as of a given date.
Get the price(s) for a specific date
1. Copy and paste the required symbol(s) into a new sheet. For this example, we’re using symbols “MB-FEU-0001″, “MB-FEV-0001,” “MB-FEV-0002” and “MB-FEV-0003.”
2. Insert the date for which you want to display the price(s). For this example, we’re using June 26, 2018.
3 . Fill in the column headers with the required price fields. Fastmarkets’ Excel Add-in supports the fields outlined in the table in the Appendix. For this example, we’re using Low, High, Mid, UnitOfMeasure and AssessmentDate.
4. Select the cell where you want the first price to be inserted (for example, C2).
5. Click on the “function wizard” icon (fx), choose the “Fastmarkets” category and select the “GetPrice” function. Then click OK.
6. Fill in the parameters as below:Symbol: Click on the cell where the first price symbol has been inserted (in this example, A2). Apply the dollar sign ($) sign to the column ($A2). This will allow you to copy the formula to other cells.Field: Click on the cell where the first price field – “Low” – has been inserted (in this example, C1). Apply the dollar sign ($) sign to the row (C$1). This will allow you to copy the formula to other cells.Date: Click on the cell where the date has been inserted (in this example, B2). Apply the dollar sign ($) sign to the row ($B2). This will allow you to copy the formula to other cells.TargetCurrency: This is an optional parameter, if left blank it will return the assessed currency for the price. We will leave it blank in this example.TargetUnitOfMeasure: This is an optional parameter, if left blank it will return the assessed unit of measure for the price. We will leave it blank in this example.
7. Click OK. The low price for the first The low price for the symbol – “MB-FEU-0001” – will appear in cell C2, as shown in the following screenshot.
8. Copy the formula over to get the information for all of the data fields you’re seeking (see following screenshot). You can either grab the little square in the corner of the cell with the function in it and drag it across and down to highlight your entire table; or you can copy and paste the cell with the function in it into your entire table. Because you applied the dollar sign ($) sign in the formula, all retrieved data will point to one of the price symbols in Column A and to one of the fields in Row 1.
NOTE: If you are retrieving monthly average prices for a specific period, it is recommended that you request the last date of the month. Monthly average prices are published and stored historically on the last working day of each month. For example, to retrieve the monthly average for April 2019, enter 04/30/2019 in the date parameter – not 04/01/2019. Entering 04/01/2019 will return the monthly average for March (the latest average value for that date).
The GetAvailableCurrencyConversions( ) function will identify the currency conversions that are available for each symbol for which you choose to convert pricing data. Use this function as a starting point to determine the TargetCurrency input parameter for use in the GetPrice, GetLatestPrice and GetPriceHistory functions above to convert your prices into a different currency.
Get a list of available currency conversions for your specified symbol(s)
2. Click on the “function wizard” icon (fx). Choose the “Fastmarkets” category, select the “GetAvailableCurrencyConversions” function and click OK.
3. In this example we will return the available currency conversions for a list of symbols. (Note: The available currencies may differ, depending on the base currency of the symbols in the list.) Click on the cell where the first price symbol has been inserted (in this example, A2). Apply the dollar sign ($) sign to the column ($A2). This will allow you to copy the formula to other cells.
4. For the “VerticalHorizontalOrder” field, enter “V” if you want the data fields to appear vertically or “H” if you want the data fields to appear horizontally. This field is optional; if left blank, it will return instrument fields horizontally. We will leave it blank for this example. Then click OK.
The available currency conversions for the first symbol will return as in the screenshot below.
5. Copy the formula over to get the available currencies for all of the symbols in the list. You can either grab the little square in the corner of the cell with the function in it and drag it across and down to highlight your entire table; or you can copy and paste the cell with the function in it into your entire table. Because you applied the dollar sign ($) sign in the formula, all retrieved data will point to one of the price symbols in Column A.
All available currency conversions will now be displayed and may differ between the symbols in your list depending on the base currency.
The Fastmarkets Excel Add-in enables you to convert the assessed currency and/or unit of measure for prices so you can compare them on a like-for-like basis.
Our currency conversion uses foreign exchange rates provided by a range of suppliers for current and historical pricing. Daily rates on current pricing are provided by Morningstar and update intraday on an hourly basis until 23:00 UTC. Historical prices are converted using the conversion rate of the day the price was originally assessed, which has been provided by different foreign exchange rate providers.
For further information about how our currency converter works please contact customersuccess@fastmarkets.com.
The following examples provide instructions on how to convert prices in your spreadsheet into the currency and/or unit of measure of your choice using the GetPrice(), GetLatestPrice() and GetPriceHistory() functions. You may wish to use the 2 new functions GetAvailableCurrencyConversions() and GetAvailableUnitofMeasureConversions() explained above to provide the necessary input parameters for your chosen TargetCurrency and TargetUnitOfMeasure.
Note: You may need to adjust the decimal place formatting to the returned results to display all decimal places. Any calculations based on those cells will calculate to the full available number of decimal places regardless of display formatting.
Convert the latest price data for one or more symbols to a different currency and unit of measure using the GetLatestPrice function
This example shows how to convert the different assessed currencies and units of measure for a table of prices into US cents per pound for ease of comparison. (If you only wish to convert either currency or unit of measure the TargetCurrency or TargetUnitOfMeasure parameters can be left blank at Step 5. If left blank the assessed currency or unit of measure will be returned.
Note: You may wish to use the GetAvailableCurrencyConversions() and GetAvailableUnitOfMeasureConversions() to check the available inputs for this example as available conversions for currency and unit of measure will differ depending on the assessed prices in your list.
1. Add the symbols for which you want to get the latest prices and paste them into a new sheet. For this example, we’ve chosen five aluminium premiums.
2. Fill in the column headers with the required price fields. Fastmarkets’ Excel Add-in supports the fields outlined in the table in the Appendix. For the example shown in the following screenshot, we’ve chosen Description, Low, High, Mid, AssessmentDate, Currency and UnitOfMeasure for our fields.
3, Select the cell where you want the first price to be inserted (for example, B2).
4. Click on the “function wizard” icon (fx). Choose the “Fastmarkets” category and select the “GetLatestPrice” function.
5. Fill in the following parameters as shown:Symbol: Type the cell reference where the first price symbol has been inserted and apply the dollar sign ($) to the column (for this example, type $A2, since that’s where symbol MB-AL-0231 has been inserted). Applying the $ will allow you to copy the formula over to other cells to fill out the table.PriceCalculationType: This parameter enables you to determine the type of data to return for the symbol (physical pricing or averages). The parameter is optional; if left blank, it will return “Actual” for physical pricing. In this example, we will leave it blank.Field: Type the cell reference where the first price field has been inserted and apply the dollar sign ($) to the row (for this example, type B$1, since that’s where “Description” has been inserted). Applying the $ will allow you to copy the formula over to other cells.TargetCurrency: If left blank it will return the assessed currency, if a target currency is entered that is what will be displayed. In this example we will enter “USd” for all prices to be converted to US cents. Note: to find the available conversions for your chosen symbols please refer to the GetAvailableCurrencyConversions function section.TargetUnitOfMeasure: If left blank it will return the assessed unit of measure; if a target unit of measure is entered, that is what will be displayed. In this example we will enter “Pound”. Note: To find the available units of measure for your chosen symbols, please refer to the GetAvailableUnitofMeasureConversions() function section.
6. Click OK.
The description of the instrument has now been inserted into cell B2, as shown below.
The prices will return converted into US cents per pound, and the Currency and UnitOfMeasure fields will return USd and Pound respectively.Return converted price history for monthly average prices for assessment dates with default data fields
This example uses the GetPriceHistory() function and shows how to convert the historical monthly average prices from the assessed currency and units of measure (US cents per pound) for one price into US dollars per tonne. It uses the default fields returned by the GetPriceHistory() function, and this example will return historical prices for assessment dates. Please refer to the GetPriceHistory() function section for more information on how to work with specific data fields or to return historical prices for actual physical price assessments instead of averages.
If you only wish to convert either currency, the TargetUnitOfMeasure parameter ay be left blank at Step 3. If you only wish to convert the unit of measure, the TargetCurrency parameter may be left blank at Step 3. If the parameters are left blank, the assessed currency and unit of measure will be returned.
Note: You may wish to use the GetAvailableCurrencyConversions and GetAvailableUnitOfMeasureConversions to check the available inputs for this example as available conversions for currency and unit of measure will differ depending on the assessed prices in your list.
2. Click on the “function wizard” icon (fx), choose the “Fastmarkets” category, select the “GetPriceHistory” function and click OK.
3. Fill in the parameters as below:4. Once you have finished populating the fields, click OK. The historical price table will flow into the sheet according to the specified parameters. As shown below, the Low, Mid and High price fields have been converted into US Dollars per tonne and the Currency and UnitOfMeasure fields are displaying the target currency and unit of measure selected selected.
Convert price(s) for a specific date into a specified currency or unit of measure using the GetPrice() function
This example enables you to view converted prices for multiple symbols and price calculation types as of a given date in a currency and/or unit of measure you choose. The assessed currency for all symbols in this example is US dollars, and the unit of measure is either kilogram or pound. Here, we will convert all of the selected prices into euros per pound.
Please refer to the Get Price History function section for more information on how to work with specific data fields or to return prices for averages for this function.
1. If you only wish to convert the currency, the TargetUnitOfMeasure parameter may be left blank at Step 3. If you only wish to convert the unit of measure, the TargetCurrency parameter may be left blank at Step 3. If the parameters are left blank, the assessed currency and unit of measure will be returned. Note: You may wish to use the GetAvailableCurrencyConversions and GetAvailableUnitOfMeasureConversions to check the available inputs for this example as available conversions for currency and unit of measure will differ depending on the assessed prices in your list.
2. Copy and paste the required symbol(s) into a new sheet. For this example, we’re using symbols “MB-FEU-0001, “MB-FEV-0001,” “MB-FEV-0002” and “MB-FEV-0003.”
3. Insert the date for which you want to display the price(s). For this example, we’re using June 26, 2018.
4. Fill in the column headers with the required price fields. Fastmarkets’ Excel Add-in supports the fields outlined in the table in the Appendix. For this example, we’re using Low, High, Mid, Currency, UnitOfMeasure and AssessmentDate.
5. Select the cell where you want the first price to be inserted (for example, C2).Click on the “function wizard” icon (fx), choose the “Fastmarkets” category and select the “GetPrice” function. Then click OK.
6. Fill in the parameters as below:
Symbol: Click on the cell where the first price symbol has been inserted (in this example, A2). Apply the dollar sign ($) sign to the column ($A2). This will allow you to copy the formula to other cells.
Field: Click on the cell where the first price field – “Low” – has been inserted (in this example, C1). Apply the dollar sign ($) sign to the row (C$1). This will allow you to copy the formula to other cells.
Date: Click on the cell where the date has been inserted (in this example, B2). Apply the dollar sign ($) sign to the row ($B2). This will allow you to copy the formula to other cells.
TargetCurrency: If left blank it will return the assessed currency, if a target currency is entered that is what will be displayed. In this example we will enter “EUR” for all prices to be converted to euros. Note: To find the available conversions for your chosen symbols please refer to the GetAvailableCurrencyConversions() function section.
TargetUnitOfMeasure: If left blank, it will return the assessed unit of measure, if a target unit of measure is entered that is what will be displayed. In this example, we will enter “Pound”. Note: To find the available unit of measure for your chosen symbols please refer to the GetAvailableUnitofMeasureConversions() function section.
7. Click OK.8. The low price for the first symbol – “MB-FEU-0001” – will appear in cell C2, as shown below.
9. Copy the formula over to get the information for all of the data fields you’re seeking (see following screenshot). You can either grab the little square in the corner of the cell with the function in it and drag it across and down to highlight your entire table; or you can copy and paste the cell with the function in it into your entire table. Because you applied the dollar sign ($) sign in the formula, all retrieved data will point to one of the price symbols in Column A and to one of the fields in Row 1.
The prices that are returned will be in euros per pound, and the Currency and UnitOfMeasure fields will return EUR and Pound respectively.
A link to the latest Fastmarkets pricing holiday calendar can be found at the bottom of the methodology section here – www.fastmarkets.com/methodology