Written and contributed by Rich Kaczmarek
If you go back to Part 1, the very first sentence you read was:
You can’t beat a spreadsheet for turning big chunks of data into actionableinformation.
The MID function is categorized under Excel TEXT functions Functions List of the most important Excel functions for financial analysts. This cheat sheet covers 100s of functions that are critical to know as an Excel analyst. The function will return a specified number of characters from the middle of a given text string. The RTD function can be used inside user-defined functions (UDFs) in Microsoft Visual Basic for Applications. These then become wrappers for the RTD function. This approach can be useful in shielding users from the details of the RTD server name and ProgID, and in enabling them to focus only on the data that is important to them. Functions: All Excel worksheet functions, with a few exceptions. For more information, see Differences in worksheet functions in this article. Dates: The Windows and Macintosh date systems. Excel tables: Excel table data, column headers, calculated columns, total rows, structured references, and styles. The RTD Function belongs to the Excel Lookup and Reference category Functions List of the most important Excel functions for financial analysts. This cheat sheet covers 100s of functions that are critical to know as an Excel analyst. The function will retrieve real time data from a program that supports COM automation. In regards to your inquiry about DDE, we now support the Microsoft Excel function RTD(Real Time Data) which works in the same way. I could not attach the PDF file. I copied and pasted it. Contact TOS support for a copy. Thinkorswim RTD on thinkorswim thinkorswim has the ability to use the Mircosoft Excel function RTD (Real Time Data.).
Over the course of this article, we’ve reviewed different mechanisms which allow ThinkOrSwim to communicate with Excel, written equations that use RTD, learned a bit about how to manipulate data with Excel, and uncovered a raft (maybe even a boatload) of live and streaming information we could be getting in Excel via ThinkOrSwim.
So, really, the goal of “Connecting ThinkOrSwim to Excel” was accomplished in Part 3. Well done! Pats on the back all around!
In this series conclusion, we’re going to clean up the LockeRTD spreadsheet, then turn a chunk of live, streaming ThinkOrSwim data into actionable information.
(Before we begin, bring up ThinkOrSwim and the LockeRTD Excel spreadsheet you saved last week.)
Except for the updated values, here’s how we last left LockeRTD:
It’s ok but check out all the inapplicable option information for RUT in row 2. If we’re going to simply copy & paste the formulas, letting Excel do all the heavy lifting, let’s put a little intelligence into the equation so it only displays options data if the symbol entered in column B is actually an option.
Yes, it’s faster and easier to make it look good by simply deleting the equations from H2 to N2 but that reduces flexibility. What does that mean? It means you couldn’t just add a symbol into column B, willy-nilly, and let Excel do the work. You’d have to copy the equations in columns H through N only when you enter an option in column B, then delete them when you change the symbol from an option to a stock.
Ugh. Too much work. So let’s think like a contortionist and insert some flexibility into this act.
Pop Quiz: What’s the big difference in the symbol that separates options from everything else?
If you said, “the dot at the first position of the symbol,” good thinking! That means if there’s a dot in the leftmost position, we want the option data and if there’s not a dot in the leftmost position, we don’t want RTD to do anything.
The first offender is cell H2, so let’s change the equation in H2 from this:
=RTD(“TOS.RTD”,H$1,UPPER($B2))
To this:
=IF(LEFT($B2,1) = “.”,RTD(“TOS.RTD”,H$1,UPPER($B2)), “”)
The new part of the equation is in red, so you can see what was added.
Breaking it down:
- IF is a logical function that will return the first value if the condition is true and the second value if the condition is false. Since it’s a function, like RTD or UPPER, notice that what follows – the parameters – is enclosed in parenthesis.
- LEFT($B2,1) is the LEFT function. The LEFT function returns some number of leftmost characters from the beginning of some text. In this case, we want LEFT to return 1 character starting at the leftmost character of the contents in cell B2. (Remember, we used $B to “pin” the column to B.)
- IF(LEFT($B2,1) = “.”, is the LEFT function inside the IF function. What it means is we want to look at the leftmost 1 character in cell B2 (it’s the R from RUT) and if it’s a dot (.), then do what follows the 1st comma OTHERWISE do what follows the 2nd
- What follows the 1st comma is the RTD equation we’ve been using. You already know how it works but, if you don’t remember, go back to Part 2.
- What follows the 2nd comma is “”. Those two double straight quotes (not curly “smart quotes” because, of course, they’re different ASCII characters) next to each other are interpreted as “nothing – do nothing at all.”
Bottom Line: The equation can be translated to “If the first character in cell B2 is a dot, it must be an option so go get its EXPIRATION from ThinkOrSwim…otherwise, do nothing.”
After you modify the equation in H2 and press <Enter>, you should see:
H2 is blank, which is exactly what we wanted. Copy & paste H2 over H2 through N3 (following the instructions in Part 3) and you’ll see:
Much easier to read!
Now copy row 3 to rows 4 and 5 (revisit Part 3, More Option Data if you don’t remember how) for the next step.
Let’s build a Butterfly!
I’m already using a RUT APRIL 1000 Put so we’ll add a 950 as the center and a 900 as the lower leg. I could certainly return to TOS to copy & paste the option code into column B but, since the symbol for 1000 is already there, it’s easier to simply edit B4 and change the 1000to 950, then change the 1000 to 900 in B5:
Add column headers and quantities so column C and D looks like this:
Enter this equation in cell D2 to find the total deltas for the quantity of individual options:
=IF(AND(LEFT($B2,1)=”.”,C2<>0),C2*J2*100, “”)
Hey! That’s similar to what we did in H2 to N5, isn’t it? Let’s break it down:
- The AND function will result in TRUE if all the parameters passed to it are true. In this case:
- LEFT($B2,1)= “.” will return TRUE if the first character of the symbol (column B) is a dot (in other words, column B is an option)
- C2<>0 will return TRUE if there’s a number other than 0 in column C (we named that column Qty)
- If you follow this logic, the AND function will return FALSE in row 2 because RUT isn’t an option and, incidentally, there isn’t a quantity in column C. It will, however, return TRUE for the next 3 rows because they’re options and there is some number other than 0 for quantity.
- C2*J2*100 multiplies the quantity times the option delta, for the total number of deltas ThinkOrSwim is holding for that option, then multiplies the result by 100 so deltas are consistent with our other option modeling tools (like OptionVue or ONE).
- “” is, of course, nothing (as in, “do nothing if it isn’t an option”)
The equation can be translated to, “If there’s an option in column B of this row, and there’s some quantity, multiply the quantity times the current delta supplied by ThinkOrSwim times 100 (qty*delta*100) …otherwise, do nothing.”
Copy cell D2 into cells D3, D4 and D5. Your spreadsheet should look like this:
Let’s add the total number of deltas ThinkOrSwim has for the Butterfly by placing this equation into D6:
=SUM(D2:D5)
Add a top border to the cell to make it look nice (go to Excel’s Home | Font and look for Border):
Now it’s easy to see the current price for RUT and how it’s changed since the previous trading day. Ditto for all our Butterfly options, plus other interesting stuff (like the greeks), including the total deltas our Butterfly is holding (according to ThinkOrSwim).
Many trades in the Locke In Your Success programs depend on taking action based on the greeks, like when delta is too much or ratios exceed their proscribed limit. You can use the powerful tools within Excel (like conditionally formatting the delta sum to be red if it’s over 100) to alert you when the ThinkOrSwim greeks are telling you it’s time to look at adjusting.
The example here was simple, summing Butterfly deltas, but imagine the possibilities:
- Compare synthetic pricing for verticals
- Choose which of many covered writes best fits your goals
- Decide which pattern of adjustments most keep your greeks under control
- Summarize and track multiple existing trades all on one screen
In general, you can quickly summarize a mountain of live, streaming data so you’re able to prove or disprove theories, and move faster while making better decisions. The possibilities are limited only by your imagination.
You’ve started down the trail of how to turn big chunks of data into actionable information. Enjoy your journey!
(If you’d like, you can download the complete instructions and completed LockeRTD Excel file from https://members.lockeinyoursuccess.com/resources/. It has everything you did plus a little more.)
Written and contributed by Rich Kaczmarek
Excel Quotes Add-In
Excel Quotes Add-In has been broken recently due to changes in Microsoft's Quote Web Services. Please see this post for more information.
On 6/30/2019, I have revere-engineered the new Quote Web Services and updated my Excel Quotes Add-In with the changes. Please download the most recent version and copy over the previously saved QuotesAddIn.xll file on your computer.
On 6/30/2019, I have revere-engineered the new Quote Web Services and updated my Excel Quotes Add-In with the changes. Please download the most recent version and copy over the previously saved QuotesAddIn.xll file on your computer.
A long time ago, my team at Microsoft built an Excel plug-in to get stock quotes from MSN Money. It looked something like this: MSNStockQuote('MSFT','Last').
Unfortunately Microsoft hasn't supported this plug-in for years :(
While building MSMoneyQuotes, I reconginzed Excel was still lacking a decent Add-In to get quotes. So I built one!
The Excel Add-In I developed uses Microsoft's own quote web services to retrieve quotes. Therefore it supports getting quotes for any securities that work on the MSN Money website and/or the Windows 10 Money app. I also added the ability to get cryptocurrency quotes using the CryptoCompare API.
I have made it freely available from my website. However, I do accept donations which help offset the many hours put towards building, improving and supporting this software.
Unfortunately Microsoft hasn't supported this plug-in for years :(
While building MSMoneyQuotes, I reconginzed Excel was still lacking a decent Add-In to get quotes. So I built one!
The Excel Add-In I developed uses Microsoft's own quote web services to retrieve quotes. Therefore it supports getting quotes for any securities that work on the MSN Money website and/or the Windows 10 Money app. I also added the ability to get cryptocurrency quotes using the CryptoCompare API.
I have made it freely available from my website. However, I do accept donations which help offset the many hours put towards building, improving and supporting this software.
![Excel Excel](/uploads/1/1/9/3/119372906/489231137.jpg)
Prerequisites
- Works with Desktop Excel 2010, 2013, 2016
- Supports 32 bit and 64 bit versions of Excel
- Built as an 'Excel Add-In', which is a special DLL with an XLL file extension
- Adds a new '=QUOTE()' function to make getting quotes easy
- Adds a new 'Quotes' tab to make finding and updating quotes easy
- Supports updating quotes for all securities supported by the MSN Money website
- Supports updating cryptocurrency quotes supported by the CryptoCompare API
Download and Install
- First determine which bit version of Excel you have installed using What version of Office am I using?
- Download the appropriate 32-bit version or 64-bit version of QuotesAddIn.xll and save on your computer (e.g. C:GaierSoftwareQuotesAddIn.xll)
- Add/Enable the Add-In in Excel using the 'Browse' option. Add or remove add-ins in Excel
- Click File/Options then click 'Go' next to Manage Excel Add-ins
- Click 'Browse...', navigate to the folder where you saved the QuotesAddIn.xll and select it
Dowload the 32 bit version - QuotesAddIn.xll
Dowload the 64 bit version - QuotesAddIn64.xll
Example Excel File
After you've downloaded and installed the Add-In, please see my sample Excel file to see how this works!
QuotesAddInExample.xlsx
QuotesAddInExample.xlsx
Basic Usage
The Excel Quote Add-In works by providing a new Excel function (aka formula) called =QUOTE(). It can be used to retrieve quote data like last, open, day high/low, year high/low, etc.
The function accepts two parameters:
The function accepts two parameters:
- Financial Instrument: This the special symbol MSN Money (and Morningstar) assigns to the security to uniquely identify it. Because MSN Money supports many international markets the symbol 'GOOG', for example, is not enough to uniquly identify Google. Instead, MSN Money uses the following Finanical Instrument to indentify Google: '126.1.GOOG.NAS'. Financial Instrument Search Page
- Quote Field: This indicates which quote field you'd like to retreive. See the 'Supported Quote Fields' for the complete list.
Example Usage:
How to determine the appropriate 'Financial Instrument' symbol
I've build it a basic search pane that makes finding a MSN Money supported financial instrument easy. Go to the custom 'Quotes' tab and click the 'Search' button.
The 'Quotes' tab has a 'Search', 'Update Now' and 'Start Auto Update' buttons:
In the search pane, you can search by symbol or company name and hopefully find what you're looking for. Just start typing in the search field and the listbox will automatically load relevant results. This uses the same search service used by the MSN Money website, so I can't control the search behavior and/or results.
Once you find what you're looking for, you have the option to:
The 'Quotes' tab has a 'Search', 'Update Now' and 'Start Auto Update' buttons:
In the search pane, you can search by symbol or company name and hopefully find what you're looking for. Just start typing in the search field and the listbox will automatically load relevant results. This uses the same search service used by the MSN Money website, so I can't control the search behavior and/or results.
Once you find what you're looking for, you have the option to:
- Select the Quote Field you'd like to get a value for and click 'Insert Forumla' to insert the applicable =QUOTE() forumla
- Select 'Financial Instrument' to insert the raw financial instrument into the currently active call. This does not insert a formula. This may be useful if you're using the second approach [e.g. =QUOTE(A1, 'Last')] where you're referencing another cell to get the financial instrument.
Cryptocurrency Support
Rtd In Excel
Cryptocurrencies are a hot topic recently, so I also added support for those. I leverage the CryptoCompare API to get quotes.
If you know the cryptocurrnecy pair, you can simply type the formula directly into the cell. For example: Or you can use the 'Cryptocurrency' tab in the search pane. Same as the other tab, start typing in the search field and relevant results will show up.
If you know the cryptocurrnecy pair, you can simply type the formula directly into the cell. For example: Or you can use the 'Cryptocurrency' tab in the search pane. Same as the other tab, start typing in the search field and relevant results will show up.
Excel For Mac Rtd Functions Think Or Swim Club
Supported Quote Fields
Here's a list of Quote Fields and which security types they're generally applicable to. Not all fields work for every security.![Excel For Mac Rtd Functions Think Or Swim Excel For Mac Rtd Functions Think Or Swim](https://lockeinyoursuccess.com/wp-content/uploads/2016/02/Blog-10_CS_RTD-2.7.jpg)
Quote Field | Indexes | Stocks | ETFs | Funds | Currency Rates | Cryptocurrencies |
---|---|---|---|---|---|---|
Symbol | X | X | X | X | X | X |
Name | X | X | X | X | ||
Currency | X | X | X | X | ||
Last | X | X | X | X | X | X |
Change | X | X | X | X | X | X |
Date | X | X | X | X | X | |
Type | X | X | ||||
Exchange | X | X | X | |||
Previous Close | X | X | X | |||
Day High | X | X | X | X | ||
Day Low | X | X | X | X | ||
Open | X | X | X | X | ||
PE | X | |||||
EPS | X | |||||
Volume | X | X | X | X | ||
Avg Volume | X | X | X | |||
Year High | X | X | X | X | ||
Year High | X | X | X | X | ||
Market Cap | X | X | ||||
Ask | X | X | X | |||
Beta | X | |||||
Bid | X | X | X | |||
Dividend Yield | X | X | ||||
Industry | X |
How to update quotes
Once the cell(s) are setup with the proper =QUOTE() forumla, you can go to the custom 'Quotes' tab and click the 'Update Now' button. This button forces a 're-calculation' of all =QUOTE() forumulas, which invokes my add-in to retrieve quotes from the relevant web service APIs.
To automatically update quotes every 1 minute, click the 'Start Auto Update' button. Essentially this starts a timer to automatically run the 'Update Now' logic every minute.
Click 'Stop Auto Update' to stop the auto update timer.
Note: Quotes are cached for 30 seconds, so the if you try to click the 'Update Now' button more often than that nothing will happen.
To automatically update quotes every 1 minute, click the 'Start Auto Update' button. Essentially this starts a timer to automatically run the 'Update Now' logic every minute.
Click 'Stop Auto Update' to stop the auto update timer.
Note: Quotes are cached for 30 seconds, so the if you try to click the 'Update Now' button more often than that nothing will happen.
Help and Support
I've made this free to avoid creating a sense of obligation that I'll provide support. I don't guarentee it will work in every scenario or that I'll provide support when it doesn't work. With that said, if you do have a problem that you think I can help shoot me an email and I'll reply if I can.
Please recognized I am no longer a Microsoft employee and cannot fix quote issues on MSN Money. Nor can I address outages that the MSN Money quote web service may have. My Excel Quote Add-In is not responsible for the actual quote values returned by MSN Money, so please don't email asking me to fix such issues. Same with the cryptocurrency quotes from the CryptoCompare API.
I can be reached at my support email address: [email protected]
Please recognized I am no longer a Microsoft employee and cannot fix quote issues on MSN Money. Nor can I address outages that the MSN Money quote web service may have. My Excel Quote Add-In is not responsible for the actual quote values returned by MSN Money, so please don't email asking me to fix such issues. Same with the cryptocurrency quotes from the CryptoCompare API.
I can be reached at my support email address: [email protected]
Release History
- Version 1.0 - 01/31/2018 - Initial Release
- Version 1.1 - 02/22/2018 - Added Auto Quotes Update
- Version 1.2 - 06/30/2019 - Updated to consume the new Microsoft Quote Services