Today we released a small but very important new feature: a set of new formulas to convert commonly used financial instrument identifiers, such as a CUSIP or ISIN, to a ticker.
This has been a recurring request ever since we launched Excel Price Feed. Often a user is given a spreadsheet with a bunch of ISIN codes and wants to connect those with our live pricing formulas. The problem is that these formulas require a ticker not an ISIN.
What makes this now possible?
FIGI (Financial Instrument Global Identifier)
What is FIGI you may ask?
Well FIGI is an attempt to produce an open standard for financial identifiers, and part of that effort has produced a service that describes the links between different types of codes.
We have now integrated this into our software so conversion formulas are readily available in Excel:
For example, to convert an ISIN (US4592001014) to a ticker (IBM) this simple Excel formula can be used:
=EPF.ID.ISIN.To.Ticker("US4592001014")
And to convert a CUSIP to a ticker, well that is very simple too:
=EPF.ID.CUSIP.To.Ticker("037833100")
To find out more about Excel Price Feed head over to the website and try it free for 10 days: https://www.excelpricefeed.com/
Just a quick blog post today, looking at market performance for a bunch of stocks so far this year (otherwise known as year to date or YTD).
As a reference point, today (24 March 2022) the S&P500 is at -6.2% and the Nasdaq is at -11% for YTD.
I am using the Excel Price Feed Add-in to download latest price data and start of year prices for a small set of diverse US stocks. I use the live price formula (EPF.Yahoo.Price) in column C together with the historical price formula (EPF.Yahoo.Historic.Close) in column D to retrieve the stock price on the first trading day of the year which was 3rd January.
This produces the following data table (you can see the formula for cell D2 in the formula bar):
Now that we have the current stock price and at the start of the year we can calculate the % change using a simple Excel formula which works out the difference (C2-D2) as a proportion of D2:
Now we can apply this formula to the rest of the table and create a simple bar chart to visualize the performance:
As we can see the tech sector is under-performing, particularly Facebook and Netflix whilst the oil giants Exxon and Chevron are out-performing everything else.
I hope this gives a good introduction to stock analysis using Excel, to find out more about Excel Price Feed head over to the website and try it free for 10 days: https://www.excelpricefeed.com/
In the world of financial markets most people are aware of stock symbols, the short codes that are used to identify a company. These codes, called symbols or tickers usually reflect the name of the company, for example the symbol for Apple is APPL and the symbol for Microsoft is MSFT.
These symbols represent the tradeable stock or equity in the company.
Other financial instruments have other types of identifiers, for example stock options, which we will discuss in this article.
Stock Option symbols follow a standardised format; for example in the first column below, you can see symbols for 4 different Tesla stock options from the Yahoo Finance website:
The symbol consists of 4 parts:
These parts are:
Stock symbol, eg. TSLA
Expiry Date, eg 3 December 2021, which is written as 211203 (year then month then day)
Option type: P (Put) or C (Call)
Strike Price: eg 1095
The Excel Price Feed Add-in includes a simple Excel formula EPF.OptionTicker which is used to construct an option symbol from its component parts. This is very useful when you wish to analyse lots of options in Excel without having to manually create lots of option symbols.
For example, here is the formula being used to construct the symbol for the option above:
=EPF.OptionTicker("TSLA","Call","3 Dec 2021",1095)
We can see below the formula in action in Excel.
The real power of the forumla, however, is realised when you want to analyse multiple options, which you can combine with the EPF.Yahoo.OptionsChain.ExpiryDates formula (column C below).
Here we are looking at Apple put options with a strike of 160.
We have used the symbol formula in column D and then used the Price/Volume/Open interest formulas for columns E,F,G. This provides a nice visualization of option volume and open interest for a specific strike:
Check out Excel Price Feed today to see how it can help you with your stock option trading.
Excel Price Feed has been built to perform well with large spreadsheets and can handle spreadsheets that contain formulas for many financial instruments and data fields. You can quite easily build an S&P500 stock sheet and analyze a bunch of fundamental or live data points for each stock and not encounter any deterioration in performance.
However, if you start pushing the Add-in or Excel itself towards its “limits” or dont’ think too carefully about the format and design of your spreadsheet you may start to encounter some performance issues.
Here are some tips to help you improve the performance of your spreadsheets. Some of the tips are quite specific to using the Add-in, whilst others are more general and can be used on any type of spreadsheet:
Avoid volatile Excel functions
This issue is the probably the most common reason we see users contacting us regarding spreadsheet performance. Often a spreadsheet will use the Excel function TODAY() which is one of the Excel volatile functions. You should try to avoid using any Excel volatile functions, for a full explanation please see our blog post specifically about Excel volatile functions.
Avoid lots of single historical market data formulas
Often users wish to compare the historical performance of stocks. You can do this using the very useful single historical date formulas. I have seen many spreadsheets which use 100s of these single date formulas to return a time series. A much better alternative is to replace these formulas with a single historical data array formula. These formulas can return a time series of historical data with one formula.
If you don’t need to use all the data in the time series, or want to cross reference trading days/holidays for example, you can use the powerful Excel XLOOKUP function. In this case dedicate one tab on your sheet to time series data and reference this data from your main sheet.
Avoid single large spreadsheets
If you find your spreadsheets becoming unwieldy or suffering from performance issues due to the sheer volume of formulas and data you can look to split your single spreadsheet into multiple spreadsheets. This way you will be dealing with smaller volumes of data on each spreadsheet refresh and are less likely to breach data limits.
Often the data returned by the formulas on your spreadsheet does not change very often or does not change at all, especially if you are looking at fundamental or historical market data rather than live market data.
In this case you can replace your formulas with static values, using the “Paste Values” option in Excel. Select all the cells that you want to set to static, copy them to the clipboard, then “paste values” back into your spreadsheet. Using this method you can initially populate your spreadsheet using formulas, and then replace those formulas with static values/text.
This will greatly reduce the number of formulas on your spreadsheet and improve performance.
Summary
I hope these tips help you improve the performance of your spreadsheets. If you have any more tips please leave a comment below.
Tesla is in the headlines, yet again, this time as it’s share price hits $500. The latest jump in price was triggered by the news that Tesla will join the S&P500 in December; it is now by far the most “valuable” car company in the world:
In 2020, Tesla has become the world’s most valuable car manufacturer and blown its sales forecasts out of the water.
Market capitalzation is the most common measure, in the financial press, to gauge how valuable a company is. Market capitalization is simply:
Number of shares outstanding (x) the share price
That is, how much it would cost to buy all the outstanding shares of a company. On this measure, Tesla is certainly the most valuable:
Another measure is Enterprise Value:
Market Capitalization (+) Total Debt (-) Cash & Cash Equivalents
Enterprise Value is the cost of how much it would actually cost you to buy the company, as you would not only need to buy all outstanding shares but also take on all debts of the company. You could offset some (or all) of the debt by the amount of cash & cash equivalents (i.e. liquid assets) that the company owns. Enterprise Value is often used as the theoretical takeover price of a company.
In this case, Tesla is still the most valuable car company but the other car companies are much closer in value:
I think we can safely say that Tesla is currently the most valuable car company in the world but maybe not by such a large margin as the financial press reports.
What about revenues, surely a company must have large revenues to justify a large valuation?
Tesla earns a fraction of the other car companies; investors are betting on Tesla growing these revenues substantially over the coming years…..
All data in the spreadsheet shown is provided by Excel Price Feed Add-in market data formulas (Yahoo Finance data).
The latest release of Excel is focused on performance improvements. The main areas that have been addressed are RealTimeData functions, faster opening of workbooks with many user defined functions and faster aggregation.
RealTimeData functions (RTD) are commonly used to update a spreadsheet from a real-time data source. For example, if you have a stock portfolio you can add RTD functions to your sheet to provide real-time prices: as the stock moves, the price in the Excel cell is updated automatically:
Microsoft has removed bottlenecks in the underlying memory and data structures and also made it “thread safe”. The result is a significant performance improvement on sheets with lots of RTD functions.
I have noticed with one of my testing spreadsheets, with lots of RTD functions, that Excel now consumes less CPU and is generally more responsive.
User Defined Functions (UDFs) are custom functions that can be created by users or provided by an Add-in to add additional functionality to Excel. These functions operate the same as regular functions/formulas.
Opening workbooks with many UDFs was previously very slow as Excel looked up each UDF on the sheet. Now, Excel includes a UDF cache to make this process much faster.
Aggregation functions like SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS are some of the most commonly used Excel functions. With the latest release of Excel the performance of these functions is drastically improved.
Yesterday a customer contacted us asking if our financial markets data Add-in could calculate the number of trading days between two dates. Unfortunately we don’t have this functionality as implementing it is not as easy as you may think.
You need to take into account weekends, which for most financial markets are Saturday and Sunday. Exceptions to this include several markets in the Middle East such as Saudi Arabia where the working week is Sunday to Thursday and the “weekend” is Friday and Saturday.
You also need to account for market holidays which are usually, although not always, also national holidays.
Fortunately Excel provides the NETWORKDAYS function, which:
Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays.
This function can be used, together with a holiday lookup, to provide the functionality we need.
In the example above we are using a holiday list lookup in column D together with the NETWORKDAYS function in cell B4 to compute the trading days between 21 July 2020 and 1 Dec 2020 for the US market.
The NETWORKDAYS function assumes that weekends are Saturday and Sunday. If you wish to specify a different weekend then you can use the NETWORKDAYS.INTL function.
Today I was talking to a customer and his issue was a strange one which I hadn’t seen before.
Every time he changed ANYTHING on his spreadsheet he noticed that it would refresh unrelated formulas, i.e. formulas that did not reference the cells he was updating.
The main formula on his sheet was this:
=EPF.Yahoo.HistoricDatePeriod("AMD","Weekly","1 Jan 2020", TODAY(), "DESC", 1)
This is an Excel Price Feed dynamic array formula which returns weekly historical stock market data for AMD stock from 1 Jan 2020 to today.
This formula is non-volatile so should only update when any of its parameters change.
So, what was going on?
The culprit was actually one of the parameters: the Excel TODAY() function.
This built-in Excel function is a volatile function and will update ANYTIME ANYTHING on the spreadsheet changes.
What is a Volatile Function?
Microsoft defines a volatile function as follows:
…one whose value cannot be assumed to be the same from one moment to the next even if none of its arguments (if it takes any) has changed.
There are 8 built-in volatile Excel formulas, and it is worth being aware that using these in your spreadsheet could have unintended consequences:
NOW
TODAY
RANDBETWEEN
OFFSET
INDIRECT
INFO (depending on its arguments)
CELL (depending on its arguments)
SUMIF (depending on its arguments)
The Solution
The solution was simple, replace the TODAY() function with a string value of the current date. This could be either hardcoded into the formula or better still entered into a cell and referenced from the formula.
So, if you every notice your spreadsheet starting to slow-down or start doing unnecessary calculations then look out for volatile functions.
The Excel Price Feed Blog 