Home

從網路檢索實時數據到Excel

一位本科生最近問我如何將網站上的實時數據導入Excel來完成作業。許多金融數據來源都需要收費，因此我提供了一個簡單的、免費的解決方案。現在，我也將這個解決方案分享給您。

首先，打開Microsoft Excel 365並轉到數據選項卡。找到從其他來源獲取數據的選項，然後選擇網絡。

接下來，輸入包含所需數據的URL。

然後導航器將顯示各種可選表格。對於這個例子，我們選擇表格8。

當這個完成后，數據將導入您的Excel表格。但是，它需要手動刷新。要自動化這個過程，請右鍵點擊查詢，然後將屬性更改為每1分鐘刷新一次。

這對於每分鐘更新一次很有用。但是如果你希望每秒鐘幾乎實時更新怎麼辦？在那種情況下，您需要編寫一些代碼。

導航到文件 -> 選項 -> 自定義功能區。在主選項卡下，啟用開發者選項卡。

然後，在開發者選項卡中，選擇Visual Basic。

選擇插入 -> 模塊，然後復制並粘貼下面的代碼片段：

Sub refresh_data()
    Sheets("Sheet2").Select
    Range("B2").Select
    Selection.Copy

    Sheets("Sheet1").Select
    Dim xCell As Range
    For Each xCell In ActiveSheet.Columns(1).Cells
        If Len(xCell.Value) = 0 Then
            xCell.Select
            Exit For
        End If
    Next
    ActiveSheet.Paste

    Sheets("Sheet2").Select
    ActiveWorkbook.RefreshAll
    Application.OnTime DateAdd("s", 1, Now), "refresh_data"
End Sub

點擊運行，然後就全部設定完成了！這段代碼可以翻譯為簡單的步驟：選擇Sheet 2，複制當前值，去到Sheet 1，找到第一列的最後一行並將值粘貼在那裡，然後回到Sheet 2並刷新所有數據。最後，該代碼將每秒觸發一次，使您能夠在Excel中實現幾乎實時的數據更新。

如果您有任何進一步的問題，隨時在下面留言。:)

Enabling HTTPS on an AWS EC2 Instance with Node.js and Nginx on an Ubuntu Server

I have an AWS EC2 instance running Node.js and Nginx on an Ubuntu 16.04 server. In this tutorial, I will show you how to switch your website from HTTP to HTTPS using Let's Encrypt. HTTPS prevents unauthorized tampering with the communication between your website and your users' browsers. It encrypts this communication using Transport Layer Security (TLS) Certification. Let’s Encrypt is a certificate authority that provides free X.509 certificates.

Firstly, SSH into your EC2 instance:

ssh -i <keyfile.pem> ubuntu@<public-ip-address>

Next, clone the Let’s Encrypt repository into the /opt/letsencrypt path:

sudo git clone https://github.com/letsencrypt/letsencrypt /opt/letsencrypt

Then, check if any application is listening on port 80 by running:

netstat -na | grep ':80.*LISTEN'

If any processes are returned, terminate them. For example, if you already have an Nginx server running on port 80, you may need to stop it as follows:

sudo systemctl stop nginx

After that, navigate to your repository with cd /opt/letsencrypt and run the following command to obtain the certificates:

./letsencrypt-auto certonly --standalone --email <your@email.com> -d <domain.com> -d <subdomain.domain.com>

If you encounter an error like this:

OSError: Command /opt/eff.org/certbot/venv/bin/python2.7 - setuptools pkg_resources pip wheel failed with error code 1

Then set the following environment variables before you rerun the script:

export LC_ALL="en_US.UTF-8"
export LC_CTYPE="en_US.UTF-8"

Follow the on-screen instructions, and you should receive your certificates at the path /etc/letsencrypt/live/<domain.com>.

Next, configure the Nginx settings to redirect your HTTP traffic to HTTPS. Edit the file using:

sudo vi /etc/nginx/sites-available/default

The content should look like this (remember to replace <YourDomain.com> and the root path for your website):

server {
  listen 443 ssl;
  server_name <YourDomain.com>;
  ssl_certificate /etc/letsencrypt/live/<YourDomain.com>/fullchain.pem;
  ssl_certificate_key /etc/letsencrypt/live/<YourDomain.com>/privkey.pem;
  ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
  ssl_prefer_server_ciphers on;
  ssl_ciphers 'EECDH+AESGCM:EDH+AESGCM:AES256+EECDH:AES256+EDH';

  root /var/www/yourPath;
  index index.html index.htm;

  location / {
    proxy_pass http://localhost:3000/;
    proxy_http_version 1.1;
    proxy_set_header Upgrade $http_upgrade;
    proxy_set_header Connection "upgrade";
    proxy_set_header Host $http_host;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header X-Forwarded-Proto http;
    proxy_set_header X-Nginx-Proxy true;
    proxy_redirect off;
  }
}
server {
  listen 80;
  server_name <domain.com>;
  return 301 https://$host$request_uri;
}

To test your configuration for any errors, run:

sudo nginx -t

If everything is okay, restart Nginx:

sudo service nginx stop
sudo service nginx start

Last but not least, go to the AWS console and make sure your security group has port 443 open for HTTPS.

Done! Navigate to the HTTPS version of your domain to verify that it's working. If you encounter issues like a 502 Bad Gateway error, ensure that your Node.js application is running correctly. I use PM2 to keep it running. Let's make the internet more secure! 🙂

Enabling HTTPS on an AWS EC2 Instance with Node.js and Nginx on an Ubuntu Server

Welcome to "Continuous Improvement," the podcast where we explore ways to enhance our skills and make progress in our personal and professional lives. I'm your host, Victor, and today we'll be discussing a topic that's crucial for any website owner – switching from HTTP to HTTPS using Let's Encrypt.

So, why is this important? Well, HTTPS provides a secure connection between your website and your users' browsers, preventing unauthorized tampering and encrypting communication using Transport Layer Security (TLS) Certification. And the best part? Let's Encrypt offers free X.509 certificates.

The first step is to SSH into your AWS EC2 instance running Node.js and Nginx on Ubuntu 16.04. Open your terminal and enter the following command:

ssh -i <keyfile.pem> ubuntu@<public-ip-address>

Great! Now that we're connected, let's clone the Let's Encrypt repository into the /opt/letsencrypt path:

sudo git clone https://github.com/letsencrypt/letsencrypt /opt/letsencrypt

Before we proceed, it's important to make sure there are no processes already listening on port 80. To check, run the following command:

netstat -na | grep ':80.*LISTEN'

If any processes are returned, terminate them. For instance, if you have an Nginx server running on port 80, you can stop it by entering:

sudo systemctl stop nginx

Excellent! Now let's navigate to the Let's Encrypt repository by running cd /opt/letsencrypt, and obtain our certificates with the following command:

./letsencrypt-auto certonly --standalone --email <your@email.com> -d <domain.com> -d <subdomain.domain.com>

If you encounter an error like this:

OSError: Command /opt/eff.org/certbot/venv/bin/python2.7 - setuptools pkg_resources pip wheel failed with error code 1

Simply set the following environment variables before rerunning the script:

export LC_ALL="en_US.UTF-8"
export LC_CTYPE="en_US.UTF-8"

Follow the on-screen instructions, and you should receive your certificates at the path /etc/letsencrypt/live/<domain.com>.

Now, it's time to configure the Nginx settings to redirect your HTTP traffic to HTTPS. Use the following command to open the Nginx configuration file:

sudo vi /etc/nginx/sites-available/default

Inside the file, replace <YourDomain.com> and the root path for your website with your domain and the appropriate paths. Your configuration should look like this:

[nginx configuration]

Wonderful! To ensure that there are no errors in your configuration, run the command:

sudo nginx -t

If everything checks out, restart Nginx by entering:

sudo service nginx stop
sudo service nginx start

Almost there! Don't forget to go to your AWS console and make sure that your security group has port 443 open for HTTPS.

And that's it! You've successfully switched your website from HTTP to HTTPS using Let's Encrypt. To verify that everything is working correctly, navigate to the HTTPS version of your domain. If you encounter any issues, such as a 502 Bad Gateway error, make sure your Node.js application is running correctly. Consider using PM2 to keep it up and running smoothly.

Remember, by securing our websites and making the internet safer, we contribute to a more secure online environment for everyone. Keep up the excellent work, and until next time, keep striving for continuous improvement.

Thanks for tuning in to this episode of "Continuous Improvement." If you enjoyed this episode, be sure to subscribe to our podcast for more valuable insights. And if you have any suggestions for future topics, feel free to reach out. See you next time!

在Ubuntu伺服器上使用Node.js和Nginx，為AWS EC2實例啟用HTTPS

我有一個在Ubuntu 16.04伺服器上運行Node.js和Nginx的AWS EC2實例。在這篇教程中，我將向你展示如何使用Let's Encrypt將你的網站從HTTP轉換為HTTPS。HTTPS防止未經授權的篡改你的網站與用戶瀏覽器之間的通信。它使用傳輸層安全性（TLS）認證對這種通信進行加密。Let’s Encrypt是一個提供免費X.509證書的證書頒發機構。

首先，SSH 連接到你的 EC2 實例：

ssh -i <keyfile.pem> ubuntu@<public-ip-address>

接著，將Let’s Encrypt的儲存庫複製到/opt/letsencrypt路徑：

sudo git clone https://github.com/letsencrypt/letsencrypt /opt/letsencrypt

然後，透過運行以下指令來檢查是否有任何應用程式正在監聽80端口：

netstat -na | grep ':80.*LISTEN'

如果返回任何進程，終止它們。例如，如果你已經有一個Nginx伺服器在80端口運行，你可能需要如下停止它：

sudo systemctl stop nginx

之後，導航到你的儲存庫cd /opt/letsencrypt，並運行以下命令以獲得證書：

./letsencrypt-auto certonly --standalone --email <your@email.com> -d <domain.com> -d <subdomain.domain.com>

如果你遇到像這樣的錯誤：

OSError: Command /opt/eff.org/certbot/venv/bin/python2.7 - setuptools pkg_resources pip wheel failed with error code 1

然後在重新運行腳本之前設置以下環境變數：

export LC_ALL="en_US.UTF-8"
export LC_CTYPE="en_US.UTF-8"

遵循屏幕上的指示，您應該在路徑/etc/letsencrypt/live/<domain.com>處獲得您的證書。

接下來，配置Nginx設置以將HTTP流量重新導向到HTTPS。使用以下命令編輯文件：

sudo vi /etc/nginx/sites-available/default

內容應該像這樣（記得替換<YourDomain.com> 和你的網站的根路徑）：

server {
  listen 443 ssl;
  server_name <YourDomain.com>;
  ssl_certificate /etc/letsencrypt/live/<YourDomain.com>/fullchain.pem;
  ssl_certificate_key /etc/letsencrypt/live/<YourDomain.com>/privkey.pem;
  ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
  ssl_prefer_server_ciphers on;
  ssl_ciphers 'EECDH+AESGCM:EDH+AESGCM:AES256+EECDH:AES256+EDH';

  root /var/www/yourPath;
  index index.html index.htm;

  location / {
    proxy_pass http://localhost:3000/;
    proxy_http_version 1.1;
    proxy_set_header Upgrade $http_upgrade;
    proxy_set_header Connection "upgrade";
    proxy_set_header Host $http_host;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header X-Forwarded-Proto http;
    proxy_set_header X-Nginx-Proxy true;
    proxy_redirect off;
  }
}
server {
  listen 80;
  server_name <domain.com>;
  return 301 https://$host$request_uri;
}

為了測試你的配置是否有任何錯誤，運行：

sudo nginx -t

如果一切正常，重啟Nginx：

sudo service nginx stop
sudo service nginx start

最後但並非最不重要的，前往 AWS 控制台，確保你的安全群組已打開 443 端口，開放 HTTPS 連接。

完成!導航至HTTPS版本的你的網域，驗證它是否正常工作。如果您遇到像502 Bad Gateway這種錯誤，確保你的Node.js應用程式正確運行。我使用PM2來保持它運行。讓我們使網路更安全！🙂

Replace Text in XML Files with PowerShell

Yesterday, I was working at a client site that has a Windows server isolated from the external network. Installing any third-party software on the machine is not permitted.

PowerShell Script

However, I was tasked with replacing all XML file names from "My Report" to "My Report (New)". The original file, temp.xml, looks like this:

    <ReportList>
      <Report Name="My Report">
      </Report>
    </ReportList>

The expected output file, temp-new.xml, should have a structure like this:

    <ReportList>
      <Report Name="My Report (New)">
      </Report>
    </ReportList>

Without access to any specialized tools and facing the prospect of manually editing hundreds of files, I turned to PowerShell for scripting. Here are a few lines of code that accomplish the task:

Step 1: Load all XML files from my Test folder

    $files = Get-ChildItem C:\Users\victorleung\tw\Desktop\Test -Recurse -Include *.xml

Step 2: Modify all report names by adding " (New)" after the original name

    $xmldata = [xml](Get-Content $file);
    $name = $xmldata.ReportList.Report.GetAttribute("Name");
    $name = $name + " (New)";
    $xmldata.ReportList.Report.SetAttribute("Name", $name);
    $xmldata.Save($file)

Step 3: Change the file name from temp.xml to temp-new.xml

    Get-ChildItem *.xml | Rename-Item -NewName { $_.Name -Replace '.xml$','-new.xml' }

That's it! All the files have been changed. Happy coding! 😃

Replace Text in XML Files with PowerShell

Hello everyone and welcome to "Continuous Improvement," the podcast where we explore practical solutions for everyday challenges. I'm your host, Victor, and today we'll be discussing a script that helps automate file renaming using PowerShell.

Yesterday, I encountered a scenario where I had to replace specific XML file names for a client's Windows server that had no access to external networks or installations of third-party software. This task seemed daunting at first, but with a little creativity and the power of PowerShell, I found a solution.

Let's take a closer look at the code involved. First, I needed to load all XML files from my designated folder. To accomplish this, I used the following line of code:

$files = Get-ChildItem C:\Users\victorleung\tw\Desktop\Test -Recurse -Include *.xml

This command allowed me to retrieve all XML files from the specified folder and its subfolders.

Next, I moved on to modifying the report names within the XML files. The code snippet below accomplishes this task:

$xmldata = [xml](Get-Content $file);
$name = $xmldata.ReportList.Report.GetAttribute("Name");
$name = $name + " (New)";
$xmldata.ReportList.Report.SetAttribute("Name", $name);
$xmldata.Save($file)

Here, we read the content of each file as XML data. We then access the specific attribute, "Name," of the report element within the XML structure. By appending " (New)" to the original name, we update the attribute value accordingly. Finally, we save the modified XML data back into the file.

Lastly, I wanted to change the file name from its original to a new naming convention. This can be achieved using the following code:

Get-ChildItem *.xml | Rename-Item -NewName { $_.Name -Replace '.xml$','-new.xml' }

This line of code uses the Rename-Item cmdlet to change the file names. We utilize a regular expression pattern to replace the ".xml" extension with "-new.xml."

And voila! With these simple PowerShell lines, we were able to efficiently rename and modify hundreds of files without relying on external software installations or compromising security measures.

I hope you found this PowerShell script useful for your own file management tasks. Remember, continuous improvement is all about finding creative solutions to streamline our work processes.

If you have any questions or comments about today's episode, feel free to reach out to me through our podcast's website or social media channels. I'm always excited to hear from our listeners.

Thank you for tuning in to "Continuous Improvement." Stay curious, stay inspired, and keep striving for improvement in all aspects of your life. Until next time!

[Theme music fades in and out]

[End of episode]

使用PowerShell替換XML文件中的文本

昨天，我在一個客戶現場工作，那裏有一個與外部網絡隔離的Windows伺服器。不允許在該機器上安裝任何第三方軟件。

PowerShell腳本

然而，我被交付了一項任務，要將所有XML文件名從“我的報告”換成“我的報告（新）”。原始文件，temp.xml，是這樣的：

    <ReportList>
      <Report Name="My Report">
      </Report>
    </ReportList>

預期的輸出文件，temp-new.xml，應有這樣的結構：

    <ReportList>
      <Report Name="My Report (New)">
      </Report>
    </ReportList>

沒有專門工具的訪問權限，並面對手動編輯數百個文件的前景，我轉向了PowerShell來編寫腳本。以下是完成此任務的幾行代碼：

步驟1：從我的測試文件夾中加載所有XML文件

    $files = Get-ChildItem C:\Users\victorleung\tw\Desktop\Test -Recurse -Include *.xml

步驟2：通過在原始名稱後添加"（新）"來修改所有報告名稱

    $xmldata = [xml](Get-Content $file);
    $name = $xmldata.ReportList.Report.GetAttribute("Name");
    $name = $name + " (New)";
    $xmldata.ReportList.Report.SetAttribute("Name", $name);
    $xmldata.Save($file)

步驟3：將文件名從temp.xml 改成 temp-new.xml

    Get-ChildItem *.xml | Rename-Item -NewName { $_.Name -Replace '.xml$','-new.xml' }

就是這樣！所有文件都已被更改。開心編碼！😃

Replace Text in a File Using a Batch Script

Yesterday, I was working at a client site. The site had a Windows server that was isolated from external internet access, and installing new software was prohibited.

I was tasked with removing all the id values in YAML files, setting them to null. For instance, the input file temp.yaml looks like this:

    something
      id: 4
    something else
      id: 64
    next one
      id: 231
    another one
      id: 34

The target file (result.yaml) that I wanted would look like this:

    something
      id:
    something else
      id:
    next one
      id:
    another one
      id:

The file was large, so removing each id value manually would be time-consuming. The only tool accessible on that Windows server was the CMD command prompt. Hence, I wrote a simple batch script to complete the task. Create a file called convert.bat in a text editor with the following content:

    @echo off
    for /f "tokens=1* delims=:" %%a in (temp.yaml) do (
      if "%%b"=="" (
        echo %%a
      ) else (
        echo %%a | find " id" > null && echo %%a: || echo %%a: %%b
      )
    ) > result.yaml

You can replace the text temp.yaml and result.yaml in the script with your target input and output files, respectively. Double-click to execute the script, and you're done!

For those unfamiliar with Batch scripts, here are some basic explanations:

• @echo off means to suppress the command prompt display, effectively hiding it.

• The for loop has some options. The tokens= parameter specifies which numbered items to read from each line (default is 1), and delims= specifies the delimiter character (default is a space).

• The %%a and %%b variables are similar to arguments in batch files.

The last line exports the result to the desired file. This simple script saves a lot of time compared to doing the work manually. :)

Replace Text in a File Using a Batch Script

Welcome to "Continuous Improvement," the podcast where we explore ways to enhance our productivity, efficiency, and effectiveness. I'm your host, Victor. In today's episode, we'll discuss a clever solution to a common problem. But before we dive in, I want to thank our sponsor, Productivity Pro.

Yesterday, I encountered a situation that many of us can relate to. I was at a client site, working on a Windows server with no internet access. The task at hand was to remove all the id values from YAML files and set them to null. With a large file and no convenient software options, it seemed like a time-consuming task.

But fear not, fellow productivity enthusiasts! I came up with a simple solution that saved me a tremendous amount of time. I wrote a batch script to automate the process. Let me walk you through the steps.

First, in your text editor, create a file called convert.bat. Make sure you have the necessary permissions on your Windows server. Now, let's take a closer look at the script itself.

Inside the convert.bat file, start with the line @echo off. This will suppress the command prompt display, allowing the script to run in the background without cluttering your screen.

Next, we'll use a for loop to read each line of the YAML file. Remember to replace the text temp.yaml with the name of your target input file and result.yaml with the desired output filename.

Within the loop, we check each line for the presence of the id value. If the value is empty, we echo the line without modification. However, if there is an id value present, we check for the phrase " id" using the find command. This ensures that only lines containing id are modified. If the phrase is found, we replace the line with %%a: followed by %%b, effectively setting the id value to null.

Finally, we redirect the output of the script to the desired file using > result.yaml.

And there you have it! By executing this script, you can effortlessly remove id values from YAML files without the need for additional software. It's a simple but effective solution that can save you valuable time and effort.

Remember, continuous improvement is all about finding innovative ways to work smarter, not harder. So next time you encounter a repetitive task, think outside the box and explore scripting or automation options. Who knows, you might just stumble upon a time-saving gem like this batch script.

Well, that wraps up today's episode of "Continuous Improvement." If you found this batch script helpful or have any other tips to share, feel free to reach out to me on social media. Don't forget to tune in next week for another insightful episode.

Until then, stay productive and keep striving for continuous improvement.

Thank you for listening to "Continuous Improvement." This podcast is brought to you by Productivity Pro, your go-to source for all things productivity. Visit our website at productivitypro.com for more resources and tools to supercharge your efficiency. And remember, a little improvement every day goes a long way.

Note: The script mentioned in this episode is for illustration purposes only. Always exercise caution when running scripts on your system and ensure they comply with your organization's policies and security measures.

使用批次腳本替換檔案中的文字

昨天，我在客戶的地點工作。該地點有一台Windows伺服器，該伺服器已與外部互聯網隔離，並禁止安裝新的軟體。

我被委託將YAML文件中的所有id值刪除，將它們設置為null。例如，輸入文件temp.yaml如下所示：

    something
      id: 4
    something else
      id: 64
    next one
      id: 231
    another one
      id: 34

我想要的目標文件（result.yaml）應該如下所示：

    something
      id:
    something else
      id:
    next one
      id:
    another one
      id:

該文件很大，因此手動刪除每個id值將非常耗時。該Windows伺服器上可訪問的唯一工具是CMD命令提示符。因此，我寫了一個簡單的批次腳本來完成這項任務。在文本編輯器中創建一個名為convert.bat的文件，其內容如下：

    @echo off
    for /f "tokens=1* delims=:" %%a in (temp.yaml) do (
      if "%%b"=="" (
        echo %%a
      ) else (
        echo %%a | find " id" > null && echo %%a: || echo %%a: %%b
      )
    ) > result.yaml

你可以在腳本中替換文本temp.yaml和result.yaml，分別代表你的目標輸入和輸出文件。雙擊執行腳本，你就完成了！

對於不熟悉批次腳本的人，這裡有一些基本解釋：

• @echo off表示要抑制命令提示符的顯示，有效地將其隱藏。

• for循環有一些選項。tokens=參數指定從每行讀取哪些編號項目（默認為1），delims=指定分隔符字符（默認為空格）。

• %%a和%%b變量與批次文件中的參數類似。

最後一行將結果輸出到所需的文件。與手動做這項工作相比，這個簡單的腳本節省了很多時間。:)