Install OTC Dealer

Report System Problems

User Manuals


Latest OTC News

OTC Statistics

Make a Suggestion


OTC Dealer App

Pink Link


Data Feed




Excel Linkage for Spreadsheet Driven Quotes

AutoQuote makes quoting ADR, Foreign Ordinary and Convertible securities easy. Market maker quote prices generated in an Excel spreadsheet are automatically sent to the OTC Dealer and published on Pink Quote.

  • Use the power of Excel to create real-time quotes that automatically adjust for underlying share prices and currency changes as well as incorporate settlement and arbitrage costs.
  • Full control over the securities for which you wish to have AutoQuote enabled.
  • Easy to set up using a special Excel template provided by Pink OTC Markets. Simply link an existing or new spreadsheet that is calculating quote prices to the AutoQuote template.
  • AutoQuote does not require installing any other software on your PC as it works with existing technology included in Windows XP.
  • Tested on Windows XP with Excel 2000, 2003 and 2007. AutoQuote may work on other systems but Pink OTC Markets only offers support on the systems mentioned above.
  • You must be entitled by Pink OTC Markets to use AutoQuote. Please contact the Trading Services Department at (800) LIST-OTC to inquire about enabling AutoQuote for your OTC Dealer login.

Instructions on how to setup and use AutoQuote are below. The spreadsheet template is included with the OTC Dealer install in your C:\\Program Files\Dealer 4.x folder. You can also download the template below by clicking on the AutoQuote icon and saving the file on your PC.

Setting Up AutoQuoting in Excel

The PinkSheetsAutoQuote worksheet contains four columns labeled Symbol, Bid Price, Ask Price and Enabled. It also displays two buttons, Start and Stop. You must fill in the columns with appropriate links to your Bloomberg terminal, or, alternatively, have them point to a second workbook where calculations are made. The four columns that must be filled in are:

  • Symbol - the symbol of the security
  • Bid Price - usually a formula you create to calculate your bid or 0 (zero) for an unpriced bid
  • Ask Price - usually a formula you create to calculate your ask or 0 (zero) for an unpriced ask
  • Enabled - This column should be populated with "Y", "YO" or "N", indicating whether this quote should be automatically submitted to the OTC Dealer or not. "YO" is identical to "Y" but will overwrite the lock/cross check on quote submission; i.e., it will allow you to lock or cross the market when AutoQuoting.

The final step is to define the range where your data is contained. The range should be four columns wide and contain at least as many lines as the number of securities you wish to AutoQuote. The range comes preset to include up to 30 lines of data and will auto adjust if it detects data below the defined range. A dialog box indicating "Extra Data Found" will appear and by clicking "Yes" the range will readjust..

To manually adjust the range, select the Insert - Name - Define command in Excel. Locate and click "PSAQ" in the list under "Names in workbook". You can then edit the range where your data is contained in the "Refers to" section. Remember to exclude the top line where the heading is. In the example above, we selected columns A through D and lines 2 through 6, omitting the top line where the captions are. Click "OK" to save your range.

The range should always be exactly four columns wide. It does not matter if your defined range is longer than your actual data, as all empty rows will be ignored. Extra rows in your range can be useful if you add more securities for AutoQuoting later without making changes to your range definition. Remember to always check that newly added securities are within your defined range. This can be checked under Insert - Name - Define.

Using AutoQuoting with the OTC Dealer Application

You are now ready to start AutoQuoting in OTC Dealer. Remember, your OTC Dealer account must be entitled for AutoQuote. If you have just been entitled, you may have to log out of OTC Dealer and login again before AutoQuote will activate.

To begin, open your Excel spreadsheet entitled AutoQuoteTemplate and click the Start button.

The symbol column in OTC Dealer, under My Quotes will turn green for every security you have included in your AutoQuote list and where the Enabled column is either "Y" or "YO." To temporarily stop AutoQuoting in one particular security, change the value in the Enabled column to "N". If a quote update fails with AutoQuote, the symbol column will turn red.

You are not allowed to AutoQuote a security that does not appear in your My Quotes window. If you want to AutoQuote a security, your firm is quoting, but it does not show in your My Quotes window, remove the quote and then add it again . It should now be in your account and displayed in your My Quotes window.

If you want to add securities to your AutoQuote list during the day, add the security in your AutoQuote Excel spreadsheet, click the Stop button to briefly halt AutoQuote, then click the Start button to resume.

AutoQuote will always submit the minimum size required for the price.

To stop AutoQuoting completely, click the Stop button in the Excel spreadsheet. Your My Quotes window will turn white when AutoQuoting has been disabled.

© Copyright 2011, OTC Markets Group Inc. All Rights Reserved.
Please read our Trademarks, and Privacy Statements.