Get External Data (long shot question!)

[leopardhawk]

This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to by 'Get External Data' every year on January 1 at 00:01 local time?

The following URL is currently in use on a worksheet within my workbook:

https://www.taxtips.ca/nrcredits/tax-credits-2019-base.htm

I would like it to change to the following on January 1, 2020 at 00:01 local time:

https://www.taxtips.ca/nrcredits/tax-credits-2020-base.htm

And then do the same thing every year into perpetuity. If possible...

Thanks!

 

[sandy666]

Re: Need help with Get External Data (long shot question!)

ok, you've PowerQuery built-in

so try this:

Example

don't open it in browser just download and open in Excel

 

[leopardhawk]

Re: Need help with Get External Data (long shot question!)

Okay, John and sandy, the macro (John) works as you said it would, and sandy's solution also works, however, both force me to change the way I was hoping to get the data every year. What I was hoping to do is somehow automate the process so that there is no manual involvement required. I am planning to distribute the workbook to family and friends and need it to be as simple as possible for those (most) of them that don't know Excel. If I can tie the data refresh to the system clock so then I wouldn't have to do that. My data refresh occurs when the file is opened so when someone opens the file after midnight on January 1, 2020, the refresh would go to the 2020 webpage instead of the 2019 webpage and so on every year afterwards... See my first post in this thread.

If this is not possible, I can try and come up with an alternative. I just thought I would check with everyone here on the forum first.

Thanks so much!

 

[sandy666]

Re: Need help with Get External Data (long shot question!)

in A2 remove Data Validation list then insert: =YEAR(TODAY()) (you can use NOW() instead of TODAY() if you want)
in Data tab - Connection - select connection [Query - Year] - Connection Properties choose eg. Refresh on Open file

 

[sandy666]

Re: Need help with Get External Data (long shot question!)

In case with formula =YEAR(TODAY()) edit Query Year and change name of the Query Table to tblYear

or re-download the file from post #11

be aware that PowerQuery is required

 

[leopardhawk]

Re: Need help with Get External Data (long shot question!)

sandy, in your example, there are no formulas or code so I am having difficulty understanding how this is going to work in my workbook...

 

[sandy666]

Re: Need help with Get External Data (long shot question!)

did you re-download the file?

you need delete previous downloaded file and download again
try again

 

[sandy666]

Re: Need help with Get External Data (long shot question!)

and this is a whole used M-code and formula

A2:

=YEAR(TODAY())

[SIZE=1]let Year = (YR) =>
let
    Source = Web.Page(Web.Contents("https://www.taxtips.ca/nrcredits/tax-credits-"&Number.ToText(YR)&"-base.htm")),
    Data1 = Source{1}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data1, [PromoteAllScalars=true])
in
    #"Promoted Headers"
in
    Year[/SIZE]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Invoked Custom Function" = Table.AddColumn(Source, "TaxTips", each fnYear([YR])),
    #"Expanded TaxTips" = Table.ExpandTableColumn(#"Invoked Custom Function", "TaxTips", {"Tax Credit Type", "Federal#(cr)#(lf)Line#(cr)#(lf)Number", "Provincial#(cr)#(lf)Line#(cr)#(lf)Number", "Federal#(cr)#(lf)15%#(cr)#(lf)(3)", "NL#(cr)#(lf)8.7%", "PE#(cr)#(lf)9.8%#(cr)#(lf)(5)", "NS#(cr)#(lf)8.79%#(cr)#(lf)(1)", "NB#(cr)#(lf)9.68%", "ON#(cr)#(lf)5.05%", "MB#(cr)#(lf)10.8%#(cr)#(lf)(6)", "SK#(cr)#(lf)10.50%#(cr)#(lf)(7)", "AB#(cr)#(lf)10%#(cr)#(lf)(8)", "BC#(cr)#(lf)5.06%", "YT#(cr)#(lf)6.40%", "NT#(cr)#(lf)5.9%", "NU#(cr)#(lf)4%#(cr)#(lf)(4)"}, {"Tax Credit Type", "Federal#(cr)#(lf)Line#(cr)#(lf)Number", "Provincial#(cr)#(lf)Line#(cr)#(lf)Number", "Federal#(cr)#(lf)15%#(cr)#(lf)(3)", "NL#(cr)#(lf)8.7%", "PE#(cr)#(lf)9.8%#(cr)#(lf)(5)", "NS#(cr)#(lf)8.79%#(cr)#(lf)(1)", "NB#(cr)#(lf)9.68%", "ON#(cr)#(lf)5.05%", "MB#(cr)#(lf)10.8%#(cr)#(lf)(6)", "SK#(cr)#(lf)10.50%#(cr)#(lf)(7)", "AB#(cr)#(lf)10%#(cr)#(lf)(8)", "BC#(cr)#(lf)5.06%", "YT#(cr)#(lf)6.40%", "NT#(cr)#(lf)5.9%", "NU#(cr)#(lf)4%#(cr)#(lf)(4)"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded TaxTips",{"YR"})
in
    #"Removed Columns"[/SIZE]

to see M-code :
Data tab - Show Queries - right click on the table on the right side - Edit - Advanced Editor

 

[John_w]

Re: Need help with Get External Data (long shot question!)

Okay, John and sandy, the macro (John) works as you said it would, and sandy's solution also works, however, both force me to change the way I was hoping to get the data every year. What I was hoping to do is somehow automate the process so that there is no manual involvement required. I am planning to distribute the workbook to family and friends and need it to be as simple as possible for those (most) of them that don't know Excel. If I can tie the data refresh to the system clock so then I wouldn't have to do that. My data refresh occurs when the file is opened so when someone opens the file after midnight on January 1, 2020, the refresh would go to the 2020 webpage instead of the 2019 webpage and so on every year afterwards... See my first post in this thread.

If this is not possible, I can try and come up with an alternative. I just thought I would check with everyone here on the forum first.

Thanks so much!

Yes, it is possible to automate this with no manual intervention or having to put the required year in a cell - my first solution was just to show that the correct web page data is imported for the required year.

With my second solution below, the process is automated so that the new year data is imported only when the current year changes. Furthermore, the imported data remains static until the current year changes or you refresh it manually. The data is not refreshed when the workbook is opened.

Start with a new workbook and paste this code into a standard module (e.g. Module1):

Public Sub Get_Tax_Credits(currentYear As String)
    
    Dim qt As QueryTable
            
    With ActiveSheet
        If .QueryTables.Count = 1 Then
            Set qt = .QueryTables(1)
            If qt.Name <> "tax-credits-" & currentYear Then qt.Delete
        End If
        If .QueryTables.Count = 0 Then
            .Cells.Clear
            Set qt = .QueryTables.Add(Connection:="URL;https://www.taxtips.ca/nrcredits/tax-credits-" & currentYear & "-base.htm", Destination:=.Range("A1"))
            With qt
                .Name = "tax-credits-" & currentYear
                .RefreshOnFileOpen = False
                .SaveData = True
                .WebSelectionType = xlSpecifiedTables
                .WebFormatting = xlWebFormattingNone
                .WebTables = "4"
                .Refresh BackgroundQuery:=False
            End With
        End If
    End With
    
End Sub

Put this code in the ThisWorkbook module:

Private Sub Workbook_Open()

    Dim currentYear As String
    
    Worksheets("Sheet1").Activate
    currentYear = InputBox("Enter the simulated current year")
    If currentYear = "" Then currentYear = Year(Date)
    Get_Tax_Credits currentYear
    
End Sub

"Sheet1" is the name of the sheet where the data will be imported - change as necessary. The InputBox line prompts you for the 'current' year, allowing you to simulate the process for any available year, without having to change the computer's clock.

Save, close and reopen the macro workbook to test it. Once you're satisfied that it works correctly, you can delete the InputBox line and then the code will always operate on the actual current year.

 

[sandy666]

Re: Need help with Get External Data (long shot question!)

or much simpler version with automated Year and Refresh on Open file

[SIZE=1]let
    Source = Web.Page(Web.Contents("https://www.taxtips.ca/nrcredits/tax-credits-"&Number.ToText(Date.Year(DateTime.LocalNow()))&"-base.htm")),
    Data1 = Source{1}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data1, [PromoteAllScalars=true])
in
    #"Promoted Headers"[/SIZE]

simpler version

that's all

 

[leopardhawk]

Re: Need help with Get External Data (long shot question!)

Okay, John, when you say "Start with a new workbook and paste this code into a standard module (e.g. Module1):", do you mean 'new worksheet'? I wasn't sure what you meant by that so I copied your code into a module for the worksheet where I want to import the data from the web (it's called tax_credits_web). Then I pasted the second piece of code into a new module on ThisWorkbook, as you said to do and changed 'Sheet1' to 'tax_credits_web' as you also alluded to. I saved the workbook as an 'Excel Macro enabled workbook', closed the file and re-opened. Where will I find this InputBox prompt because I am not seeing it?

I would like to be able to test this now so I will wait to hear from you.