Web Scrapping Online Stock Prices

Investing in financial markets is one of my  interests. 

I’ve built this report to help me track the performance of my investment in stocks.
As you may suspect it is something that I particularly care and for that reason this report is a real world scenario!

Due to privacy matters I am only showing % weights and variations on the investments. However, the truth is that’s the most important outcome of the analysis. Just assume x dollars and do the mats to convert it to  dollar amount

Technically, I’ve used a few distinct approaches to collect and transform the data used on this report:

  1. Excel file located in OneDrive for manual input of my transactions;
  2. R Quantmod package  to scrap daily prices from Google Finance
  3. HTML direct query Yahoo Finance for the EUR/USD forex rate.
  4. Image URL method to display the companies logos (OneDrive repository);
  5. Parameter and Invoked Function to change the Proxy and automatically recalculate the figures;
  6. A bunch of measures with var … return if …, lookup table and others not so fancy but effective technics
  7. and more.

This still work-in-progress project, so not be surprise if it had changed meanwhile.
If you are interested to know mora about this report and/or the technics I’ve used here,  just drop me an email or leave a comment below.

Power BI April 2020 release

It’s power week and Power BI team has released a bunch of super cool Power BI updates!!! Some of them highly requested by the Power BI community. Excited to learn all about them?

Check out our Top 5 features you need to know about:

#5: Rectangular lasso select across visuals

You can now select your visuals and other report elements by clicking and dragging over the canvas to create a rectangular lasso. All visuals that are entirely encapsulated within the lasso will be selected. If you are holding down Ctrl + G you can also group visuals.

#4: Customized themes

The new theme dialog is now generally available. You can customize a theme in the theme dialog, with you company colour, font etc, an icon will represent that custom theme in a separate section and will update whenever you customize your theme.

 If you hover over the icon, a tooltip will show up that explains that the theme is a custom theme, and the theme name will also show up if you define it. 

#3: Improved discoverability for conditional formatting & Conditional formatting for totals and subtotals in table and matrix

These are actually 2 separate updates but they definitely deserve to be mentioned, the menu to access the conditional formatting was very difficult to find previously but with his release Microsoft team have updated the entry point into the conditional formatting dialog to make it more discoverable, but displaying an fx button to the right of the object you would like to format.

Clicking the fx button will bring up the conditional formatting dialog. After applying a conditional formatting rule, the input field should now look like this:

Conditional formatting for totals and subtotals in table and matrix

This is probably one of the most requested features by the community, now it will be possible to apply conditional formatting rules to totals and subtotals in table and matrix visuals.

 

#2:Change detection for page refresh

This is a new option for an automatic page refresh, in which the page is refreshed based on change detection instead of a specific refresh interval. This is a query that will run in the background to check if data has changed and will only refresh the report if a change has been detected. In this release, we will have a new data change detection option to specify how to query for changes to our Direct query source, specifically which measure should be evaluated and how frequently they should be checked for changes to that measure.

Important considerations for this new capability:

  • Only DirectQuery sources are supported
  • You’re only allowed to specify one change detection measure per model
  • In Desktop, when you set up change detection, visuals won’t refresh. You will have to publish to the service for page refresh to work, which includes re-authenticating with the same credentials for refresh.
  • This is a premium only feature
  • Premium capacity admin must turn this on in the admin portal

#1:Personalize visuals

This is a new visual personalization feature to allow your end-users to explore and personalize visuals all within the consumption view of a report. This is definitely our favorite new feature as it gives the end-users the ability to explore and modify the properties of a visual by having the full editing experience.

Using this feature, your end-users can explore a visual in many ways:

  • Change the visualization type
  • Swap out a measure or dimension
  • Add or remove a legend
  • Compare two or more different measures
  • Change aggregations, etc.

Not only does this feature allow for new exploration capabilities, but it also includes ways for end-users to capture, share, and revert their changes:

  • Capture their changes
  • Share their changes
  • Reset all their changes to a report
  • Reset all their changes to a visual
  • Clear out their recent changes

Please let us know which was your favorite release and if you agree with our top 5.

To know more about the power BI April 2020 update visit:

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-april-2020-feature-summary/#_Personalize_visuals

DAX and M edition Helpers

We need to admit, the GUI for Power BI Formula Bar (DAX) and the Power Query Advanced Editor (M) are not the most user-friendly.
DAX editor (formula bar) is getting better but still quite limited. M editor (Advanced Editor) … OK, this might be something more advanced and eventually not everyone will need to write M functions.

But just in case if you are interested on it, these are my Helpers to write and format DAX and M code:

    • DAX STUDIO – “DAX Studio is a tool to write, execute, and analyze DAX queries in Power BI Designer, Power Pivot for Excel, and Analysis Services Tabular.” by SQLBI
    • DAX FORMATTER -“DAX Formatter is a free tool that transform your raw DAX formulas into clean, beautiful and readable code.” by SQLBI
    • POWER BI HELPER – “Export the Entire M Power Query Script from a Power BI File” by RADCAD
    • NOTEPAD++ – “Notepad++ is a text editor and source code editor for use with Microsoft Windows.” by Don Ho

Continue reading “DAX and M edition Helpers”