Tag Archives: Excel

Math skills vs stage of life: Excel spreadsheet to python

Is this true? I think so if you ain’t programming. I have never seen anything as popular as thing. Must ring true if you end in a spreadsheet like Microsoft Excel or even in python.

Most traders and analysts use spreadsheets such as Excel. It makes we wonder how useful they would be if they decided to go to Python.

https://www.bleepingcomputer.com/news/microsoft/microsoft-considers-adding-python-as-an-official-scripting-language-to-excel/

Go to Python?

This may go somewhere. They even teach kids Python skills. Why are you not learning? This is especially true with your algorithmic/automated trading. We are wrapping up our live component  of this course this week.

Python Algo Trading Infrastructure with Crypto Currency

As you know the pricing of this course will be going in up in about 1 week. I will keep discounts in place if you want to transact in crypto currency like Bitcoin.  Ask away how we set that up!!

Thanks Bryan

HOW DO YOU START A PROFITABLE TRADING BUSINESS? Read more NOW >>>

NOTE I now post my TRADING ALERTS into my personal FACEBOOK ACCOUNT and TWITTER. Don't worry as I don't post stupid cat videos or what I eat!

xlq 6.3 introducing xlq2 for Microsoft Excel with norgate data

This is from the vendor of this powerful software if you want access to various market data source. This includes Norgate data

 

xlq 6.3_2 is available for download from https://www.qmatix.com/XLQ_Download.htm

To learn more about xlq2 please visit: https://www.qmatix.com/xlq2.htm

 

This release has many new features, including 3 new sources (Norgate Data, IEX and Tiingo), a new subscription level and new data engine and well over 100 new formulae.

 

Introducing XLQ2

 

In addition to xlqPlus, xlq is now also available  as a new fully compatible data engine offered under a new subscription level called xlq2. Xlq2 has been in development since 2011. It was designed to handle more data and to allow for more to be done with the data simultaneously laying the groundwork for many new possibilities. Xlq2 is provided under a different license, requiring a valid license to run. xlqPlus will continue to be developed under the same pricing and licensing as before. Making use of the new engine, new data views etc. xlq2 supports 2 new data sources, Norgate Data (subscription) and IEX (free), with both bringing tons of new information.

 

Norgate Data brings a new level of reliable analysis with data back till the 1950’s, can be processed as adjusted for splits, or adjusted for splits and dividends, or unadjusted, or all 3 at the same time. Allowing you to work with delisted symbols, detailed dividend data, as well as with historic constituent information. In addition to covering US and Australian Equites and world wide indexes they also cover futures, forex and economic data. Their Norgate Data Updater manages the data on your computer. In addition to the historic data, over 150 extra data fields covering financials / fundamentals etc. are also available. (Norgate Data offer a free trial with limited data).

IEX

IEX data is free (historic and real-time snapshots), covers 5 years of data but brings many values previously unavailable including historic dividends, splits, earnings and financials, along with around 40 extra fields such as short interest, float, insider % etc.

 

COM interface has been updated to fully support xlq2. New request functions xlqHistString, xlqHistString1 and xlqHistNorgate have been added to cover requests returning string values or requiring additional string parameters.

 

There are around 50 new AAII custom  fields calculated on existing values and included in a new ‘xlq aaii custom formulae.xlsx’  with xlq2 (can also be applied for quarterly / yearly values).
It includes calculations such as for Net Operating Assets, Excess cash, NOPAT, NOPAT return on invested capital etc.

 

New views are available within xlq2 to cover the dividend data etc, as well as a NorgateData view offering quick access to their underlying data. A new historic period view allows for historic data to be viewed for multiple symbols for a specific period (day, week or month). Also for dividends or earnings for a given month. Filters may be applied to different columns.

 

Xlq2 remains fully compatible with all xlq functionality and all xlqPlus sources are also available.

 

If you would like to try xlq2 before upgrading or purchasing send me an email and I will provide a 3 week trial key.

 

Until the 31 December, a 50% discount will be provided to existing xlqPlus users allowing to purchase an upgrade for $70 instead of $140. (Enter XLQ270 in the discount / promotion fields when upgrading.) If you no longer have a valid xlqPlus license but wish to purchase xlq2, you may use the same discount code when purchasing and the $70 discount will be applied to the new license.

 

xlqPlus and xlq2

 

Tiingo is now available as a data source. Their data is either free or subscription depending on what and how much you need. In order to begin using Tiingo in xlq, you would need to have registered with them (free) and enter your token into xlq (via preferences). They provide real-time snapshots and historic data (adjusted for splits or splits and dividends back till 1960’s) covering US and Chinese equities and funds. They also provide cryptocurrency data (possible to view historic data covering only transactions for a specific pair).

 

IQFeed’s latest protocol is supported (requires iqconnect 6.0.0.4 or later)

 

Interactivebrokers, new preference setting for historic data to include or exclude extended trading hours. Separate setting for intraday backfill. (previously they were combined).

 

Yahoo new preference setting to ignore dates with 0 volume (other than indexes)

 

Supports CoinmarketCaps new interface (Since releasing this interface they have now chosen to stop supporting it as of December and have introduced a new pay for API. Given their change of policy and fees they demand, coinmarketcap will be removed when their current api stops working.)

 

Google has been removed as a source as they are no longer providing any download data.

 

Other

 

The website is now hosted on its own servers so there would be a performance improvement. It will also have a new SSL certificate, and the whole site has been moved over to being encrypted using https, including for crash reports. As a result crash reports from old versions will now be rejected. (crash reports from old versions are of little use due to code changes. It is recommended to use the latest version as the crash has likely already been fixed). The problem where larger crash reports fail to upload has also been eliminated.

 

I have now partnered with FastSpring for all order processing. Their secure processing, offering more local payment choices, is now better integrated with a more modern and sophisticated interface, allowing you to see pricing in local currency with taxes before selecting the order pages, as well as being able to choose to include an additional, or 2 additional years of updates with your purchase.

Also within xlq from the About dialog, you can now click renew (or upgrade if applicable) to go to the order page with your registration name and code pre-filled (with the information currently in xlq on your computer) avoiding the need to find the information beforehand.

 

Installation

 

Note: If your license has expired, you will need to renew it in order for this new version to function. Before downloading and installing you can verify your license by right clicking on the Q in the task bar (while xlq is running) and choosing “About”. If the “Good for updates through” date is earlier than the release date, you should renew your license from this page: https://www.qmatix.com/XLQ_Purchase.htm. If your license has expired and you would like to purchase a new license to work with the latest release, please contact me for a discount.

 

This version requires Microsoft Visual C++ 2017 Redistributable version 14. 15.26706, which if not yet installed will automatically be downloaded and installed. In such a case the installation process will require an internet connection and a reboot.

If the runtime libraries are not already on your computer, you could alternatively install them from the links on the site.

 

If you visit the site and do not see the new download, your web browser would be showing cached pages. Click refresh in your web browser (pressing ctrl at the same time if needed) and your web browser will show the latest contents.

 

Please email me (leov@qmatix.com)  or reply to this email if:

  • You would like to receive notifications when beta versions of xlq are available.
  • There is anything you would like improved in xlq, or any feature you would like to see.
  • You no longer wish to be notified of new releases.

 

Thank you for your continued support.

 

Regards

 

Leo

HOW DO YOU START A PROFITABLE TRADING BUSINESS? Read more NOW >>>

NOTE I now post my TRADING ALERTS into my personal FACEBOOK ACCOUNT and TWITTER. Don't worry as I don't post stupid cat videos or what I eat!

xlq 6.1 for Microsoft Excel market data with Yahoo Finance

xlq 6.1 for Microsoft Excel market data with Yahoo Finance

This Excel add in is one of the only reasons why I would hang on to Windows

xlq 6.1 is available for download from http://www.qmatix.com/XLQ_Download.htm

 

What’s new:

 

Coinmarketcap

 

New formula =xlqPriceInBitcoin(), which can be used with CoinMarketCap to also return their prices in bitcoin. xlqPrice continues to return their US Dollar value.

A crash that could occur requesting data from CoinMarketCap has been eliminated.

Duplicate symbols are now handled by appending a number to those lower on the list

 

Yahoo

 

Yahoo day data handling has been modified to better handle bad data. Although some requests for cryptocurrencies can end up corrupting all data returned. Xlq will now detect this and if possible make corrections, and if it cannot will document the problem in the status view. The problem does not occur if no cryptocurrencies are requested.

 

It also includes extra checks and cleaning of yahoo data for European and Asian historic data where their downloaded date references are incorrect, apparently converting the returned date to a different time zone. For example Australian day data being referenced as Sunday to Thursday, and monthly as the month before (US symbols return a date of the 1st of the month, Europe and Asia will return the same data referenced as the last day of the prior month). Xlq will now look for the different problems in the data and correct the dates.

 

Compatibility change: Yahoo’s old download service returned the Forward Earnings per Share, which was returned via =xlqEarningsPerShare, they also return EPS estimates which they no longer do.

Their new service now returns “eps trailing 12 month” and “eps forward”. To maintain compatibility with the old service “eps forward” was originally returned. For this version “eps trailing 12 month” will now be returned via =xlqEarningsPerShare, and “eps forward” will be returned via =xlqEPSEstCurrentYear.

 

Other

 

New formulae xlqhAveVolume (+ week and month) has been added, calculated based on the preference settings. To avoid the need to specifically calculate one using the available xlqxh versions.

 

New preference setting for DMI/ADX allowing you to choose the calculation method (weighted (default) or EMA) for the xlqxh calculated versions. A separate preference setting remains for the xlqh versions.

 

Added the possibility for xlq to display a popup message concerning information on some recent event. The message will be displayed up to 5 times, unless dismissed and the functionality  may be disabled under general preference settings by changing “Enable Information Alerts” to no. As emails are only sent for new major releases and not minor builds, this method can now be used to notify of such updates and possibly other events such as a source outage, a breaking change etc. Messages will be kept to a minimum.

 

This version also includes numerous internal updates as well as updated  supporting libraries.

 

Note, many historic google requests rely more on the exchange being provided, e.g. if msft does not return data, use msft:Nasdaq etc. Examples in xlqDemo have been updated accordingly.

 

Installation

 

Note: If your license has expired, you will need to renew it in order for this new version to function. Before downloading and installing you can verify your license by right clicking on the Q in the task bar (while xlq is running) and choosing “About”. If the “Good for updates through” date is earlier than the release date, you should renew your license from this page:http://www.qmatix.com/XLQ_Purchase.htm. If your license has expired and you would like to purchase a new license to work with the latest release, please contact me for a discount.

 

This version requires Microsoft Visual C++ 2017 Redistributable, i.e. version 14.12.25810 (same as xlq 6.0), which if not yet installed will automatically be downloaded and installed. In such a case the installation process will require an internet connection and a reboot.

If the runtime libraries are not already on your computer, you could alternatively install them from the links on the site.

 

If you visit the site and do not see the new download, your web browser would be showing cached pages. Click refresh in your web browser (pressing ctrl at the same time if needed) and your web browser will show the latest contents.

 

Please email me (leov@qmatix.com)  or reply to this email if:

  • You would like to receive notifications when beta versions of xlq are available.
  • There is anything you would like improved in xlq, or any feature you would like to see.
  • You no longer wish to be notified of new releases.

 

Thank you for your continued support.

 

HOW DO YOU START A PROFITABLE TRADING BUSINESS? Read more NOW >>>

NOTE I now post my TRADING ALERTS into my personal FACEBOOK ACCOUNT and TWITTER. Don't worry as I don't post stupid cat videos or what I eat!

Excel add ins from CoinMarketCap to Redis

Excel add ins from CoinMarketCap to Redis

If I do this XLQ (for Coin Market Cap data), I should be able to push the data into Redis using this Excel add in

https://www.cdata.com/kb/tech/redis-excel-trans.rst

In beta for now

https://www.cdata.com/drivers/redis/excel/

Will I be stuck on Windows? It seems CCXT does support CoinMarketCap but it MAY NOT give the data for % change as in XLQ

CoinMarketCap

CoinMarketCapvia xlqPlus you can work with data for cryptocurrencies from Coin Market Cap (currently over 1300).

Includes formulae for their Rank, MarketCap, Available Supply, and 1 hour, 24 hour and 7 day percent change.

Data is updated every 5 minutes. http://www.qmatix.com

Looking at this CoinMarketCap directly, it seems easiest just to work their end point as described here:

https://coinmarketcap.com/api/

You will not be able to get references from the exchanges so the next question is how to get a quick reference in which exchanges supports which coin.

You could use this example to list out all the coins (symbols) to react to:

https://github.com/ccxt/ccxt/blob/master/examples/py/symbols.py

New  XLQ for Bitcoin and Cyrptocurrency with Coinmarketcap access 

HOW DO YOU START A PROFITABLE TRADING BUSINESS? Read more NOW >>>

NOTE I now post my TRADING ALERTS into my personal FACEBOOK ACCOUNT and TWITTER. Don't worry as I don't post stupid cat videos or what I eat!

Microsoft To Considers Adding Python as an Official Scripting Language for Excel

Microsoft Considers Adding Python as an Official Scripting Language to Excel

<< Test First Name >>,
Will this really file or will it get abandoned eventually?

 

Here are some Facebook post comments on this:

 

Now a fuc*in news

 

Why would you use Python in Excel, when you can use just Python  😀 The mind boggles sometimes.

 

Refer to the links here

 

https://quantlabs.net/blog/2017/12/microsoft-considers-adding-python-as-an-official-scripting-language-to-excel/

 

Here is the video 

See the links above
Thanks Bryan

HOW DO YOU START A PROFITABLE TRADING BUSINESS? Read more NOW >>>

NOTE I now post my TRADING ALERTS into my personal FACEBOOK ACCOUNT and TWITTER. Don't worry as I don't post stupid cat videos or what I eat!

Microsoft Considers Adding Python as an Official Scripting Language to Excel

 

Will this really file or will it get abandoned eventually?

https://www.bleepingcomputer.com/news/microsoft/microsoft-considers-adding-python-as-an-official-scripting-language-to-excel/

https://forms.office.com/Pages/ResponsePage.aspx?id=v4j5cvGGr0GRqy180BHbR7tUuWqOwSJFpBE5ZLhdkgtUMkhZWlkxRjhDRklXSjNTVkNSWkE2WlNQMS4u

https://www.forbes.com/sites/danwoods/2015/01/27/microsofts-revolution-analytics-acquisition-is-the-wrong-way-to-embrace-r/#5ccc945e75e1

http://ironpython.net

 

https://www.python.org/downloads/

 

HOW DO YOU START A PROFITABLE TRADING BUSINESS? Read more NOW >>>

NOTE I now post my TRADING ALERTS into my personal FACEBOOK ACCOUNT and TWITTER. Don't worry as I don't post stupid cat videos or what I eat!

Will I Automate Excel for stock data 

Will I Automate Excel for stock data ?

A question from a member

Hi Bryan,

Are you going to automate the spreadsheet where it has all the stocks data?
the one where it shows the PE0,PE1, EPS0,EPS1 and PEG with all industries.
automating that will certainly help reduce a lot of work already to look for trading ideas!
I wonder if he refers to EXCEL and XLQ. I will use for presentation purposes
https://www.youtube.com/user/quantlabs/search?query=xlq

HOW DO YOU START A PROFITABLE TRADING BUSINESS? Read more NOW >>>

NOTE I now post my TRADING ALERTS into my personal FACEBOOK ACCOUNT and TWITTER. Don't worry as I don't post stupid cat videos or what I eat!

Python Finance Excel

Python Finance Excel

A really good presentation on Python and Excel Finance

P

https://github.com/ZoomerAnalytics/talks/tree/master/thalesians_zrh_20160609

From Sholom B so thanks to him

Join my FREE newsletter to learn more about these online presentation for Python automated trading

 

HOW DO YOU START A PROFITABLE TRADING BUSINESS? Read more NOW >>>

NOTE I now post my TRADING ALERTS into my personal FACEBOOK ACCOUNT and TWITTER. Don't worry as I don't post stupid cat videos or what I eat!

Cheat sheets for R Python Excel and machine learning

Cheat sheets for R Python Excel and machine learning

Man, this overwhelms even those with 30+ years experience

http://www.datasciencecentral.com/profiles/blogs/20-data-science-r-python-excel-and-machine-learning-cheat-sheets?overrideMobileRedirect=1

Join my FREE newsletter to learn the best way to learn machine learning for automated trading

HOW DO YOU START A PROFITABLE TRADING BUSINESS? Read more NOW >>>

NOTE I now post my TRADING ALERTS into my personal FACEBOOK ACCOUNT and TWITTER. Don't worry as I don't post stupid cat videos or what I eat!

Another Python package for Excel

Another Python package for Excel

I just learned about this tonight

http://xlwings.org/quickstart/

Join my FREE newsletter learn about what other Pythons I use for my automated trading

HOW DO YOU START A PROFITABLE TRADING BUSINESS? Read more NOW >>>

NOTE I now post my TRADING ALERTS into my personal FACEBOOK ACCOUNT and TWITTER. Don't worry as I don't post stupid cat videos or what I eat!