Automatically get prices from Souq.com to your Google sheet

If you are a regular seller on Souq.com, then you know there is nothing more important than keeping a tab on the competition prices, and adjusting your prices accordingly. of course the seller console alerts you whenever there is a product available for a lower price, it does so in a limited manner. It doesn’t show you the seller name for example.

What if I could show you a way to keep all the data you want in a spreadsheet. Say your costing, lowest price you are willing to sell, and current lowest price available on souq.  Something like this sheet

If this looks interesting , Read on.

We are going to use google Sheets, importXML() function to automatically fetch the prices for you.  Here is the step by step

1. Get the URL which shows Top offers for your product.
This is the top offer page for Apple iPhone X which shows the seller with lowest price first.  This the URL I have put in the Column F of my Google Sheet.

2. Get the Xpath for Seller and Price.
Souq Displays the price in a Div with a Class called ‘field price-field‘.  You want the text that’s contained in this div. Further, you want only the first record.

The Xpath for this would be
(//div[@class=”field price-field”]/text())[1]

Similarly Xpath for Seller name would be 
(//div[@class=”field seller-name”])[1]

These are the Xpaths you see in the fields G and H.

3. Use google Sheets importxml() function to fetch this information

Finally use the ImportXML() function like this in the columns where you want to fetch price and corresponding Seller.  You used imporxml() function with the first argument as the URL and second as the Xpath.

There you have it, a single sheet that captures all the information you need to successfully keep a tab on your competition

Consideration

Consider the following when you use this approach

1. Google sheets only allow you to have around 50-70 importXML calls per sheet. So you can’t put 500 products there and fetch the data for all. If you wish to do that, you could use python for scraping. I use python with Selenium to scrape souq whenever I need to scrape more than 100 items

2. If you can live with a slower solution, you can use Libreoffice webservice() and filterXML() functions. There is no limit to the amount of URLS is fetches.

2. Xpath I have used will change as Souq changes their website structure. anytime you get an error, inspect the document and see if the structure of the page has changed.

My top 10 little known Productive apps

I am a productivity freak .  I am always looking for ways to automate repetitive tasks and minimize the number of clicks it takes to do something.  here are my top 10 windows apps which helped me save countless hours in 2018. I am not including Popular apps like gmail, evernote, slack and whatsapp , which everyone uses anyway.

1.Ditto : Clipboard Manager (Open Source)

Ditto is an open source clipboard manager which sits in your task tray and captures your Clipboard Contents.  It saves a lot of time when you need to copy multiple items from one window to another. Rather than switching back and forth between source and target window, you can copy all the bits of text you want from source, then switch to target and copy all at once. 

2. rbtray.exe : Right click to Minimize to Task Bar (Open Source)

Too many open windows not only distract you from focusing, but also makes it difficult to switch between them.  I like to keep my taskbar clean, without having to close a program and losing the state. With RBTRY, you just right click on the minimize button of an open window, and it is minimized to taskbar.
There is no installation, just make sure rbtray.exe is running all the time ( place it in your startup group). Almost all the programs are supported ( some like remote desktop, thunderbird are not )

3. Listray : Universal Search ( Lite Free Version)

Tap control key two times and listray search window pops up. Search for any program name, file or folder name and it efficiently brings it up.

4. Thunderbird : Desktop Email Client (Open Source)

I have been using thunderbird since 2006 and it remained my top productive program even in 2018.  Install extensions like nostalgy and Lightning , and you will never go back to any other email client.  

5. Auto Hot Key : Macro Recorder and Automation (Open Source) 

With Auto Hot Key, you can automate almost anything. Have a hot key for regularly used phrases, simulate multiple key strokes, send an email periodically etc.  My Favorite is pulling a report out of the ERP system with a hotkey that otherwise takes 4 steps and 8-10 clicks.

6  . Gadwin Print Screen – Screen Capture ( Open Source) 

Take a screen shot, crop it , resize it, save it and send it to email or printer – all with just one program. I use Gadwin for all my screen captures. 

7. Irfran view – Image viewing and Editing (FreeWare)

Nothing Beats ifran view in speed and versatility when it comes to Image editing.  This extremely lightweight program is full of features. Use the batch mode for bulk operations at once, like inserting background, changing size/format or Auto crop borders.

8. Libre Office for regular expression matching and replacing (Open Source)

Libre office is free and open source. It also can search and replace based on regular expressions.  The one regular expression that I use the most is    ‘W.*?R’  which will match any string starting with W and ending with R. 

9. Google sheets for smalltime web scraping

I can have my google pull content form the internet everytime I refresh it.  Taking competition price from a URL, updating a stock price, or checking stock status from a URL can all be done with Google sheets importxml(). 

10. Python and Selenium for Web Automation 

To use this you would have to know a bit of python, but this is the most powerful way to automate your browsing. I have automated stuff like login, classified postings, scraping web and even website testing with python and Selenium. 

2 Easy Methods to Setup OpenVPN server on ubuntu in under 10 minutes

Generally setting up an OpenVPN server is a daunting task for non linux Users, which can easily take 40 minutes to an hour for a first time user.  However there are 2 extremely easy ways of setting it up in under 10 minutes.


Method 1 : By Installing OpenVPN Access Server ( AS)

This is the easiest method that can get you a fully working server up and running in under 5 minutes.  You have to just download the installation file and run it. All the basic configuration is out of the box.  All the advanced configuration ( if you need), user creation, profile download etc is done via a graphical user inteface in your webbrowser, and you never have to see the command line .



There is a limitation however to the free version, you only get 2 free concurrent users for testing. If 2 users is all you want, use OpenVPN AS as this is the easiest way to install the VPN server.

Step 1.1 : Download the package

Step 1.2 : Run the installation package

You should be able to double click on the downloaded package .  If you are running a headless server, or only have access to command line, then run the dpkg command to run the installation

dpkg -i openvpn-as-2.0.5-Ubuntu10.amd_64.deb

Substituting the openvpn-as-2.0.5-Ubuntu10.amd_64.deb for the name of the package file you have downloaded

Step 1.3 : Change Default Password

Now your OpenVPN AS has been installed for you already and a default admin user openvpn has been created. Change the password for this user by running

passwd openvpn

 

Step 1.4 : Access your OpenVPN GUI, and add a user.

Open your web browser and go to  https://openvpnasip:943/admin , replacing openvpnasip as your server ip.  Use the default user name ‘openvpn’ and the password that you had set in step 1.3
Add one more user by visiting the user section.  You can add as many users as you want, but only 2 users can be connected to the server at a time.

Step 1.5 : download the .ovpn profile

login to  https://openvpnasip:943/?src=connect
Login as the user you want to connect and download the .ovpn profile for this user.  Import this .ovpn profile in your OpenVPN client and connect with the respective username and password

Method 2 : By Using a containerization application

There is an excellent container by Kylemanna for docker that can be used to setup a full OpenVPN server.

Step 2.1 : Install docker if you don’t have it on your system

Though you can run sudo apt-get install docker-ce , it will mostly get you an old version.  To get the latest version, add the docker official repository and install from there.
Add Docker official GPG key
curl -fsSLhttps://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
set up the stable repository.
sudo add-apt-repository   "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"
Update the apt package index.
$ sudo apt-get update
Install docker

sudo apt-get install docker-ce

Test that docker has been installed by running
docker run hello-world

Step 2.2 :  Pull the image to your sytem

Copy the image to your system by running
sudo docker pull kylemanna/openvpn

Step 2.3 : Prepare your volume

You need to create a docker volume which will hold your configuration and certificates . Choose a name for the $OVPN_DATA volume.  Save the name to the variable $OVPN_DATA , so we can easily reference this variable.
OVPN_DATA="ovpn-data-example"
Create the Volume and initialize it. It will ask a passphrase, remember this since you will need it while creating your client certificate.
docker volume create --name $OVPN_DATA
docker volume create --name $OVPN_DATA
docker run -v $OVPN_DATA:/etc/openvpn --log-driver=none --rm kylemanna/openvpn
ovpn_genconfig -u udp://VPN.SERVERNAME.COM
docker run -v $OVPN_DATA:/etc/openvpn --log-driver=none --rm -it kylemanna/openvpn ovpn_initpki

Step 2.4 : Generate client certificate and download config

Generate the client certificate with
docker run -v $OVPN_DATA:/etc/openvpn --log-driver=none --rm -it kylemanna/openvpn easyrsa build-client-full CLIENTNAME nopass
And dump a copy of the client configuration with
docker run -v $OVPN_DATA:/etc/openvpn --log-driver=none --rm kylemanna/openvpn ovpn_getclient CLIENTNAME > CLIENTNAME.ovpn
Import CLIENTNAME.ovpn to your openvpn client and connect. Since we created the certificate with no pass option, you can connect without password.

Filenames on network share different from actual file names Qnap

Problem :
The filename you see in ftp and filemanager in Qnap is correct. But a strange name (often with ~) is shown for this file when you access your network share.  See the below screen shot

Solution :

This happens because Samba is serving shortfile name in 8.3 format to you . SAMBA is used to make a linux share look like a windows share.  However for some files ( where the filename has a special character), samba may show the filename as conventional 8 char long.

To solve this you have to enable “Only allows applications to access files using the long file format”.

 

  • Go to “Control Panel” > “Privilege” > “Shared Folders”.
  • Click on the shared folder which has this problem
  • Click on “Edit Properties”, the button that has a pencil on it
  • Enable “Only allows applications to access files using the long file format”.
  • Click “ok”

After this , the applications will have access to the full long file format

 

Openvpn process killed after “Initialization Sequence Completed “

Problem :
Your openvpn client on ubuntu doesn’t connect to your server.  After “Initialization sequence complete” the process is killed.

A Log Excerpt , after the last step the process is killed
Sun Jul 8 14:13:58 2018 GID set to nogroup
Sun Jul 8 14:13:58 2018 UID set to nobody
Sun Jul 8 14:13:58 2018 Initialization Sequence Completed

Solution :
Make sure that your openvpn client is latest 2.4.  Ubuntu official repository gives you an outdated version. once you update, the problem should disappear.

here is how to update to the latest client

https://community.openvpn.net/openvpn/wiki/OpenvpnSoftwareRepos